How to create a named constant

In this video we'll look at how to create a named constant. This is a useful way to create a name that doesn't appear in a cell on the worksheet.

Let's take a look.

Here we have a list of materials to build a dog house which includes quantity, unit cost, and a total.

Let's add a formula that calculates sales tax.

One way to do this is to name a range on the worksheet, and use that name in the formula. For example, I can name cell I4 "tax_rate," and then add formulas in column F that multiply the total in column E by the tax rate using that name.

This works fine. The formula is easy to read and the tax rate is easy to change. But what if I don't want the tax rate to appear anywhere on the worksheet?

Well, I could move it to another sheet, and even hide that sheet if I want. But it's also possible to name a constant directly with Excel's Name feature.

First, I'll undo the formulas and name that I created.

Next, I'll create a new name called "tax_rate". This time, however, instead of pointing to a reference on the worksheet, I'll just enter a simple formula that sets the name equal to the tax rate.

For the tax rate, I can use a decimal value, or I can use a percent value. You may want to add a comment to explain the purpose of this name.

Back in the worksheet, I can create the formulas as before using this new name. With this approach, I don't need the tax rate to appear anywhere on the worksheet.

However, if I do want to display the tax rate, I can always just refer to the name using a simple formula.

Note that if I want to change the tax rate, I need to edit the name and provide a new value.

This is probably not a technique you'll use everyday. But it's a useful way to add named information to a worksheet that doesn't need to appear in a cell.