Categories

# Define Names to Store a Value or Formula

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.

1. Define Name to Store Value
2. Define Name to Sum Cells
3. Generate Names From the Selection

## Named Value

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:

1. Click on the `Formulas` tab and in the “Defined Names” group, click on `Define Name`.
2. In the Name box, type `TaxRate`.
3. In the Refers To box, enter `=0.1`
4. Click OK.

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`.

## Define Name to Sum Cells Automatically

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.

1. Select cell `A11` on any worksheet.
2. Click on the `Formulas` tab and in the “Defined Names” group, click on `Define Name`.
3. In the Name box, type `Total`.
4. In the Refers To box, enter `=SUM(A1:A10)`.
5. Click OK.

Enter any 10 numbers in any column starting from row 1. Now come down to row 11 of the same column and type the following: `=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.

1. Select cell `B11` and then
2. Click on the `Formulas` tab and in the “Defined Names” group, click on `Define Name`.
3. In the Name box, type `Total2`.
4. click the Refers To box and type the formula `=SUM(B\$1:B10)`.
5. Click OK.

Select any row in any column other than row 1 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 row number 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`.

## Generate Names From the Selection

Excel can generate named ranges based on your selection, using the top/bottom rows and/or left/right columns as the names. These named ranges can then be used to reference the data. To generate names from the selection follow these steps:

1. In cell `A1`, enter the heading `Name`.
2. In cell `B1`, enter the heading `Pay`.
3. In cell `C1`, enter the heading `Title`.
4. In cell `A2`, enter `Philip`.
5. In cell `A3`, enter `Reese`.
6. In cell `B2`, enter `10`.
7. In cell `B3`, enter `20`.
8. In cell `C2`, enter `Mr`.
9. In cell `C3`, enter `Dr`.
1. Now, select the range `A1:C3`.
2. Go to the `Formula` tab and click `Create from Selection`. It will open the “Create Names from Selection” dialog box.
3. Ensure that both the Top row and Left column checkboxes are checked.
4. Click `OK`.

Select any cell outside your table and enter `=Reese Title`. You should get the correct title (in this case Dr. ) for the name Reese. See the following figure:

The space between the words `Reese` and `Title` is important, as this is what Excel understands as the intersect operator.

Note: The names of named ranges cannot contain a space. When you create names from a selection, Excel automatically places an underscore in the spaces between two or more words. To refer name that contains spaces, you should replace the spaces with underscores. For example, to refer Carl Nicks, you should use Carl_Nicks.