Study smart | Excel super-stars
How to use named ranges
Save time and make life easier by utilising an often overlooked function
Words Traci Williams, excelace.co.uk
Introduction
Named ranges are one of the most underutilised functions in Excel, but they can save time, reduce the risk of error and make formulae easier to enter, read and understand.
They don’t seem to do very much on their own, but use them alongside formulae, PivotTables or data validation and you start to see the magic happen.
What is a named range?
A named range is simply a name given to a cell or range of cells. This name can then be used in place of that cell or cells.
In this instance, the formula is looking at the value from the cell F1 for the VAT rate:
We could give cell F1 a named range (VAT_Rate), and then the formula can refer to that instead:
This saves the user having to work out what cell F1 contains, as they can see directly from the formula that VAT_Rate is being used.
Creating a named range
Creating a named range is simple. Select the cell, or range of cells, click into the Name Box and type in the name, followed by Enter:
There are a couple of rules to observe when choosing a name:
Do
- Make names as long or short as required
- Use mix of upper and lower case letters, as well as numbers
- Use underscore ( _ )Use the scroll bars at the bottom and right of the screen to ensure that ALL rows and columns have been selected before applying Sort
Don't
- Start named range with a number
- Use characters, such as space or dashes
- Use a name that is the same as a cell reference, e.g. B2
This is the error message you will see if a name is invalid:
This is not terribly clear as to what the issue is, or how to resolve it, and also does not make it clear that the named range has NOT been created.
It is also possible to highlight the required data and select Create from Selection from the Defined Names section of the Formulas ribbon:
You will then be prompted to select the option to use for the name from the following:
This will use the selected value as the name of the named range and not include that value within the range.
In this instance, if I selected this range and chose Top row, the name for the named range would become Type and the range would be B3:B10:
This is particularly useful if you want to create named ranges for multiple columns or rows.
How to find an existing named range
To the right of the Name Box, you will see a small arrow:
Click on the arrow and a list of all named ranges in the file (across all sheets) will appear:
You can select a named range from this list to navigate to it. This is perfect if you forget where a named range is, or if someone else has created them.
TOP TIP
This is also a great tool to use before Sort to highlight data, or to select data to print.
Amending an existing named range
From the ribbon, click Formulas and select Name Manager:
This screen will appear, containing a list of all named ranges in the whole file:
By selecting the named range, the cell reference will appear at the bottom and can be manually edited as required. Once this has been edited, click on the tick to save the changes, then close. If you forget the tick and press Close, you will be asked if you want to save (yes or no).
Of course, if you insert or delete rows or columns within the range, then the named range will automatically update to accommodate this.
How do you use a named range within a formula?
The F3 button on the keyboard will bring up a list of named ranges within the file, and they can be selected while editing a formula. This saves the user needing to remember the exact names and spellings.
**This applies to PCs only, and is not available for Mac users.
Where are named ranges best used?
In the above examples, named ranges have been used to represent an individual cell within a VAT calculation. But they also have many other uses:
Vlookup formula
Here, we have a list of suppliers and the associated expense type each relates to. This list has a named range called Supplier_Type, and the range includes the cells D3:E11:
In the next sheet, we can include a Vlookup formula to find the associated expense type for the selected supplier, by connecting to the named range:
Notice how much smaller, clearer and more succinct this formula is, instead of having to include the cell reference: Lookup!$D$3:$E$11.
Data validation
This list of different types of expense has been given the named range Expense_Type:
This named range can then be used in Data Validation to provide the source of data to be used in the pick list:
As above, this is far smaller, clearer and more succinct than including the cell reference: Lookup!$B$3:$B$10.
PivotTables
PivotTables can also be linked to named ranges, as opposed to the cell references. This is extremely useful if you have more than one PivotTable, as it means you don’t need to manually amend the range for each, as it will automatically update as the named range is amended.
This is not only less risky (in case you forget to amend the range of a PivotTable) but also quicker as it saves a manual job.
Conclusion
Named ranges can be used within any formula or function, and they make formulae far easier to read as they contain words (hopefully meaningful) instead of cell references. As a result, they tend to make formulae appear neater and less daunting.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is a primary benefit of using named ranges in Excel?
2
Which of the following methods can be used to create a named range in Excel?
3
How can you edit an existing named range in Excel?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).