Tutorial Details
Downloadable Files: Excel File
Getting Started in Excel
How to Enter, Manage, and Find Data in Excel
Introduction to Formatting in Excel
Introduction to Formulas and Functions in Excel
Creating Charts in Excel

Naming Cells in Excel to Make Using Formulas/Functions Easier

Add to Favorites
Author:

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