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.

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 a 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:

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

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:

• 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:

• 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.

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:

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:

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

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.