How Excel Names Range Work

Excel workbook is not just a handy place to store and organize your data but also provide a great flexibility to do lot more with your data. One Important thing is the ability to analyse a data in meaningful and handy manner.

Often, you will find yourself in a situation where you want to use a group of cells for more than one calculation. Excel enables users to give a valid name to refer to a specific list or group of cells within a complicated formula.

In this post, you will understand procedures of managing and referencing group of cells or data range in an Excel Workbook.

Following are key topics that we will be covering related to naming a range of data; the emphasis is to be straight forward without lengthy narratives.

Download Your Example Excel File

While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

Quick Introduction to ‘Name Range’

At the time when you need to manage and analyze a large data table, it’s often useful to identify a data range with a related name. Naming a cell range is an easy way to perform complex, nested formulas more decipherable.
For Example, if you have a list of Sales data year wise (2016 & 2017), you can name the sales value for each year with different names like Sales_2016 and Sales_2017 rather than referencing a cryptic cell address like =Sheet3!D4:D16.

Now whenever you want to refer Sales data of 2016 using a named range like to add sales data of 2016 only, we can use a formula such as =SUM(Sales_2016).

So instead of specifying each cell or range of cells every time in a formula, you can define those cells as a range (also called a named range).

Create Name Range in Excel
Create names for cells or ranges by using the New Name dialog box

Name: Type the shortest description as text field. 
Scope: The defined name Range will be valid to a worksheet or entire workbook.
Comments: A description about the Name Range.
Refers to: Range of cell address to refer by Name Range 

Entering a range name not just make reference easy but also reduce error; Excel returns error #NAME if in case a user enters an incorrect name reference.

Rules of Creating Names

Before we deep dive into Excel name range please remember to follow the Rules and guidelines for naming ranges include the following:

  • Range Names can’t contain spaces like ‘Year 2016’.
  • Symbols aren’t allowed except for underscores (_), backslashes (\), and periods (—).
  • You can use any combination of letters and numbers, but the name must begin with a letter, underscore, or backslash.
  • Neither a reference looks like a cell address like A2, Y16 or CY16
  • Names are not case sensitive. For Excel, ‘Year_2016’ and ‘year_2016’ is same.
  • Names can incorporate up to 255 characters.

Create a Named Range

The quickest way to create a Name range is by using Name Box. Remember to follow the rules while entering a named range in the Name Box.

In case of an Invalid name or characters, the Excel will pop an Error message.

Excel Error Message in case of invalid reference
Error Message in case of invalid reference

To create a Cell Name Range using Name Box

  • Go to worksheet ‘Sales’ of Excel working file (Image instructions below)
  • Select the range of cells C4:C7
  • In the Name Box (next to the formula bar), enter Sales_2016 (choose your name)
  • Click Enter Key

The Name box is a drop-down list that shows all names in the workbook. Although we can quickly name range using name box; for more control use the New Name dialog box.

Name Box is a quick way to name cells and ranges in Excel
Use the Name Box to quickly assign a defined name to cells or ranges.

Another way of creating named ranges is by using options available in the Defined Names group.

In our previous example, you created the name range of 2016 sales data. For this step, you need to name range 2017 sales data using the New Name dialog box.

To create a Name Range using the New Name dialog box

  • Go to worksheet ‘Sales’ of Excel working file (Image instructions below)
  • Select the range of cells C8:C11
  • Click on the Formula tab of the ribbon,
  • Click the Define Name under the Defined Names group
  • Enter a name Sales_2017 in the New Name dialog box
  • Click OK
Create Name Range using New Name dialogue box in Excel
Once you click Define Name, Excel suggests labels in an adjacent cell and cells range.
  • Do not forget to click Enter while creating named range using Name Box 
  • Remember that range references in formulas will not automatically update the corresponding defined names after designating a range name. Although you have the option to apply them quickly in Excel.

Using names in formulas

We can create formulas using de­fined names instead of cell or range references in formulas.

For Example, in the following formulas, the defined name replaces the range reference ‘D1:D20’.

Range reference =AVERAGE(D1:D20)
De­fined name =AVERAGE(Sales)

Let’s use range names in a formula. The task is adding total sales using both Range Name i.e. Sales_2016 and Sales_2017.

To create a formula using Name Range

  • Go to worksheet ‘Sales’ of Excel working file (Image instructions below)
  • Select Cell C14,
  • In the Name Box enter TotalSales (choose your name)
  • Then press the Enter key
  • In the cell enter formula ‘=SUM(Sales_2016, Sales_2017)
  • Press the Enter key again
Use Name range with formula in Excel
Use Named Ranges with Nested and Complex Formula
  • Excel adds the worksheet reference for you, but note that the cell reference stays relative, Excel uses absolute references if defined names is the cell reference (indicated by the dollar signs in the Refers To definition).
  • Once you name a range of cells you can not undo the action. You need to delete them separately.

Editing and Deleting Defined Names

With lots of data in hand, an Excel user may end up in a situation where you are interacting with any number of named cells and ranges. Although, we can verify name ranges using Name Box you can use the Name Manager dialog box that not only creates a new name but also edits or delete existing ranged names, and ­even alters the list of names.

