How to Use the Excel Name Feature

The power of the Excel Name feature (sometimes referred to as a Named Range or Names) is a personal favorite of mine for saving time and improving quality when working in Excel.

In this blog post, I’m going to explain what the Excel Name functions is, why it is useful, and provide you with a few examples, and videos, of how they can be used.

What is the Excel Name Function?

Put simply, a “Name” in Excel is a meaningful title for a range of cells. Remember that a range of cells is a collection of cells (for more information, see one of my previous posts here). Sometimes it makes sense to reference a range of cells c multiple times throughout a Workbook. For instance, annual sales figures, months of the years, a collection of names and so forth.

Using the sales figure analogy, for instance, a worksheet might contain a range of cells that contain a list of sale figures in cells D23:D45. Constantly rekeying this range can be error prone. “Names” allows you to name a range of cells with something meaningful, for instance, “SaleFigures.” So rather than typing D23:D45 over and over, you would enter “SalesFigures” instead.

How to Create an Excel Name

There are a couple of different ways of creating a “Name,” but for now, we’ll stick with the most straightforward, which is to use the “Name Box” on the “Formula Bar” highlighted in the image below.

Excel Name Box

Let’s create our first “Name.” If we enter the months of the year in column A from rows 1 to 12 and select this range of cells, as shown in the image below, we are ready to create our first “Name.”

Months of the Year

Now that the target range of cells has been selected, we then click on the Name Box on the Formula Bar, enter the text “MonthsOfTheYear,” and click enter. That’s it, we’ve created an Excel “Name.”

Enter MonthsOfTheYear in Name Box

Naming your Name

Excel has certain rules that you need to abide by when naming your “Name.” The follow table lists them out.

RuleThing to Remember
CaseExcel Names are not case sensitive; this means that Excel will treat "MONTHSOFTHEYEAR," "monthsoftheyear," and "MonthsOfTheYear" all the same. Therefore, each Name has to use a unique sequence of characters irrespective of upper or lower case.
LengthA name can't exceed 255 characters. That's a lot of characters to play with.
SpacesYou can't have spaces in your Name. For instance, you can't use "Months Of The Year." To make your names a bit more readable, you could try using Camel Case as I have in the example above. Camel Case basically means no spaces and starting each new word in upper case. For more on Camel Case click here. Other options include using a period or underscore between each word. For example, "Months.of.the.year", or "Months_of_the_year".

For more information on Camel Case click here.

Examples

Okay, now for some examples.

In a Formula Example One

In the first example, I’m going to use a table of sales data for different products. First, we need to create a table of data to work with, so four columns. First column is months of the year, then three columns of sales figures for laptops, tablets, and desktops.

Sales Table Worksheet

Now, select the range of cells—in this case, A2:D13—click on the “Name Box” in the formula bar, type the Name, in this case “SalesTable”, and press Enter as shown in the image below.

SalesTable Name Entry

To check that the Name has taken, click away from the range of cells, then select cells A2:D13. When we’ve finished selecting the range of cells, the name should appear in the name box like as highligted in yellow in the image below.

SalesTable Selected

Okay, on a different worksheet, we’re going to create a little formula that will search for a specific month, then move across the columns of the sales table and display the sales information. To do this, we will use the VLOOKUP function. If you’re not familiar with the VLOOKUP function, check out my block post at the following link.

The formula we will use will look like this: =VLOOKUP(A1,SalesTable,2,False). To do this, we click on a new worksheet. We’ll call it “VLOOKUP Example 1.”  In cell B1, we type the formula =VLOOKUP(A1,SalesTable,2,False), just like the image below.

Enter VLOOKUP Formula

Now, we type a month into cell A1, and hey presto, we should see the sales figure from the sales table.

Enter March in Cell A1

Extending the first Example

Ok, the first example was a bit simplistic, so let’s tidy things up a bit. First, select a new worksheet. This time, we’ll call it “VLOOKUP Example 2.” This time, we’ll add some headings and a bit of formatting like the image below.

Tided Up Worksheet

Now, in the following cells type the formulas as shown in the table below.

CellFormula
B2"=VLOOKUP($A2,SalesTable,2,False)"
C2"=VLOOKUP($A2,SalesTable,3,False)"
D2"=VLOOKUP($A2,SalesTable,4,False)"

Notice that the only thing that has changed in the formula for each cell is the col_index_num attribute of the VLOOKUP function (see image below).

Col_index_num

When you’ve done this, type a month’s name in cell A2 and you should see the sales figures that correspond to that month as in the image below.

Sales Figures

Again, this might seem very simplistic but imagine if you had sales figures dating back 10 or 15 years.

