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 percent, 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 will automatically know that TaxRate has a value of 0.1. Here are the steps:
- Select the Formulas tab and then select Defined Names → Define Name (pre-2007, Insert → Name → Define).
- Type TaxRate in the Names: box.
- 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 percent 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 newpercentage, you can select the Formulas tab, choose Defined Names → Define Name (pre-2007, 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. Follow these steps:
- Select cell A11 on any worksheet, right-click, and go to Name a Range (pre-2007, select Insert → Name → Define).
- In the Name: box, type the name Total, and in the Refers To: box type =SUM(A1:A10). Click OK.
- Enter any 10 numbers in any column starting from row 1.
- Nowcome down to row11 of the same column and type =Total. The name Total automatically will return the SUM of the 10 cells you just entered in A1:A10.
If you want to create a similarly named formula that is not restricted to only 10 cells, but rather, includes all the cells directly above whatever row happens to contain =Total, follow these steps:
- Select cell B11, go to the Formulas tab, and select Defined Names → Name Manager (pre-2007, select Insert → Name → Define). Click the name Total.
- Examine the Refers To: box, which will say =SUM(B1:B10). This enables you to create named formulas. In other words, because you did not make the column references absolute for the original name Total, it always will reference the column you use it in.
- Now, click the Refers To: box and change the formula to =SUM(B$1:B10). Click OK.
From this point on, you can select any rowin any column other than row1 and enter =Total, and you automatically will get the SUM of all the cells above where you enter this, regardless of how many rows there are. This is because you anchored the rownumber 1 by making it an absolute reference, yet left the reference to cell B10 as a relative reference, meaning it always will end up being the cell immediately above where you entered the named formula =Total.
