ICAEW.com works better with JavaScript enabled.
Exclusive

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 subscribers.

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.

In the first post we looked at how to enter cell references in formulae as quickly and efficiently as possible. This time, we are going to consider other ways of improving how we refer to cells, and ranges of cells, when creating a formula.

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