Create Named Cells or Named Range in Excel and Use the Names in Formula or for Data Validation

In Excel, you can name a cell or a range of cells. Giving a meaningful name to a cell or a range helps understand the data or the set of data it holds. However, named cells have other useful benefits. Here in this post I’ll show you how to create Named Cells and Named Range in Excel and use the names in formulas or even use it for data validation.

Naming a cell in Excel is very simple. By default, cells have names, such as A1, A2 etc., which mean Column A, Row 1 and so on. You can name each cell according to your requirement.

Create a Named Cell

To create a named cell, simply select the cell (or set focus on the cell) you want to name and click the Name Box (before the formula box). Enter a name in the named box and press the Enter key. Pressing the Enter key confirms the change and Excel will store the new name.

Excel Named Box

Now, the cell A2 (before) has a new name called student. Excel will identify the cell with its name now. You can now use the name in a formula etc.

Create a Named Range

Naming a range is very similar to naming a cell. In a range, you will have multiple cells or a group of cells.

Named Range in Excel

Note: The Name of a cell or Range should be a single word. Do not add a space between words. If you wish to add two words then use an underscore (_) or hyphen (-).

Using a Named Range in a Formula

You can use a Named Range in a formula. This is very useful. For example, I have a monthly sales data for each product in my Excel workbook. I want to check the total sales of a particular product for a month and for the entire year.

First, I’ll create names for a range of data. The first range is named pen and second is named as apr. See the image below. Similarly, give names to other product range.

Two Different Named Range

Next, I’ll use the SUM() function (as formula) with the names to get the sum of all the Pens for the entire year and the sum of pens for a particular month. You can add the formula at the end of the table. For example,

1) To get the sum of pens for the entire year: =SUM(pen)
2) To get the sum of pens for the month of April: =SUM(apr)

Excel Named Range in Formula

Using a Named Range for Data Validation

You can use a Named Range in Excel for Data Validation also. With Excel’s Data Validation feature, you can actually create combo boxes or dropdown lists.

For example, I want to create a dropdown list for all my Products (see above images) using a Named Range. Why? This will ensure that my users will choose a product only from the list (and no mistakes).

I have already created a Products named range in the above example. I’ll use this name in this example too.

Note: You can use a named range anywhere in your workbook. Therefore, I’ll create the dropdown list in Sheet2, just to prove my point.

Now, follow these steps.

a) Go to Sheet2 (or any other sheet) and set focus on A2 cell. Since, I want to add a dropdown list in this cell.

b) From the top menu, click the Data tab and select Data Validation. This will open the Data Validation window.

c) Choose the first TAB Settings and select List (indicates dropdown list) under the Allow option.

d) Finally, in the Source box, type =products (the named range). Click OK.

Using Excel Named Range for Data Validation

The result: When you set focus in the cell A2, you will see a down arrow next to the cell. You will have to choose a product from the list. If you try to type or enter any other value, Excel will throw an error.

A Dropdown List in Excel using a Named Range

The advantage of using a named range for data validation is that you can add, edit or remove values from the list, with very little effort.

Add, Edit or Delete Named Range

Simply press Ctrl+F3 and Excel will show you the list of the Named Range you have created. It will open a Name Manager window.

Excel Name Manager

Select a name from the list and choose an option from the top of the window to either edit, delete or create new names.

Now, lets say I want to extend the products range by adding some New products.

1) Enter new products starting either from the next row or somewhere between the existing values.
2) Press Ctrl+F3 to open the Name Manager window.
3) Select the products name and choose the Edit… option. This will open a small window called Edit Name.
4) Set focus on the third option Refers to. It will automatically select the range of products.
5) Press the Shift key and click on the cells to add more products to the range.

Edit Named Range in Excel

Any changes that you make in the Named Range, will automatically reflect in the dropdown list that we have created.

Well, that’s it. Thanks for reading.

Previous - How to Get Live Stock Quotes in your Excel Worksheet using VBANext - Read Multiple Excel Files and Merge Data into a Single File using VBA

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!