Say My Name – Simplifying Excel With Named Ranges

Overview

Named ranges are one of the hidden gems in Excel. They are treasured by experts and unknown to the vast majority of users. They provide several benefits (many of them long-term benefits), without a lot of effort. In this article, we’ll discuss the what, why, and how of named ranges, followed up by some gotchas, tips, tricks, and macros that make them super-easy to use.

What are named ranges?

• The range can reference one cell, many cells, one or more columns, or one or more rows. They don’t have to be contiguous either!
• They can reference that range with an entire workbook scope or a specific worksheet scope.
• They are absolute by default (i.e. equivalent to $A$1) but they can also be relative (see tips & tricks below!).
• They can be static and not change (unless you insert/delete within the range) or be dynamic and change based on the results of a formula.
• They are accessible in the name box (to the left of the formula box) and in the name manager (located on the Formulas ribbon tab).

Why should named ranges be used?

There are several benefits to using named ranges:

• They make formulas easier and more informative to read (example: the name Tax_Rate is easier to read than $C$3), effectively making them like constants or variables used in programming concepts.
• They help reduce errors due to those easier-to-read names, especially because range name suggestions appear as you type in the formula bar!
• They help you navigate your workbooks faster in two ways: first, you can use the name box to jump to a name and second, you can easily create hyperlinks to range names.
• They can be dynamic (using formulas) and automatically update along with your data – a key use case for this is supplying data behind data validation drop-down lists.
• They are crucial for referencing ranges in macros, since ranges in macros are hard-coded and don’t update with structural changes, but named ranges will follow along with structural changes.

How do you use named ranges?

• The name box 🖼️ – this is the most basic way to use named ranges. Just select your range, type a name in the box, and press enter! If you type a name that’s already in use (or a standard reference), Excel will jump to that range. This can help navigate complex workbooks.
• The name manager 🖼️ – this dialog box (located in the Formulas ribbon tab) is a more comprehensive way to add, delete, and update named ranges. Use it to define the named range scope (workbook vs. worksheet), formula-driven ranges, and add comments to the named ranges as to their purpose. It’s also where you define LAMBDA formulas.
• Define from selection 🖼️ – this is a way to quickly define multiple names based on the column headers or row labels.

Gotchas

Be aware of several gotchas related to range names, starting with valid names:

Make sure you name ranges uniquely, logically, and consistently.

• Range names can include letters, numbers, periods, and underscores, must start with a letter, underscore, or backslash, and must not be function names (i.e. “sum”) or range addresses (i.e. A1). (Full named range rules, per Microsoft)
• Though you can use upper- and lower-case letters, Excel does not treat them differently.
• Be aware of the scope of named ranges – workbook vs. worksheet, otherwise you might be referencing the wrong range!

Tips & tricks

• You can also use the name box to navigate unnamed cells 🖼️. This is especially helpful for selecting large ranges where you know the cell range address (i.e. “A2:A8556”).
• You can create 3-dimensional named ranges that span across worksheets! Just specify the sheets to include, then cells to include in a syntax like this: “=Sheet1:Sheet5!$B$21:$E$25”.
• Range names can refer to fixed values 🖼️ (they don’t have to be cell range values!). Just use the name manager to set a name and a fixed value in the “Refers To” box (i.e. a name like “Tax_Rate” with a value/definition of 0.0825).
• You can set a range name to be relative 🖼️ based on the cell you enter it to (i.e. “Cell_Above” could always refer to the cell directly above)! Just use the name manager to change the default absolute references to absolute (remove the $ symbols as appropriate).
• Named ranges can be dynamic 🖼️! Use the name manager and set a formula using functions such as OFFSET and COUNTA to determine the number of columns and rows to include. See the [macros] below that include a versatile shortcut to create a dynamic named range in one step!
• Named ranges can be hidden (with VBA) so that users can’t accidentally mess them up!

Keyboard shortcuts

To make it easy to use and manage named ranges, there are several shortcuts you can use:

• F3 – paste a basic list of range names in the worksheet (click the Paste Link button)
• F5 – shows a list of range names and other references you can jump to (and back from!)
• Alt+F3 – jump to the name box
• Ctrl+F3 – launch the name manager box
• Ctrl+Shift+F3 – apply names from column headers / row labels

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

Summary

If you work in Excel files with a lot of sheets and data, it’s likely that named ranges can help you in several ways, including simplifying your formulas, navigating quicker, and making your files more dynamic. Hopefully this article got you thinking about several ways you can leverage them. I’ve added hundreds of them to templates to help navigate, tie-out numbers, make formulas easier to build, and ensure macros work correctly in case there are structural changes.

How have you leveraged named ranges to make your Excel files quicker and more powerful? Let us know in the comments below!