Tips for Excel, Word, PowerPoint and Other Applications
Using Named Formulas in Excel
Why It Matters To You
Most people are familiar with how to name data ranges to make formulas easier to manage. However, you can also use the same naming functions to name formulas. Why are all the cool people doing this? You may have a number of formulas that are written identically. Typically, if you want to modify the formula, say a commission schedule, you have to change them all and that requires that you remember where all instances of that formula exist. With a named formula, you only have to change the formula in one spot, and all instances will adopt the changes. Named formulas also allow you to change what calculation is performed in a cell with a CHOOSE function, which is pretty neat in and of itself. We'll talk about this later.
How To ...
We've discussed the naming cells or ranges of cells before. Naming formulas isn't that much different. Here is a sample file to help you follow along.
To name a formula, go to Insert: Name: Define (ALT+ I, N, D). Start with the Names in workbook box and give your formula a name. In my example, I'm going to name it "add". Next, go the the box labelled "Refers to:", type in the formula you want to execute. You have to be careful here. By default, it makes any cell you click on while building the formula, an absolute reference. You may have to delete the sheet reference and use F4 to remove the $ signs. For complex formulas, you may want to build them in a cell first, test it, and then copy it into the Define Name window.
To use a named formula, just enter =[formula name] into any cell and it will execute as if you wrote the formula in the cell. Open the sample file and type in =multiply in any cell and see what happens.
The sample file also has a little twist built into it. Using a CHOOSE function and a number of named formulas, I can create the ability to execute a number of different calculations based on a user input. I'll just throw that out as a point of interest.
There are a couple of rules. Like named ranges, you cannot use a name that starts with a number and the name has to be contiguous. If you have several words, you'll have to connect them with an underscore.
Notes
| Last updated | 7/23/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |
Using Named Ranges in Excel CHOOSE |