If you were to ask modelling professionals about the merits of using range names, you would find that opinion is strongly divided. In spreadsheets, used appropriately and sparingly, range names can provide great value because they can make formulas easier to read. In macros (not discussed here), they are vital. Overuse, on the other hand, can lead to end-user confusion.
Range names are names used to refer to cell references, formula results, or values. They are often used to avoid hard-coded values appearing in formulas and to make formulas clearer in general. They are stored in what is known as the Name Manager in Excel.
Users may create these names with the Name Box (circled in the screenshot above) drop-down menus and/or simply use the keyboard shortcut Ctrl+F3 in all versions of Excel, and then click on the New button in the Name Manager dialog box, as shown in the screenshot below.
Clicking on New (shown in the screenshot above) causes the following dialog box to appear:
Note the second section (Scope). All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognised without qualification.
For example, if you have defined a range name as “Profit” with its scope as Sheet1, rather than Workbook, then it will be recognised as “Profit” only in Sheet1 (ie, without qualification).
To use this local name in another worksheet, you must qualify it by preceding it with the localised worksheet name:
If you have defined a name, such as “Cashflow”, and its scope is the workbook, that name is recognised for all worksheets in that workbook (but not for any other workbook). If the scope was to a worksheet (say, Sheet1), then the range name would be “Sheet1!Cashflow” instead. Therefore, workbook scope makes for clearer range names and avoids confusion.
A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However, you can use the same name with different scopes. For example, you can define a name, such as “Profit”, that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name “GrossProfit”, for example, is always referencing the same cells at the local worksheet level.
You can even define the same name, such as “Profit”, for the global workbook level, but again this scope is unique. In this case, there may be a name conflict. To resolve this conflict, Excel uses the name that is defined for the worksheet by default. The local worksheet level takes precedence over the global workbook level. This can be circumvented by adding a prefix to the name, eg, rename it “WorkbookFile_Profit” instead.
It is possible to override the local worksheet level for all worksheets in the workbook, except for the first worksheet. This will always use the local name if there is a name conflict and cannot be overridden.
It is strongly recommended that you always try to create range names on a workbook scope level only. Further, where possible, avoid using range names in (sections of) worksheets that will be copied to other worksheets or workbooks. You will confuse Excel, the end user, and most importantly, yourself!
Care with names
The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.). For example, to enter the name “Cash Flow” you should enter “Cash_Flow” or “Cash.Flow”.
You cannot use a name that could otherwise be confused as a cell reference; for example, “Day1”, as this is already a cell reference (many people have tried!).
There appears to be no limit to the number of names you can define, but a name may contain no more than 255 characters. Names can contain uppercase and lowercase letters, but Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the global name “Profit” and then create another global name called “PROFIT” in the same workbook, the second name will be rejected as names need to be unique, irrespective of capitalisation.
It is not a syntax issue, but I strongly recommend thought is given to adding prefixes to range names. Regular readers will note that my list range names always begin with “LU_” where “LU” stands for “Look Up”. Similarly, I use “BC_” for “Base Cell” when working with the OFFSET function.
By using these prefixes, I understand the purpose of the range name, and names with a common purpose are grouped together in a list. This is not to say all range names should contain a prefix. “Tax_Rate”, for instance, makes sense on its own, and adding a prefix would only detract from the name given, potentially confusing the end user.
Creating range names quickly
There is a nifty shortcut for creating range names using existing names. Consider the following list:
Imagine you were to highlight cells N12:N18 in the above example and then use the shortcut Ctrl+Shift+F3 (alternatively, select Create from Selection in the Defined Names grouping of the Formulas tab on the ribbon):
With the first box (Top row) checked, by clicking on OK the range N13:N18 (not N12:N18) will be named “Phonetic_Alphabet” (ie, the underscore will be added automatically). Ranges across rows can be named in seconds similarly using Left column.
The reason this dialog box uses check boxes (rather than option buttons) is to allow users to select more than one at a time. For example:
Highlighting N31:R34 and using the keyboard shortcut Ctrl+Shift+F3 once more should generate the Create Names from Selection dialog box, as shown above, with both Top row and Left column checked. This means that O32:O34 will be called “Jan”, O33:R33 will be called “COGS”, and so on. This would take considerably longer to perform manually.
This example also illustrates why spaces are illegal characters in range names (and should not be added to formulas either). Space is the intersect operator in Excel. If you were to type the following formula:
Excel would return the value in cell P34 (the intersection of the two ranges, above), ie, $4,183. This can be a powerful yet quick and simple analytical tool for key outputs — faster than VLOOKUP or INDEX MATCH.
Using range names quickly
One of the reasons I like using the Ctrl+F3 shortcut is that it is part of the F3 Names family of shortcuts. We have just seen how Ctrl+Shift+F3 can be useful — and so can F3 on its own.
Perhaps superseded by the fact that in Excel 2007 and later versions, Excel will now prompt as you type formulas, F3 has been very useful in the past as the Paste Name shortcut. For example, as you type a formula, you can refer to a range name by simply typing F3 to get the Paste Name dialog box, as shown in the screenshot below.
If you select one of the names and click OK, this will insert the range name.
However, look closer at the dialog box. The Paste List button in the bottom left-hand corner, if depressed, will paste the list and their definitions into a preselected range of cells (ie, before you use the shortcut F3) in an Excel worksheet, which can be invaluable for model auditing purposes.
Sometimes, formulas have been written before the range name was created. In some circumstances, it is possible to apply these names retrospectively using Apply Names within the Define Names group of the Formulas tab (see screenshot below).
Note that the keyboard shortcut Alt+I+N+A will work in all versions of Excel. Selecting the required range names in the resulting dialog box will see formulas on the active worksheet(s) updated accordingly.
Deleting range names
If I got paid just $1 for every time I have been asked how to delete range names, I would probably have retired by now. This was chiefly attributable to the counterintuitive menu in Excel 2003 and earlier versions:
From the resulting dialog box, you would then select the range name (unfortunately, only one at a time could be selected) and hit Delete, as shown in the screenshot below.
Excel 2007 and later makes this much simpler. In this case, users are more likely to go to the Name Manager rather than the confusing Insert drop-down menu:
The other marked improvement is that multiple names may be deleted simultaneously by using the Ctrl or Shift keys to make multiple selections before hitting the Delete button.
By default, range names are referenced absolutely (ie, contain the $ sign so that references remain static). However, imagine a scenario where you are modelling revenue and you wish to grow the prior-period value by inflation (already given a range name, say cell C3 on Sheet1). Simply click on any cell (eg, I will use D17 arbitrarily), then define the new range name as follows:
Note the Refers to: entry. Cell C17 (the cell to the left of D17) has been chosen without the dollar signs. This is a relative reference. Once we click on OK, the range name “Prior_Period” will be defined as the cell immediately to the left of the active cell. We can then inflate values easily by copying the formula
across the row.
Other types of names
As I said earlier, names may also refer to functions, dates, and constants — the latter can be useful (eg, “Months_in_Year” is defined as 12) in order to avoid inserting hard code into a formula.
Word to the wise
This article discusses just the tip of the names iceberg. Experimenting can pay big dividends. The aim is not to go overboard, however, as a preponderance of names in a workbook may actually make formulas — and hence your model — more difficult to follow.
Further, be careful if you name ranges that are then deleted. The range names will not be deleted (even though they will no longer appear in the Name Box). They will need to be deleted as described above in order to avoid potential errors in formulas, etc. It’s important to remember this as this has often been a cause of file corruption or crashing in Excel.
— Liam Bastick, FCMA, CGMA, FCA,is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of An Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at firstname.lastname@example.org. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.
Excel Named Ranges makes it easy to refer to data sets in Excel. You can create a named range in Excel for each data category, and then use that name instead of the cell references. For example, dates can be named 'Date', Sales Rep data can be named 'SalesRep' and sales data can be named 'Sales'.Why is it important to use range names in Excel? ›
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.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.
A named range can be up to 255 characters long and can contain letters, numbers, periods and underscores (no spaces or special punctuation characters). Named ranges are not case sensitive and they can contain both upper and lower case letters.What is not allowed in names of ranges? ›
Names cannot be the same as a cell reference, such as Z$100, BIN9, or R1C1. A name can contain up to 255 characters. If a name that is defined for a range contains more than 253 characters, you cannot select it from the Name box, but you can still use it in formulas.What are the disadvantages of Excel name ranges? ›
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 is an advantage to defining range names? ›
For accountants, named ranges allow you to quickly navigate a workbook, read formulas with ease and create formulas with named ranges while waiting on data. Setting up named ranges can be done very quickly with a few clicks of the mouse. Once set up they can be used in formula and used to navigate a workbook.What is the name range concept? ›
A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box. Note: named ranges are absolute references by default.How do I create a dynamic named range in Excel? ›
- On the Formula tab, in the Defined Names group, click Define Name. Or, press Ctrl + F3 to open the Excel Name Manger, and click the New… ...
- Either way, the New Name dialogue box will open, where you specify the following details: ...
- Click OK.
Set Multiple Named Ranges at the Same Time
The simple solution to this is to use a built-in feature by Excel called “Create from Selection” under the Formulas tab within the Named Ranges group. You'll see that the named ranges have been applied to the values in the table.
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.Do named ranges slow down Excel? ›
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.Do named ranges increase file size? ›
Typically, Named Ranges will not significantly increase file size. Sometimes, however, they get corrupted in some fashion and add significantly to the file size. We recently received a workbook which contained over 2,600 Named Ranges, many of which were hidden and included external links.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.
Answer: Explanation: Named ranges are not case sensitive and they can contain both upper and lower case letters. They cannot resemble any actual cell addresses such as "B3" or "AA12".Why would we use descriptive range names in formulas instead of using cell addresses? ›
Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren't familiar with the workbook.What are the range values? ›
When you are given various values, the range of those values is how big the difference is between the largest value and the smallest value. In other words, the range is what you get when you subtract the smallest value in the group from the largest value in the group.What are the letters at the top of a spreadsheet called? ›
These letters and numbers are called row and column headings. To refer to a cell, type the column letter followed by the row number.What does the range mean? ›
The range is the difference between the highest and lowest values in a set of numbers. To find it, subtract the lowest number in the distribution from the highest.How to use named range in data validation? ›
Go to Data > Data validation. On the sidebar, click “Add a rule”. Select “Dropdown (from a range)” under 'Criteria'. In the range selection box, type the name of your range and press 'Enter'.
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.What is allowed in range names? ›
The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.).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.How do you use a range name in a formula? ›
- Select the cell(s).
- On the Formulas tab, in the Define Names group, click the Define Name button.
- In the New Name dialog box, specify three things: In the Name box, type the range name. ...
- Click OK to save the changes and close the dialog box.
Named ranges are a form of an absolute reference. By naming a range and using that name in a formula or function, we ensure that the reference will not adjust as the formula or function is copied. The syntax of the VLOOKUP function requires the data being looked for to be in the first column of the lookup range.Can I use named ranges throughout the worksheet? ›
You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard.How do I manage named ranges in sheets? ›
- Open a spreadsheet in Google Sheets.
- Click Data. Named ranges.
- On the named range you want to edit or delete, click Edit .
- To edit the range, enter a new name or range, then click Done.
- To delete the named range, next to the name, click Delete range .
- On the menu that opens, click Remove.
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.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.What is one advantage of using named ranges in a spreadsheet? ›
Named ranges have many advantages. They can help make formulas easier to understand, especially for those who are not familiar with Excel. Additionally, if the range of cells changes, you only need to update the named range once, and all formulas that reference it will automatically update.
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 is the purpose of range in 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.