The following video demonstrates how to create a Name in Excel.

Using Names in a List

In the example above the month could be entered incorrectly. It could be misspelled, for instance. To get around this, we are going to create and use a drop-down list of month names. First of all, we’ll go back to the first worksheet and create a new Name. This time, we’ll select cells A2:A13, and click on the “Name Box” on the formula bar, type the Name, in this case, “MonthsOfTheYear” and press enter, as shown in the image below.

MonthsOfTheYear

Now we’re going to create the drop-down list. Click on cell A2 of worksheet “VLOOKUP Example 2”. This is the cell that we typed the month into. To create a drop-down list, we need to click the “Data” tab of the ribbon, then click the “Data Validation” command. See the image below.

Data Validation - Mac

Data Validation – Mac

Data Validation - Windows

Data Validation – Windows

When we’ve clicked the “Data Validation” command, we should see the Data Validation Menu.

Data Validation Menu - Mac

Data Validation Menu – Mac

Data Validation Menu - Windows

Data Validation Menu – Windows

From the “Allow” dropdown down box, select “List”.

Select List - Mac

Select List – Mac

Select List - Windows

A text box should now be visible. In the text box with the title source, type  “=MonthsOfTheYear” and click enter.

Source - MonthsOfTheYear - Mac

Source – MonthsOfTheYear – Mac

Source - MonthsOfTheYear - Windows

Source – MonthsOfTheYear – Windows

Now, click on cell A2, and you should see a drop-down list of months that you can select from. Click on any month from the drop-down list, and the sales figures should appear.

Drop-down list of months

Drop-down list of months

In the text box on the “Data Validation” menu, we could have typed “=A2:A13”, and it would have worked just the same. However, the Name is easier to remember than a range of cells. Plus, if the spreadsheet is being maintained by somebody else, the Excel Name can be more meaningful to a new user as a reference to the purpose of the cells than simply a reference to a range of cells.

The following video shows how to use a Named Range in a drop-down menu.

Managing Excel Names

What if we want to extend our range of cells because we have new values to add? For example, instead of just months, we might have months and years such as January2016, Febuary2016, and so on. Excel has a tool for that, and it’s called “Define Name” on Macs and “Name Manager” on Windows. Note that Windows does have an option called “Define Name” as well, but we want to click “Names Manager.”

Start by clicking the “Formulas” tab on the ribbon, and click either “Define Name” on the Mac, or “Name Manager” on Windows, as shown in the images below.

Define Name - Mac

Define Name – Mac

Name Manager - Windows

Name Manager – Windows

You will then get a pop-up menu called either “Define Name” on a Mac or “Name Manager” on Windows. From here, you can select the specific “Name” you want to manage; for example, you can change the range of cells that it references.

Okay, let’s change the “SalesTable.” On the “Table” worksheet, we’re going to add a new column, column E. We’ll give this column the heading “Mobiles” and then add some sales values for each month, just like we have done in the image below.

Add Column E to SalesTable

Now we need to modify the “SalesTable” Name to add the new column. So, as mentioned above, click the “Formulas” tab on the ribbon. Either click “Define Name” on the Mac or “Name Manager” on Windows. Next, select “SalesTable” and click in the text box below the title “Select the Range of cells:” on the Mac, or “Refer to:” on Windows as shown in the images below.

 

Select the range of cells - Mac

Select the range of cells – Mac

Refers to - Windows

Refers to – Windows

From here, we’re going to change the range of cells the “SalesTable” references by changing the range of cells from “=Table!$A$1:$D$13” to “=Table!$A$1:$E$13” as shown in the images above. Now just press enter.

That’s it; the “SalesTable” name has been updated. Now if we go back to the “VLOOKUP Example 2” worksheet, we can add a couple of new cells as shown in the image below to display the Mobile sales figures.

Mobile Sales Values

Once this is done, we can add the formula “=VLOOKUP($A2,SalesTable,5,1)” to cell E2. The only difference here is that we’ve changed the col_index_num attribute of the VLOOKUP function to pick up the “Mobile” sale figures of the “SalesTable”. Everything else works just fine; the old formulas are now referencing the updated range of cells that the “SalesTable” name refers too.

The following video shows how to use the “Define Name” menu in Excel for Mac.

The following video shows how to use the “Name Manager” menu in Excel for Windows.

 

That’s it for this blog post. I hope you found it useful. In a future blog post, I will discuss how you can use “Dynamic Ranges” in conjunction with “Names” to save time and improve quality even further.

If you liked this post, please share it with your friends on Facebook or Twitter.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit