Using Excel Names
When using formulas or referring to a specific cell in your worksheet, you’ll typically reference ‘range’ or specific cell. For example, B6:B10 is a range reference, where B6 is a cell reference.
A problem with this sort of reference is that it’s not very meaningful and easy to forget. If you want to refer to a range several times in formulae or functions, you may find it necessary to write it down, or select it, which often means wasting time scrolling around the workbook.
Excel provides a solution to this problem: Names. Names make formulas much easier to understand and maintain. They’re similar in function to using Absolute References. However, because Names are literally words, what is being referenced is much more clear.
=SUM(Sales) vs =SUM($E$14:$E$48)
Creating Names can be accomplished in a number of ways. Let’s first look at the Rules that are required, and save how to create Names for another post.
Rules for using Names in Excel
There are FIRM rules, and expections to the rules. You can make it easy on yourself and remember just the BOLD portion of the following rules.
- The first character of a name must be a letter.
- Exceptions: the underscore (_) or a backslash (/)
- Remaining characters can be letters or numbers.
- Exceptions: the underscore (_), or a period (.)
- 255-character length
- Each name must be unique
- Excel doesn’t distinguish uppercase to lower case. Sales, SALES, and sales would be duplicate names.
- They can’t be the same as a cell reference; i.e. QTR1 is already a cell name, but QTR_1 would be okay)
Names in Formulas
As mentioned previously, Names are similar in function to using Absolute References. But they’re literally words, making what you’re referring to very clear. Here you can see an example of where cell C3 was named DueDate.
Formula
=B6-DueDate
The cell reference B6 is a relative cell reference, allowing you to copy the formula down the list. B6 would update to B7, and so forth. The cell named DueDate would stay locked as if it were an absolute reference.
Names in Functions
Look at this example of a VLOOKUP function.
Bonus_Table refers to the range $B$2:$C$5.
The image shows B8 as the cell evaluated, and the range Bonus_Table is where both the B8 and the bonus rate are found. (The VLOOKUP function is another post altogether).
The data you work with every day will be different than these samples. I’m sure you use many formulas and functions and you’re eager to learn how to create names… Sit tight. I’ll get the post for Creating Names posted shortly.
As always, we’re available to help you with your individual questions. With over twenty years of experience, we are proud to have clients in many industries including: Real Estate, construction, manufacturing, non-profits, and research and development.
You can reach us by calling or texting 775 . 525 . 0885 or emailing info [at] MyRenoComputerTutor [dot] com
We’re dedicated to helping people understand what they’re doing. So please reach out. You can contact us via email, or voicemail, and you can even text us to get your training scheduled. Diana also trains for Washoe County, City of Reno and UNR Extended Studies.
