Thursday, 22 November 2012 14:00

How to Use Names in Excel Formula and Make it Easier to Understand

Written by
Rate this item
(0 votes)

using defined names in excel

Usually we use cell numbers in formuals whenever we work on an Excel spreadsheet. Remembering each cell numbers and what it contains is really hard. There is an easy way - you can use names in formulas instead of cell numbers. For example: imagine that cell C10 contains Price and cell D10 contains Quantity and you need the Total in cell E10. You can use "Price x Quantity" in cell E10 instead of "C10 x D10". This makes your formulas much easier to remember and understand.

Understanding Names in Excel

A name in Excel is a meaningful shorthand that is easier to remember and understand and can be used for cell referencing, constants, formulas or table. There are two types of names used in Excel, Defined names and Table Name. This article covers Defined names and how it can be used in formulas.

The following shows the use of names in formula:

using defined names in excel

The Scope of Defined Names:
The scope of a defined name is the location where the name is recognized without specifically qualifying it. You can define the scope of a name, for example, Price, throughout the current sheet or the entire workbook. If you define the scope of a name to the entire workbook, then you cannot define another cell or range of cells using the same name. If you define a name to the current sheet only (e.g. Sheet1), then you need to qualify (e.g. Sheet1!Price) the name in order for you to use in another sheet (e.g. Sheet2).

How to Define Names in Excel

There are few ways to define names in an Excel spreadsheet:

1. By using the Name field in Formula bar

You can simply select a cell or a range of cells and type a meaningful name in the name field in the formula bar as shown below:

using defined names in excel

2. By using "Create from Selection"

This is very useful especially when you are working on a large spreadsheet. You can define names for the worksheet by selecting the entire range of cells which has it's own l abels.

To create names from selection:

Step 1:

Ensure your worksheet has proper labels for each columns and rows that you want to define names. Then select the range of cells / table / list.


Step 2:

Click on Formula Tab | Create from Selection | Under "Create names from values in the: " window, select Top row and Left column, since the labels in the example shown here are in the top row and left column. And Click OK.

using defined names in excel

3. By using New Name Dialogue Box

You can also define names for a cell or range of cells by using New Name dialogue box in Formula Tab.

Step 1:

Select the cell or range of cells

Step 2:

Click on Formula Tab | Define Names | Use the suggested name or type the name you want and click OK.

Using Defined Names in Excel

Naming Rules:
Certain rules must be followed when you define names in Excel:
1. The first character of the name must be a letter or an underscore (_) or a backslash (\). Remaining characters can be letters, numbers or periods.
2. Cannot use cell references, for e.g. cannot use name "B100", "$B$100", etc.
3. Spaces are not allowed. For example, if you need to name Total Price, you must name it either "Total_Price" or "TotalPrice" or "Total.Price".
4. You can only use names that contain 255 characters maximum.

How to Use Defined Names in Excel

Once you define names in your spreadsheet, you can use those defined names in formulas. If you already have formulas in the spreadsheet, you need to apply names by clicking Formula Tab | Click on the small arrow beside Define Names | Apply Names.

You can use the defined names in formulas in many ways:

1. By simply typing the name in a cell as shown:

using defined names in excel

2. By selecting from the Use in Formula list:

using defined names in excel

Deleting and Managing Defined Names
You can either delete or make changes to the defined names by going to the Name Manager dialogue box in Formula Tab | Name Manager. You can re-define the cell or range of cell for a name by selecting the defined name and click Edit.

using defined names in excel

Do you find this information useful? Why don't you tell your friends by sharing it on Facebook, Google+ or Twitter. You can also follow me on Twitter or Google+ or Like me on my Facebook for  more updates, technology tips and tricks, iPhone, iPad, other iOS devices tips, iOS App Deals, Blogging tips, etc. Please leave your comments in the comment section or contact me if you have any other questions.


Read 12756 times Last modified on Monday, 03 December 2012 17:08
Sunday the 28th. © 2012. Reproduction without permission is not permitted. All rights reserved.
Copyright 2012


Joomla template poker