Naming Cells in Excel to Make Using Formulas/Functions Easier

Add to Favorites
Author: don

In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell which you have given a custom name to.

This cell can then be referred to by its new custom name within and Formula or Function. For example, I have a table of orders and at the bottom are my totals:

I could use the cell references C12 and B12 calculate the average OrderTotal. Or I could Name those cells beforehand and use the Names as my reference. To Name a cell, select a cell then navigate to the Name Box. (Highlighted by the red box below):

You can then edit the Name of the cell. Just hit enter to save the Name. (C12 is now named Total)

This also works with a range of cells. Just select the range then edit the name in the Name Box:

It is important to note that certain symbols will not be permitted including spaces. In the above example I used an underscore (_) instead of a space. This window will pop-up if the Name you entered is invalid.

Once you have entered an acceptable name for the cell, this can then be used in formulas:

 

In the above example I Named cell B12 to No.Orders and C12 to Total. I then used these new Cell Names as a reference in my average formula. This also works with Functions and Named cell ranges if applicable to the Formula/Function. I gave the range C2:C11 the Name of OrderTotals. I then put this name into a Sum Function:

Manage Lots of Names in the Workbook

As you create more and more Named cells you may lose track of where they are. This is when the Name Manager tool comes in. If you go to the Formulas tab of the Excel ribbon there is a Defined Names section:

If you click the Name Manager button the following window opens:

This helps you keep track of all the Named cells/ranges within your current workbook. You can see all the Names you have created as well as the cells/ranges they refer to. Double click on a Name to edit it or alternatively select the Name then click the Edit button.

You can also delete a Name by selecting it then click the Delete button.

More Complex Names in Excel

You can also define Names and use them in formulae by using the Define Name and Use in Formula buttons on the Formula Tab. Define Name is also available when you select a range of cells and right-click. This opens a New Name pop-up similar to the Edit Name pop-up.

For this new Name I used a constant for the value instead of a cell reference. Formulas/Functions can also be entered as a reference for a Name:

You can also alter the scope of your Names. In the above example I have kept the scope of the TotalTax Name to Sheet1. This means that the TotalTax Name can only be accessed/referred to on Sheet1. If you go to Sheet2 and try to use the TotalTax Name it will not be recognized. If the scope of the Name had been set to Workbook then the TotalTax Name would have been accessible from anywhere in the Workbook.

You can also enter a message in the Comment box above and that will display when you go to enter the Name into a formula or function in Excel.

Dont forget to download the file for this tutorial so you can follow along and see how everything works.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Select Ranges of Cells in Excel using Macros and VBA
Tutorial: This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This ...
Combine Multiple Chart Types in Excel to Make Powerful Charts
Tutorial: In this tutorial I am going to show you how to combine multiple chart types to create a si...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Quickly Copy the Last Action to Multiple Cells in Excel
Tutorial: In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y ca...