By Dennis59
This is a really basic thing, but I’ve personally seen very, very few Excel users taking advantage of it.
To add a “name” to a cell, simply click in the Name Box after you’ve selected a cell (or cells). (The Name Box is the white rectangle in the upper left just below your toolbars. If you have selected cell G6, the Name Box will be displaying G6.)
When you click in the Name Box, the G6 will become highlighted, and at that point you can type whatever name you want for that cell.
There are some limitations on what you can use for a name. Here are a few of them:
- no spaces are allowed (but underscore character or period works fine to separate words)
- you can’t type a name that is a valid cell address like HI22, (but HI_22 or HI.22 are OK)
- you can’t start the name with a number digit or a symbol (period, comma, asterisk, etc)
[You’ll find other limitations by trial and error! (one of the most foolproof ways to learn software in my opinion)]
Now that you’ve got your cells named, you can write equations that will still make sense to you 5 years from now when you have to resurrect your old spreadsheet.
If you name your cells before writing your formulas, then Excel will automatically use the names for you if you create the formulas by clicking on named cells.
For example
say you’ve named A1 as Width, A2 as Length, and A3 as Height.
you want to calculate the volume in cell G12.
Click on G12 and type the ‘equals’ sign
now click on A1 >> the formula bar will display {=width}
type an asterisk
click on A2
type an asterisk
click on A3
the formula bar will display =Width*Length*Height
hit Enter
5 years from now when you click on G12 and see
= Width*Length*Height
it will make a lot more sense than
=A1*A2*A3
If you’ve already got a spreadsheet and you think that adding names will help you to decipher it (or make it more user-friendly) you can add them after the fact. Click on a cell that you think needs a name, and give it one. Repeat until you’ve named all the cells you want. Now go to Insert>Name>Apply… and you will see the Apply Names dialog box. All of the names on the spreadsheet will be listed. Click on each and every one, and then click ‘OK’. Excel will look to see if any of the named cells occur in formulas. If so, it will replace the old alphanumeric name with the new intelligent name. (This usually works fairly well, but not flawlessly in my experience.)
This is just a simple example. As you are working with more complicated stuff like lookups and conditional statements, names can be extremely helpful.