Let’s continue with our previous example and practice to manage range names.

You have two range names Sales_2016 and Sales_2017 that define sales of a particular year. Although the name is descriptive, the task is to reduce the length by renaming them as Sale16 and Sale17, respectively. Moreover, also to delete the TotalSales name as it is not needed.

To Edit and Delete Defined Range name

  • Go to worksheet ‘Sales’ of Excel working file (Image instructions below)
  • Click on the Formula tab of the ribbon,
  • Click the Name Manager button (or press the Ctrl+F3 keys) that open Name Manager dialog box
  • Select Sales_2016 name
  • Then click the Edit button that open Edit Name dialog box
  • Edit Name to Sale16
  • Repeat Steps 4,5 and 6 to rename Sales_2017 to Sale17
  • Click TotalSales, and then click the Delete button. Confirm the dialog box to delete the defined name.
  • Click Close.
Name Manager provide ability to manage all defined names in the Excel workbook
Name Manager provide ability to manage all defined names in the workbook

Name Manager is a handy tool to manage the name of an Excel Table as well as already defined range names as part of complex and nested formulas.

Once you delete a ranged name, any formula in the worksheet referring to that name returns value #NAME error.

Create Names from Selection dialog box

Did you notice that it can become difficult to create the name of each group of cells if there are so many? The Name Box or New Name can only help in creating one name range at a time.

The Excel provides an ability to name several adjacent cells or ranges at once using row labels, column labels, or both just by clicking the Create from Selection button within defined names Group.

Assign multiple Named Ranges in Excel
Quickly assign multiple Named Ranges with labels in selected data range

While naming multiple range names, Excel assumes that selected labels are the name of each range.
For example, the above picture shows that Excel automatically selects name range options from Top row and Left column (in this case) labels of Profit table, creating a set of names for each Quarter and each State. 
A user can Select box as needed.

  • Keep in mind while using Create from Selection option, select the labels as well as the data.
  • You can view all names by clicking Name Manager button.

Download Your Example Excel File

Adding Names to Existing Formulas

It is very much possible that when you start working on an Excel Workbook, you may find there is no Name Range or did not use name range when you created a formula.

Later, you decide to name a range of cells, and you name it. However, the problem is that Excel doesn’t substitute the cell range with a named range in any formula automatically.

For Example, when you created names for each Quarter and each State using Create from Selection dialog box the Total column still refer to the Quarter’s cell range of each State.

In Excel, it is easy to replace cell range references with their corresponding names using Apply Names.

In this Exercise, we will apply or add name reference in the respective existing formula of Total column.

To apply name range in Existing formula

  • Go to worksheet ‘MultipleName’ of Excel working file (Image instructions below)
  • Click on the Formula tab of the ribbon,
  • Click Defined Name button arrow, and then click Apply Names; that open Apply Names dialog box
  • In the Apply names list, Select all five states
  • Click OK
Add or Apply Name Range to Existing formula automatically in Excel
Apply name to Existing formula from the list of Named Range

You can verify the Total sales column where name range is replaced to respective State in all five rows.

If you leave ‘Use row and column names’ box checked, the formula will contain too many characters and return an error.

Create Name with Constant

Name range not just give an ability to manage cell references but also create names that are defined by constants and formulas instead of by cell references.

For Example, you often pay a sales tax of 10.0% and you want to calculate the Tax amount of each state in the ‘Average_Tax_Rate’ column.

In this simple Exercise, we will carry the same example further to calculate total tax using the Tax formula.

Apply custom formula in Excel

  • Go to worksheet ‘MultipleName’ of Excel working file (Image instructions below)
  • Click on the Formula tab of the ribbon,
  • Click Defined Name button;
  • Type the name Tax in the Name box,
  • Then type 10% (or .01) in the Refers To text box.
  • Click OK
Create custom formula using New Name dialogue box Excel
Create custom formula using New Name dialog box
Create my or custom formula in Excel
Create your own formula and use in respective place quickly

You can also use name, text, functions, absolute and relative references as name definitions. We can refer a Tax rate of 10% to a cell (absolute reference) so that we can make changes If required without opening the Defined Name dialog box.

This is simple Example but we can include complicated formulas using INDEX formula, MATCH formula, VLOOKUP formula and including other Excel function.

To create a custom formula with the cell reference

  • Go to worksheet ‘MultipleName’ of Excel working file (Image instructions below)
  • Select cell J4
  • Click on the Formula tab of the ribbon,
  • Click Defined Name button;
  • Verify name as Average_Tax_Rate,
  • and Refer to: =MultipleName!$J$4.
  • Click OK

Now you can enter the Tax rate in cell J4, and Excel will update the Tax value accordingly every time.

Create custom your formula cell reference Excel
Names you define appear in the Formula AutoComplete list when you type a formula.

Note that named constants (Quarter or States) and formulas =Tax do not appear in the Name box adjacent to formula bar, but they do appear in the Name Manager dialog box.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments