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"
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".
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.
Using range names in Excel allows you to quickly navigate to areas of your worksheet and makes formulas much easier to create. A range name is simply a name you assign to a range of data and is much easier to remember than a cell address.Why is it useful to name a range of data? ›
Named ranges are most useful when working with formulas as they make them much more readable and improve their overall organisation. In this image , cells A2 and B2 have each been given a name (Sales and Expenses, respectively). Rather than cell references being used in the formula, there are named ranges.How do I use name ranges in Excel? ›
- Select the range you want to name, including the row or column labels.
- Click Formulas > Create from Selection.
- In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
- Click OK.
Range in statistics is simply the difference between the highest and lowest values in a set of data. For example, the lowest to the highest values of a salary dataset might be $10,000 to $50,000. In this case, the range of the data is $40,000, as $50,000 - $10,000 = $40,000.Why is a named range used in a spreadsheet? ›
Named ranges in Excel are labels that you can assign to individual cells or cell ranges. This allows you to use range names anywhere you would use a cell or cell range reference.What is the biggest benefit of creating a named range? ›
- You don't have to physically select the cell range each time you want to use it.
- You don't have to remember the cell reference.
- Named ranges can be managed easily by using Name Manager.
- Navigation to your named range is much easier through the Name Box feature.
This represents the difference between the smallest and largest values in the dataset. In statistics, the range is important for the following reasons: Reason 1: It tell us the spread of the entire dataset. Reason 2: It tells us what extreme values are possible in a given dataset.What do you mean by naming a range describe the method to do so? ›
A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells. Providing cells with actual descriptive names can be used to simplify formulas and make them much more user friendly.How do you define names in Excel? ›
- Select the cell, range of cells, or nonadjacent selections that you want to name.
- Click the Name box at the left end of the formula bar. Name box.
- Type the name you want to use to refer to your selection. Names can be up to 255 characters in length.
- Press ENTER.
Named ranges in Excel have something called "scope", which determines whether a named range is local to a given worksheet, or global across the entire workbook. Global names have a scope of "workbook", and local names have a scope equal to the sheet name they exist on.
The only problem is that named ranges, especially dynamic ranges, consume a considerable amount of working memory. Overusing them may slow down your Excel spreadsheet.What are the disadvantages of name ranges in Excel? ›
So, what are the disadvantages of name ranges? Well, at first, if you don't use them often, they can seem just a little bit fiddly. The second disadvantage of name ranges is that they don't lend themselves to copying relative references. However, there are some workarounds for this when we are creating name ranges.What does range tell us about data? ›
Range, which is the difference between the largest and smallest value in the data set, describes how well the central tendency represents the data. If the range is large, the central tendency is not as representative of the data as it would be if the range was small.Why is range relevant? ›
The relevant range refers to a specific activity level that is bounded by a minimum and maximum amount. Within the designated boundaries, certain revenue or expense levels can be expected to occur. Outside of that relevant range, revenues and expenses will likely differ from the expected amount.When would we use the range? ›
We should use the range when we're interested in understanding the difference between the largest and smallest values in a dataset.What is an advantage of naming a range of cells you can use the name in a? ›
You can assign a name to a cell or a cell range. This introduces a number of benefits: The names act as bookmarks, making navigation of a workbook easier and faster. In a formula, a good name in place of a cell reference makes the formula easier to read, especially when the cell is on a different worksheet or workbook.What is the benefit of using a range name in Excel quizlet? ›
What is a benefit of using a range name in Excel? Range names are easier for people to remember than cell references.Why is it important to name changing cells? ›
If you name the worksheet cells you're changing, the cell names appear here, making it easy to tell what value you're working with.Can you use a named range instead of a cell address? ›
You can use a named range instead of a cell address in a formula. Answer: The statement is true. MS Excel allows us to assign names to cells in a worksheet and use it for quickly locating specific cells by entering the names. This could be useful when working with large spreadsheets.