Excel Formulas – How to Store Constants in a Central Location with Excel Names

Constants can change, this is the reason why you need to manage fixed quantities efficiently in one place. Remember that Excel Formulas with constants are used all through sheets so updating hard coded quantities manually means a waste of time and a weak trust in your own model.

Here are some examples:

  • An incentive for employees has been 5.1% for years. You assume this is a constant but new politics could change it to 5.5% for example
  • Newton Gravity with two decimals 6.67 m3/kg/s2 could be later changed to a greater precision: 6.67428 m3/kg/s2
  • A change in the constant is reflected throughout the model during a sensitivity analysis

Hopefully, you can store constants in a central location with Named Ranges in Excel. Here’s how…

1) Open the New Name Dialog (Excel 2007)

Go to Formula>Defined names>Define name.

Graphic 1

New Name Dialog in Excel 2007

For Excel 2003 users, proceed this way…Go to Insert>Name>Define

Become aware of…

“Scope” and “Comment” options are not available in Excel 2003 (see graphic 1)

2) Create the Constant: Define Name, Scope and Description

  • Specify the Name (without spaces)
  • Set the Scope: Workbook or on a Sheet basis
  • Specify the value of the constant with “Refers to:”

This is easily done in 3 steps

You can add comments to your constant and get oriented during the inserting process. The new Formula Autocomplete is a big deal. See graphic 2…

3) Insert the Constant in Your Excel Formulas

Use new Formula Autocomplete tool to do the job very easily and intuitively. You can use F3 too.

Graphic 2

Formula Autocomplete, Insert Constants