Imagine a friend tell you his address this way: 25°47’N, 80°11’O instead of 1633 North Bayshore Drive, Miami.
The same applies to naming formulas, using the A1/R1C1 reference style is like using a coordinate instead of an address. For example: =A2*B2 vs. =area*height.
That’s not all, you have more reasons to use Excel Names in your Excel Formulas, take a look at these ones…
1) Write Formulas Easier and Faster
- Focus on the logic of the formula instead of selecting cells/ranges. Insert a cell/range with Paste Name Command (F3) or with Formula Autocomplete (Excel 2007)
- Select cells/ranges just once. This is the case when you have the same range more than one time in a formula. For example: =SUMPRODUCT((A2:A22>I9)*(A2:A22<I10)+(A2:A22))
- Write arguments directly from your mind. Named Ranges are stored more easily in your memory because they are meaningful (base, interest_rate, amount, etc)
You spend less time picking and configuring arguments:
2) Move/Copy/Paste Formulas Easier
You are not worried about setting the correct reference: $A$1, A$1 or $A1.
Once you define Excel Names properly you just copy and paste the formula.
A Range named in form of array is difficult to analyze because you don’t know which number from the array is used for the calculation.
3) Read and Maintain Formulas more Efficiently
- You can depict the formula meaning more easily in your mind, for example: the formula =base*height/2 is more meaningful than =A2*A3/2
- Names act as both reference and description; compare “A2” with “base” in the example above. Excel does not provide a method to add descriptions to formulas. Here’s an example:
The following formula calculates the Compound Interest Rate:
B2 = amount
B3 = number of years
B4= Interest Rate
Well, now see this formula: =amount*((1+numberyears)^interestrate) it explains itself.
Memorizing a list of long/complicated names that make no sense is difficult. In this sense, Excel Names are a kind of mnemonics.
Named Ranges will help you with interpreting formulas in better ways. Let’s see…
4) Write Shorter Formulas that Contain External Links
- Define the name, for this example: labcost
- Set the range “Refers to” as =’ [Labor costs.xls]Rocks I and II’!$H$13
- You are done
You can turn this formula:=’ [Labor costs.xls]Rocks I and II’!$H$13 into this shorter version: =labcost
How do you do it? Create a named range.