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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
