Creating and Using 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, and D4 etc. The answer is YES and the concept is called “Named Ranges”. In this post we will see how to create named ranges, why to use named ranges and the most important thing how named ranges will improve my efficiency or make my work easier.

Why should I use Named Ranges

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

  1. Easy to remember – A range named as Employee Name is much easier to remember instead of identifying the reference as B2:B200.
  2. Makes navigation and selection easier – All names can be accessed through the name manger or the name box. You can directly select/ move to the particular range.
  3. Formulas are much easier to write/ understand – Instead of the cell references a named range can directly be used in the formula.

Rules for Creating a Name

There are few things to be noted while creating/ defining names.

  1. No spaces are allowed. Instead you can use _ as an alternative. Ex: “Emp_Name” instead of “Emp Name”.
  2. Name should start with a letter or underscore (_) or a backslash (\). Numbers can be used as a part of name but a name cannot start with a number.
  3. Names are not case sensitive. “PROFIT” and “profit” is identified and treated as the same way by excel.
  4. Cell references such as A2, F$100, R1C2 etc. cannot be used as names. Also, characters “C”/ “c”/ “R”/ “r” cannot be used as names as they are used as shortcuts to identify the row and columns.
  5. Length of a name can be up to 255 characters.

Creating/ Defining a Name Range

Naming a range can be done 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 a named range.

Name Box: 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. Know more about excel terminology in case if you are not familiar about excel terminology

Name box in excel
Name box in excel

Let’s see the step by step procedure.

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

Step 2: Go to the name box and type the name you wanted in it. Press Enter.

Step 3: Click the down arrow in the name box and check if the name is being seen or not.

Step 4: Click on the name and you can see the range getting 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 ranged for the data.

Creating named rages in excel 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/ Labels

Imagine in the same 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, is there an easier way to do this?

The answer is YES J.

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.

Step 1: Select the data.

Step 2: Go to Formulas tab.

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

Namedranges from selection or labels

Step 4: A pop up opens with options to choose named ranges. Check the ones which you need and click on OK.

Namedranges from selection or labels

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

See the below image to understand the steps.

Namedranges from selection

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

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.

Got to Formulasà Defined Names à 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.

Named range-name manager

  1. New: To create a new named range. On clicking New a dialog opens with 3 options as shown in the image below.
    1. Name: Name of the Range
    2. Scope: Limit of the range whether to a sheet/ workbook. By default the scope of named ranges is for the whole workbook.
    3. Refers to: The range for which the name should be given.

      Named range-name manager1

  2. Edit: To edit an existing named rage. The options of the Edit dialog box remains the same.
  3. Delete: To delete an existing named range.

Alternatively you can use the Key Board Shortcut “CONTROL + F3” for opening the Name Manger dialog box.

See the below image to see how to use Name Manager.

Named Ranges-Name Manager

Download the Practice Workbook used in Example

What are your thoughts on Named Ranges?

Please do share your thoughts on Named Ranges in the comments.

2 Replies to “Creating and Using Named Ranges in Excel”

  1. […] Ref_text : This can be a cell reference, text/ string or even both which is used to create a reference to a cell/ range. The range which is being referenced can again be a cell/ text/ range or Named Range (Read this post to know more about Named Ranges). […]

Leave a Reply