O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #41. Create Custom Functions Using Names

Although referencing data by name is convenient, it's sometimes more helpful to store a constant value or even a formula, especially if you've been creating custom functions in VBA.

Assume you have a tax rate of 10%, which you need to use throughout your workbook for various calculations. Instead of entering the value 10% (0.1) into each formula that requires this tax rate, you can enter the word TaxRate and Excel automatically will know that TaxRate has a value of 0.1. Here is how to do this.

Select Insert Name Define, and in the Names in Workbook: box, type TaxRate. In the Refers To: box, enter =0.1 and then click Add.

From this point on, you can enter any formula into any cell, and instead of adding 10% as part of the calculation, you can use the word TaxRate. Probably one of the biggest advantages to using this method is that if and when your tax rate increases or decreases, and your formulas need to reflect this new percentage, you can select Insert Name Define, then select the name TaxRate and just modify it to suit.

To take things a step further with this concept, you can use formulas as your Refers To: range rather than a cell address or constant value. Suppose you want to create a name that, when entered into a cell, automatically returns the SUM of the 10 cells immediately above it.

Select cell A11 on any worksheet and then select Insert Name Define. In the Names in Workbook: box, type the name Total. In the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required