Usually we use cell numbers in formulas whenever we work on an Excel spreadsheet. Remembering each cell number and what it contains can be really hard. However, 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:
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:
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 labels.
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.
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.
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:
2. By selecting from the Use in Formula list:
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.
Do you find this information useful? Share it with your friends by on Facebook, Google+, Twitter or other Social Media. You can also follow me on Twitter @sarayoo.info or Google+ or Like me on my Facebook or on my LinkedIn 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.