How to Create and Use a Named Constant

How to Create and Use a Named Constant - Twitter

In this blog post, we’re going to take a look what an Excel named constant is, how to create them, and how to use a named constant in a couple of examples.

What is a Named Constant?

A named constant is similar to a named range except the value of the name doesn’t appear in any cell. If you’re not familiar with what an Excel name is, then I suggest reading my previous blog post on the Excel name feature first by clicking here.

Named values are useful because they can be referred to across a workbook using a friendly, meaningful name. One of the most useful features is the ability to update the name once and for this change to be reflected where the name is used in the workbook.

Named constants are a useful way of creating a name without the need for it to appear in your worksheet. Why might this be useful? Well, you might not want the value to appear in the spreadsheet for design reasons, to reduce complexity or confusion for the user, or simply to remove the value from prying eyes; but, don’t confuse this with a security feature.

How to Create a Named Constant?

To create a named constant, you use the Define Name dialogue window.

For this first example, we’re going to create a named constant for a fairly universal tax, sales tax, sometimes referred to as VAT or GST. Once we’ve created the sales tax named constant, we’ll be able to use it anywhere in our worksheet.

In the image below you can see a simple worksheet with a list of fruit in column A and the Pre-Tax price in column B.

Spreadsheet Pre-tax Price

For this worksheet, we’re going to use the SalesTax named constant to calculate the sales tax for each type of fruit and the price including sales tax. Start by clicking on the Formula tab on the ribbon, then clicking the Define Name button. When you do this, the Define Name (Mac), or New Name (Windows), dialogue window should appear like the images below for both Windows and Mac.

Open New Name Dialogue - Windows

Open New Name Dialogue – Windows

Open Define Name Dialogue - Mac

Open Define Name Dialogue – Mac

On Windows, click in the “Name:” textbox; on the Mac, click the plus sign “+” and then click in the “Enter a name for data range” textbox. Remembering that you can’t use spaces when creating names, type SalesTax in the textbox as shown in the image below.

Once this is done, click in the “Refers to:” textbox on Windows, or “Select range of cells:” textbox on the Mac and type 10%.

New Name Dialogue - Windows

New Name Dialogue – Windows

Define Name Dialogue - Mac

Define Name Dialogue – Mac

If you’re using Excel on Windows, you might want to type some words in the “Comment:” textbox to describe what the named constant is used for.

Comment Textbox - Windows

Comment Textbox – Windows

Once this is done, click the OK button, and you’re done. You’ve created a named constant.

Now to use the SalesTax named constant in a worksheet. On our original worksheet, we are going to type the following formula in column C to calculate the sales tax on each piece of fruit.

Sales Tax Formula

When the formula is typed into cell C2, the worksheet should look like the image below.

SalesTax named constant in worksheet

Now we’re going to add another formula to column D to calculate the total price (pre-tax price plus sales tax) for each item of fruit. To do this, we enter the following formula in cell D2.

Sales Tax and Price Formula Formula

This formula simply adds together the pre-tax sales price and the sales tax for the piece of fruit to get the total sales price. Once this formula has been entered, the worksheet will look like the image below.

Total Price Formula in Worksheet

When this is done, we simply copy the formulas in cells C2 and D2 to the remaining rows.

Flash Fill Formula

 

Updating and Editing a Named Constant

Ok, we’ve seen how to create a named constant, but what do we do if we want to update the value?

Updating a Named Constant on Windows

On Windows, click the Formula tab on the ribbon, then click the Name Manager button. Highlight the name you want to change by clicking on it as shown in the image below.

Edit Name Dialogue - Windows

 

When you do this, the Edit Name dialogue window should appear (it looks identical to the New Name dialogue window) as in the image below.

Edit Name 15 Percent

Click in the “Refers to:” textbox and edit the value to 15%, then click OK on the Edit Name dialogue window and close the Name Manager menu.

Once all this is done, the formulas in the worksheet should now be using the new SalesTax named constant value of 15%, and the calculations should have updated to reflect this as in the image below.

Total Price Formula in worksheet 15 Percent

Updating a Named Constant on a Mac

On a Mac, click the Formula tab on the ribbon, then click the Define Name button. Highlight the name you want to change by clicking on it as shown in the image below.

Define Name 15 Percent

 

Click in the “Select range of cells:” textbox and edit the value to 15%, then click OK on the Define Name dialogue window.

Define Name 15 Percent Update

Once all this is done, the formulas in the worksheet should now be using the new Sales Tax value of 15%, and the calculates should have updated to reflect this similar to the image below.

Total Price Formula in worksheet 15 Percent - Mac

Wrap-up

So, that’s how to create and use named constants. They’re fairly easy to create and use; they can make your workbooks look cleaner and less cluttered. Like the named ranges, they can help with spreadsheet maintenance and quality.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit