Overview
Named ranges are a powerful and flexible tool in Excel, but they are way underused. One of the biggest benefits they provide is being able to reuse your effort – a crucial efficiency play. They also allow you to manage things centrally, so that you only have to update important items like references and assumptions in just one place. If you aren’t yet familiar with them, here’s a comprehensive article to get you started.
When they are used, named ranges tend to be used very basically – just one static cell or a range of cells. While that can still be helpful, you’re missing out on some great approaches!
In this article, we’ll look at 5 creative ways to take named ranges to the next level. Make sure to download the example workbook using the box below to see these examples you can copy from and use.
The Examples
#1 – Dynamic Named Ranges
Most people only use named ranges to define one cell or a static range of cells. But making the range definition dynamic is even more powerful because it will automatically update as your data grows (usually the rows). Defining drop-down lists (via data validation) is a great use case for dynamic named ranges.
There are two approaches to making ranges dynamic – the legacy approach (using functions like COUNTA, OFFSET, and MAX) and the modern approach (using the period/dot operator), which requires a more recent version of Excel (Excel 365 or builds from late 2024). Take a look at both with the definitions and screenshots below.
Legacy approach example:
=OFFSET('Example 1'!$M$2,0,0,MAX(COUNTA('Example 1'!$M$2:$M$512),1),1)
Modern approach example:
='Example 1'!$O$2:.$O$2000
#2 – Relative Named Ranges
By default when you define named ranges by selecting ranges, they are absolute – they always reference the same range. But sometimes, you want it to move around. As an example, a common modeling trick is to define the cell above relatively as the last cell for calculating a total. Note the reference in this definition does not have the $ symbols that would otherwise make it absolute.
='Example 2'!B17
#3 – Constant Value
The named range definition does not have to be a range, it can also be a constant value (number, text, date, etc.). This is useful for assumptions like growth rates, tax rates, names, dates, or other values you want to centralize.
=0.0825
#4 – Formula Results
Using a named range to store formula results that you reference in formulas throughout your files is another great approach. A common use of this approach is determining the last row of data in a range, like you see below.
=ROW('Example 4'!$A$7)-1+COUNTA('Example 4'!$A$7:$A$2000)
#5 – Custom Functions
When the Excel product team introduced LAMBDA in 2019 along with dynamic array functions, it changed the game with what we can do in Excel. LAMBDA allows you to define a custom function – complete with required and optional arguments – so that you can simplify formulas (often really complex ones!) and give you one place to apply updates.
To learn more about LAMBDA, check out this article, and if you want to leverage a growing library of 80+ custom LAMBDA functions, check out our template here!
The LAMBDA definition below lets you apply a tax rate and rounding. Check out the screenshot below the definition for usage with and without the optional arguments.
=LAMBDA(pre_tax,[tax_rate],[round_decimals],
LET(
tax_rate_b, IF(ISOMITTED(tax_rate) = TRUE, 0.0825, tax_rate),
round_decimals_b, IF(ISOMITTED(round_decimals) = TRUE, 2, round_decimals),
ROUND(pre_tax * (1 + tax_rate_b), round_decimals_b)
)
)
Macros That Help
- Search Named Ranges – this lets you search all the named ranges in your workbook with auto-complete search suggestions, showing you the ranges they relate to. Also lets you jump to a cell range address.
- Add Dynamic Named Range – this lets you set a formula-based named range for one or more rows or columns using the OFFSET and COUNTA functions. It works very well with data validation drop-down lists.
- Bulk Edit Named Ranges – this lets you add, edit, and delete named ranges in one bulk step across one or more workbooks.
- Named Range Picker – this brings all the macros above together and more in a helpful pick-list
Video
Summary
As you can see, named ranges are all about simplicity and keeping things centralized. In some cases, they are also a good approach to keeping data out of sight from users that might otherwise clutter up your worksheets. Using them in creative ways like these unlocks even more time savings and enhances quality. Remember to get a copy of the example file that covers these approaches with the box above. And for some great custom named range shortcuts, check out the macros above!
What creative named range approaches have you found helpful? Let us know with a comment below!
Recent Comments