Why name ranges in Excel?
Naming ranges in Excel can help you bring a lot of flexibility into your workbooks.
First, named ranges have an explicit name
This makes it simpler for you or any user to understand your formulas or track errors. If you have a formula like
where A1:A50 refers to sales amounts and B1:B50 refers to months, you can make it much simpler if A1:A50 is named "Sales" and B1:B50 is named "Months". Then your formula will be:
and your formulas becomes clear for you and for anyone who needs to work with your workbook.
Second, named ranges can be formula-based
So this allows you to refer to variable-size ranges. Such a named range will automatically adjusting your formulas, charts or pivot tables to new data. For instance, let's say you have a chart that shows how your sales month by month.
When the next month there is new data, you would like the chart to automatically take the new row into account, right? Well that's something you can do if the chart range is named.
Third, named range allow you to type formulas faster
When typing the beginning of a named range in a formula, a tooltip will suggest you existing named ranges. You can double-click it or press Tab to validate the choice. This can prove very useful, especially to avoid navigating between different Excel sheets looking for one range and then another.
When typing a formula, you can also press F3 to display the list of named ranges, and the double-click a name to insert it in your formula.
Fourth, named ranges are very useful for VBA developers
Referring to a range based on its coordinates, e.g. "A1", is really not flexible. If the cell is moved, the code will not understand it and still apply to "A1" no matter what. But if you refer to a range by its name, it allows you to move it, or even to refer to dynamic ranges with a variable-size directly in your code.
Writing your code with variables, like: ActiveSheet.Range("MyRange").Value, where MyRange is a named range, is always more flexible that simply referring to the range's position at a given time: ActiveSheet.Range("A1").Value
OK you got me. So how do I name ranges?
Naming range can be done very simply, so no excuse for not doing it! There are different ways to do it.
Method 1: Using the name box - quick and simple
Select the range of cells you want to name
Click the name box, left to the formula bar
Type the name for your range, e.g. "Sales"
Press Enter
Tadaaa, you now have a named range!
Method 2: Create from selection - useful to create multiple names at once in a table
Excel allows you to create names automatically based on labels. Look at the example on the right. What would be cool is to automatically name "Sales" the range A2:A8, name "Months" range B2:B8, etc.
If you have a large database, naming manually each range like this can take some real time. Hopefully, Excel allows you to automatically name each range based on the labels on the top, left, right or bottom.
How does it work?
1. Select an entire range of cells, where you would like to create multiple
2. In the Formula tab on the ribbon, click "Create from selection"
3. In the dialog box, choose where the labels are. In this example, labels are on the top of the selected range, so we choose "Top".
4. That's it, we now have automatically created ranges. You can check it by selecting the range B2:B8. The name box on the left of the formula bar will show "Months".
Method 3: Using tables - quickly create dynamic ranges
You can create tables to automatically generates names for each column.
While the "Create from Selection" method allowed to create names from labels located on any side of your data, this will only create names from labels in the header row. Another limitation is that names are usually longer because they contain the name of the table.
However using tables allow the named range to automatically adjust when you add new data in the table. This is highly useful since you don't have to change the name's reference every time.
To create a table, select the range of cells, go to the "Insert" tab on the ribbon and click "Table". Then names are automatically created for each column. In the table "Table1" on the illustration above, a name was created for the Sales column. Type:
Table1[Sales] will refer to the entire sales column without header (range A2:A8 in the example above). For instance, you can type anywhere =SUM(Table1[Sales]) to compute the sum in the sales column.
Table1[@Sales] will refer to the value of sales in the same row. For instance, type =Table1[@Sales] in B4 and you will get the value in A4.
Although we won't detail it here, you can also refer to specific parts of the table by using the # sign followed by All, Data, Headers or Totals.
So when you have created a table, simply type its name in a formula followed by "[" and you will get the suggestions of names as shown below:
Last advice, better rename the table to make it even easier to use names in formula, in particular if you have many tables. To rename a table, click on any cell of the table, then go to the "Design" tab and type the new name in "Table name".
💡To learn more about Tables, read 12 reasons why you should use Excel tables
Method 4: Typing a formula - useful to create dynamic named ranges
Last method, you can define a named range with a formula:
Go in the "Formula" tab from the ribbon
Click "Define Name"
In the "Name" field, type the desired name for the range (check below the rules for naming ranges)
In the "Refers to" field, type your formula
Validate to save your changes
A must-know formula to use in range naming is the OFFSET formula. You can use it to create dynamic ranges, which size will adjust to the number of rows or columns. In the example below, the range "MyRange" adjusts to the number of rows and columns:
Reference cell is Sheet2!$A$1. It's the starting point.
Then the rows to offset is 0. This argument is not needed for this usage of the OFFSET formula but can be useful in other usages.
Then the columns to offset is 0. This argument is not needed for this usage of the OFFSET formula but can be useful in other usages.
The number of rows to add from the reference cell is COUNTA(Sheet2!A:A), which counts the number of non-empty cells in the A column.
The number of columns to add from the reference cell is COUNTA(Sheet2!1:1), which counts the number of non-empty cells in the 1 row.
This method has multiple advantages, allowing to create Pivot Tables referring to a variable range (and without the "Blanks" row), charts with a variable range of reference so they can adjust to new data, etc.
A few rules to keep in mind for naming ranges
There are a few rules to keep in mind when you are naming ranges:
The first character must be either a letter, an underscore (_) or backslash (\)
All the other characters must be either letters, numbers, periods or underscores
Names can't have spaces
Names can't be an existing reference. You can't name your range "$A$1" because it already exists!
Names don't recognize case, so MyRange, MYRANGE or myrange are all the same.
Changing a named range
If you have created a name and want to change the name or change what cells it refers to, you can simply:
Go in the "Formula" tab from the ribbon
Click "Name Manager"
In the list of names, select the named range you want to change
Change either the "Name" or the "Refers to"
Validate to save your changes and close the Name Manager
Conclusion: naming ranges in Excel has many advantages, making your workbooks easier to read, making formulas faster to type and debug, and allowing to create ranges with variable-size to make your spreadsheets dynamic and easy to update.