ICAEW.com works better with JavaScript enabled.
Exclusive

excel how to

Excel how to: speed up entering formulae 2

Author: Simon Hurst

Published: 03 Mar 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

In this post we are going to consider other ways of improving how we refer to cells, and ranges of cells, when creating a formula.

'How to' series

In this series we will be looking at the Excel tools and techniques that help you accomplish a range of day-to-day Excel tasks more efficiently and effectively.

As part of each article, we will be scouring the extensive Excel Community archive to provide links to additional details and ideas.

Range Names

Using the familiar 'battleships' column/row notation is not the only way to refer to a cell. It is also possible to allocate a name, known as a Range Name, to a cell or range of cells. It is then possible to refer to this name in a formula, rather than referring to the cell directly.

As an example of how to do this, and why it can be a useful technique, we'll return to our VAT rate example from Part 1. As we discussed last time, being able to fix a reference to an absolute cell, rather than a relative position, can make it much easier to copy a formula from one cell to hundreds of others. In part 1, we used the dollar signs to fix our reference to the cell containing the VAT rate that we wanted to use:

=$B$1

Excel example 1

Using a Range Name as an alternative, we would first allocate a name to cell B1. The format of Range Names has to follow certain rules: the name can't include spaces and some special characters. In addition, although a Range Name can include numbers, it can't begin with a number. To make names more readable, you can either capitalise each word:

VATRate

or use a special character such as an underscore as a separator:

VAT_rate

The quickest way to create a new Range Name is to select the cell or cells then just type the name into the Name Box to the left of the Formula Bar:

Excel illustration 2

Alternatively, if you have used an adjacent cell to create a 'label' for your cell, then you can select the label cell as well as the cell to be named, and use the 'Create from Selection' command in the Defined Names group of the Formulas Ribbon tab. This will use the label, with underscores replacing spaces, as the name.

It is also possible to use the Name Manager and Define Name command to create new Range Names.

Once you have created the Range Name, you can use it in a formula to replace the direct cell reference and, if it applies to just a single cell, it will automatically be a fixed reference that can be copied to any other cell. Entering Range Names as part of a formula is easy: Range Names are included in the AutoComplete list that appears as you type different parts of a formula:

Excel illustration 3

Using a Range Name rather than a direct cell reference also has the advantage of making our formula easier to understand. Using a cell reference in formula on a different sheet to refer to our VAT rate cell would look like this:

=A1*'Sheet2 (2)'!$B$1

Whereas the use of the Range Name makes the intention of the formula much clearer:

=A1*VATRate

You can also select a named cell very quickly. The Name Box includes a dropdown that displays a list of all cell-based Range Names. Clicking on one will select it and make it the active cell.

Related links

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250