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.
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.
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.
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)
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.
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.
• 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.
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.
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.
Any changes that you make in the Named Range, will automatically reflect in the dropdown list that we have created.