How to Create and Use Named Ranges in Excel

We know that every cell in the spreadsheet has a unique name by default. This name is nothing but the combination of the row number and the name of the column. Now the question is can I have my own name(s) assigned to a cell/ a group of cells (range) instead of the default name such as A1, C2. The answer is YES and the concept is “Named Ranges”. In this post we will see how to create named ranges in excel. Why to use named ranges and how named ranges will improve my efficiency or make my work easier.

Download the sample workbook to follow along.

Why should I use Named Ranges in Excel

Naming a range in Excel has many advantages and makes our job much easier. Here are a few advantages of using named ranges in excel.

• Easy to remember – A range named as Employee Name is much easier to remember instead of identifying the reference as B2:B200.
• Easy to navigate and select – You can access the names through the name manger or the name box. You can directly select/ move to the particular range.
• Easy to write/ understand formulas– Instead of the cell references you can use a named range directly in formulas.

Rules for Creating Named Ranges in Excel

There are few things to remember/ make a note of while creating/ defining names for named ranges in excel.

• You cannot have spaces in named ranges. Instead you can use an alternative like _ (Underscore) or Hyphen (-).
  • Example: Use “Emp_Name” instead of “Emp Name”.
• Name Cannot start with a Number. Instead a name can start with a letter or underscore (_) or a backslash (\).
• Names are not Case Sensitive. Upper case and Lower case is considered as one and the same.
  • PROFIT” and “profit” is identified and treated in the same way by excel.
• Cell references such as A2, F$100, R1C2 etc. cannot be used as names. Also, characters “C” or ”c” or ”R”or ”r” cannot be used as names as they are used as shortcuts to identify the row and columns.

Creating or Defining Named Ranges in Excel

You can create a named range in different ways.

1. Using the Name Box.
2. Creating from a Selection/ Labels and
3. Using the Name Manager.

Let’s see each of them in Detail.

Creating a Named Range Using the Name Box

This is the easiest way to create named ranges in excel.

What is a Name Box in Excel

The box on the top left adjacent to formula bar on the spreadsheet is the name box. This shows the name of the cell selected.

Suggested Reading: Read and Understand more about excel terminology

Let us see the step by step procedure.

Select the range for which you want to give a name.

Go to the name box and type the name you want to have in the name box. Press Enter.

Click the down arrow in the name box and check if the name is reflecting in the dropdown or not.

Click on the name and you can see the range is selected.

Example:

The data in the below image shows the quarterly sales for 10 products. See the below image to understand how to create a named ranges in excel for the data using name box.

Download the practice workbook and try creating named ranges in the same way for the rest of the quarters and products as well.

Creating a Named Range from a Selection or Labels

Imagine in the above example if we have monthly data instead of quarterly and you need to create 12 named ranges for each month.

Heyyy! I just learnt how to create a named range. I will do it for 12 months with ease. Hold on!!!

If the data extends for 2 years (24 months) then, how much time we need to spend in creating named ranges for 24 months. Still manageable? As the size of data increases the time we need to spend increases right. Remember, we are using Excel to reduce manual work not to do donkey work.

Then comes the question, Do we have an easy way to do it. The answer is YES.

There is a much simpler way to create named range(s) from a range. Excel does all the necessary work to make our work much easier. Let’s see how to do it.

Select the data.

Go to Formulas tab.

In the defined names section, Click on “Create from Selection” option.

A pop up opens with options to choose named ranges. Check the ones which you need and click on OK. Refer to below image. I want to create named ranges for months and product as well. So I would select top row and left column

Check the name box and you can see the names created based on each option selected.

See the below image to understand the steps.

Try creating named ranges in the same way for the data in the sheet 2 (Named Range-Selection) in the practice workbook.

Using the Name Manger

The 3rd way to create named ranges in excel is using the name manager.

Using Name Manager to create named ranges provides us with the option to customize our named ranges unlike the ones created using the name box and selection options.

• Go to Formulas -> Defined Names Block -> Define Name – opens a window to create named range directly.
• Go to Formulas -> Defined Names Block ->Name Manager

On clicking Name Manager a dialog box opens with different options. Also, you can see if any named ranges exist in the work book.

Different Options in Name Manager window are as follows.

New: To create a new named range. On clicking New a dialog opens with 3 options as shown in the image below.

• Name: Name of the Range
• Scope: Limit of the range whether to a sheet/ workbook. By default the scope of named ranges is for the whole workbook.
• Refers to: The range for which the name should be given.

Edit: To edit an existing named rage. The options of the Edit dialog box remains the same as new.

Delete: To delete an existing named range.

Alternatively you can use the shortcut Control key + F3 to access the name manager.

See the below image to understand how to use name manager to create or modify named ranges in excel.

Sample / Practice Files

Click the below button to Download the Practice Workbook used in Example

What are your thoughts on Named Ranges in excel? Do share in the comments.