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