Named ranges are one of the MOST under utilised functions in Excel, and that’s a huge shame as it can save time, reduce the risk of error and make formula’s easier to enter, read and understand.
Part of the reason they are under utilised is that on their own, they don’t seem to do very much, but if use them alongside formulae, pivot tables or data validation, that’s when you start to see the magic happen.
What is a named range?
A named range is simply a ‘name’ that is given to a cell, or a range of cells. This ‘name’ can then be used in place of that cell, or range of cells.
So, in this instance, the formula is looking at the value in cell F1 for the VAT rate:
We could give the 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 the ‘VAT_Rate’ is being used.
How to create a named range?
Creating a named range is super 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 as name:
- Make names as long or short as required
- Use mix of upper & lower case and numbers
- Use underscore ( _ )
- Start named range with a number
- Use characters, such as space or dashes
- Use a name that is the same as a cell reference i.e. B2 etc
This is the error message you’ll 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.
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:
If you select a named range from this list, the cursor will move to the cell or cells in the range. This is perfect if you forget where a named range is, or if someone else has created them.
Key tip: This is also a great tool to use before ‘sort’ to highlight data, or to select data to print.
How to amend a named range?
From the ribbon: formulas, 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 here, and can be manually edited as required. Once this has been edited, click on the tick to save the changes, then close.
Of course if you insert or delete rows or columns within the range, then the named range will automatically update to accommodate this.
How to 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. This saves the user needing to remember the exact names and spellings.
Free Excel webinar
Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below
Where are named ranges best used?
In the above examples, they have been used to represent an individual cell within a VAT calculation, but they also have many other uses:
1. Vlookup formula
Here we have a list of suppliers and the associated expense type they each relate to. This list has a named range called: Supplier_Type (this named 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:
2. Data validation
This list of expense types have been given the named range: Expense_Type:
This named range can then be used in the data validation to provide the source of data to be used in the pick list:
3. Pivot tables
Pivot tables can also be linked to named ranges, as opposed to the cell references. This is extremely useful if you have more than one pivot table, as it means you don’t need to manually amend the range for each pivot table, 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 pivot table), but also quicker as it saves a manual job.
4. Other formula
Named ranges can be used within any formula, and as you can see from the above examples, 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, shorter and certainly less daunting.
I am pretty obsessed with named ranges and use them extensively, and I hope I may have convinced you to try them out too.
Traci Williams is a self confessed ‘Spreadsheet Geek’ and has spent more than half of her life working with Excel Spreadsheets. She launched her own Excel training business, www.excelace.co.uk, and has since trained over 500 Delegates, and worked with over 200 businesses.