Named Range in Excel

In this article we are going to learn about Named Range in Excel. Named Range gives the Excel users a privilege to provide names to a specific cell, cells (defined range of cells), formula or constant value. Named Range is supported in all versions of Excel. This article will brief you about why and how to use named range.

When and Why to Use Named Range:

When you normally enter data into columns of a spreadsheet you tend to give column headers so that the data in that particular column is much understandable. But when you use this data for various functions then all you use is the data range instead of the column headers. Named range is the name given to list of data, you can use this name in formulas to make them easier to understand.

Have a look at below image to get an insight. In this example, I have given a Name – “Sales_of_Apple_in_US_in_KGS” to the sales of apples in US available from Jan 2014 to Sep 2014, making it easier to understand.

Example of Named Range

So, this is how a “named range” makes your formula easier to understand.

How to create a Named Range:

You have two methods to create a named range in excel. We have detailed both of them in this section.

Method 1: Create Named Range using “Formulas Tab” Define Name Option:

You need to follow below listed steps to create a “Named Range” in your excel using “Define Name” option under “Formulas Tab”.

  • Select the “Data range” which you want to name.
  • Click on “Formulas Tab” available on Excel Ribbon.
  • Click on “Define Name” button as shown in below image:

Adding Name Range using Define Name Option

  • Now enter the desired name for named range in “Name” text-box and click on “OK” button once done.

New Name Window

Now, your data range is having a name that could be easily referred in formulas.

Method 2: Create Named Range using Name Box:

If you are looking for a much easier way of adding named range to your data instead of hopping onto a number of tabs, then this method is definitely for you.

Follow below steps to create a named range using name box:

  • Select the “data range” for which you want to create a named range.
  • Click on the “Name Box” which appears next to the “Formula bar” wherein the cell number is usually updated as shown in below image:

Add Name Range using Name Box

  • Now enter the “name” of this range and hit “enter button”.

That’s all your data range will be named after this and could be easily referred.

Name Manager in Excel:

Using Name Manager you can list all the “Named Ranges” that are present in the current workbook. Name Manager gives you a wide variety of operations to perform, which are listed below:

Name Manager

  • Create – Using this you can create new ranges in the workbook.
  • Edit – Using this you can edit any existing range created in your workbook.
  • Delete – As the name implies, using this you can delete the existing names created in your workbook.
  • Filter – Filter gives you many options as depicted in below image to sort the listed names.

Name Manager Filter Options

How to Use Name Manager:

There are two options available to check the name manager.

Method 1: Selecting Name Manager from Excel Ribbon:

Below listed steps need to be followed to view Name Manager:

  • Navigate to “Formulas Tab” in Excel.
  • Click on “Name Manager” button available as shown in below image:

Name Manager from Excel Ribbon

Method 2: Keyboard Shortcut for Name Manager:

Name Manager could be used easily by holding “Ctrl + F3” Keys together.

Deleting Named Ranges:

Named Ranges could be deleted using Name Manager only. All you need to do is to follow below steps to delete any named ranges available in your excel workbook.

  • Select “Name Manager” from “Formula Tab” or by holding “Ctrl + F3” Buttons together.
  • Once the “Name Manager Window” opens, select the range which you want to delete.
  • Click on “Delete” Button available on “Name Manager Window” as shown in below image:

Delete Name Range from Name Manager

  • Click on “OK button” on the Confirmation Page.

Confirmation Window for Deletion of Name Range

After this the selected “Name” will be deleted from your workbook.

So, this was all about Named Range functionality and various ways to use it in Excel.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About