Why Use Named Ranges in Excel
Named ranges allow you to specify a unique name for a range of one or more contiguous cells within a sheet. Cells can also be assigned to multiple named ranges. This is helpful for referencing rows, columns, or cells in a sheet that may structurally change. For example if you are referencing a total number in another file, and additional rows may be added within the total sum range. Any files referencing the total may get out of sync and reference the wrong row. By specifying a named range, that reference will always reference the current range for the total cell. This works really well when setting up a tie-out list of items that should match between different template files.
Named ranges work very well when combined with data validation, PivotTables, lookup functions, and macros for the same reasons as above – you don’t have to manually update the reference if additional rows or columns are added to your data set. For macros in particular, named ranges are a must. Because macro references do not automatically update when you insert and delete rows, columns, or cells the way references do in cell formulas, assigning named ranges and using them in your macros for functions such as sorting or selecting ranges is necessary to ensure you select the correct range.
Named ranges can be dynamic using the OFFSET and COUNTA functions to determine the last non-blank cell in a list. We show an example of this in our article about data validation.
Named ranges are also easier to understand if you name them logically and consistently. If I use the name CY_PTD_Total_Sales, it’s pretty obvious what the contents of the cell are compared to just referencing a cell address such as B55. In this way, named ranges also work well as bookmarks for jumping to specifc sections of the workbook. They are available using the drop-down name box to the left of the formula bar.
How to Use Named Ranges in Excel
To add named ranges, just select the range to name (one or more contiguous cells), then enter a name in the name box to the left of the formula bar. You can also use the Name Manager, which is found in the Formulas tab, to add, edit, and delete named ranges. Named ranges must start with a letter or underscore, and include only letters, numbers, and underscore characters. To include them in a formula, simply type in the range name. As you type, Excel will show you a list of the names you can use (hint: click the Tab key to fill in the rest of the name!). You can use the range name pretty much anywhere you would normally use a cell column/row range address.
The VBA code below gives some examples of working with named ranges in a macro.
The Excel Add Named Ranges From List macro lets you add named ranges in bulk when you specify the range name and the range address it should reference. You can add hundreds of named ranges in just seconds without all the clicking required doing it the manual way. This macro is demonstrated in the video below.
Example Files
The example files below show the before and after effects of running the Add Named Ranges From List macro, which is included in the XLEV8 add-in. These are the files used in the video below.
Recent Comments