The Named range feature found in both Excel and Google Sheet gives the user the ability to name a range of cells with a label that is more memorable than the built in spreadsheet range format of “Sheet!ColumnRow:ColumnRow” (e.g. ‘Excel - Test - One’!A15:AF255’). While there are a few Excel wizards out there that have the ability to interpret the built in reporting format without switching sheets or scanning cells, the majority of users will most likely find this memorization difficult and there is absolutely no shame in that. Named ranges removes these requirements with the simple steps of selecting a range or formula, providing it a name and then referencing that name wherever it is needed. This ability to reuse cell ranges in a maintainable and recognizable manner will cut down on cell searching and eliminate scenarios involving incorrectly referenced cells.
How do you create a named range?
There are two ways to add a named range to a selection of cells.
- Select a group of cells that you would like to define as a named range. In the left hand side there is a field called the name box. The name box is where there is an input field with the value of the first cell in that selection of cells. Highlight the value in that box, change it to the name of your named range and hit enter. If the name stays in that box, then you have verified that the name range has been defined.
- Navigate to “Insert” in the excel navigation and hover over “Name”. When the sub-menu is revealed, click “Define”. This will open a window that contains all of the currently defined named ranges, as well as the ability to select a range and name it.
This sub-menu also contains the additional name range options of Paste, Create and Apply. While I won’t go into much detail with these options because there aren’t too many common use cases related to their actions, I will provide a brief summary of what they do.
Apply = Allows you to replace the format of a cell range used in a formula with the defined named range that matches the cell range criteria. i.e. “Find and replace” for named range.
Create = Automatically creates named ranges from a selected group of cells. Based on the user selected option from the pick list, the range values could be defined from a combination of the values found in the left or right column and starting from the top or bottom row
Paste = Pulls up the named range list and allows you to select a named range that you would like to paste in the currently selected cell.
As you can see from the two methods I have described above, using the named ranges feature is straightforward and can be done in the matter of a few clicks. This ease of use is worth the effort of updating your old spreadsheets to use them, as well as program your mind to think about name range strategies for upcoming report build outs. And if that isn’t enough of a selling point for the small amount of personal effort required, then consider the time you will save by not having to navigate sheets to figure out cell references, as well as the ability to decrease the likelihood of using the wrong cell range in a formula. Mind blowing, right!