Enter a Constant Value in a Name

Once upon a time I was working with an unfamiliar Excel spreadsheet and came across a formula that used a Name, which I figured for a constant value and thought it hidden somewhere in the worksheet, yet couldn’t find it anywhere.

It never dawned on me that a value doesn’t have to reside in a worksheet cell.

Enter a Constant Value in a Name — Excel 2007, 2010

You can put a constant value into a Name, which Excel will hold internally.

Go to Formulas, and select Define Name (Excel 2010, 2007)

Named Constant Menu Selection

In the New Name dialog box:

  1. Enter a Name into the Name field (no spaces, please)
  2. Select a scope: Workbook or specific worksheet
  3. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  4. Click OK

New Name Dialog Box with Constant Value

You can use the Comment box (shown above) to provide a comment, which will display (below) when you select the Name in a formula.

Comment for Named Constant

Enter a Constant Value in a Name — Excel 2003, 2008

The menu path is: Insert, Name, Define…

Named Constant Menu Selection 2003

Excel 2003

In the Define Name dialog box:

  1. Enter a Name into: Names in workbook
  2. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  3. Click Add
Add Constant Value with Name Box Excel 2008

Excel 2008

You can now use the Name in a formula and possibly freak somebody out.

Check to See if a Name Has a Constant Value

If you’re stumped as to where a constant value may be hidden, in Excel 2007 or 2010 check the Name Manager. Select Formulas, Name Manager or use the keyboard shortcut Ctrl+F3.

If there’s a value in the Refers To column, then you won’t find it in a worksheet cell.

Name Manager

Excel 2003 and 2008 don’t have a Name Manager. In Excel 2003 using the keyboard shortcut Ctrl+F3 brings up the Define Name dialog box. Similarly, in Excel 2008 Cmd+fn+F3 brings up the Define Name dialog box.

For these and earlier versions of Excel, you can get a freeware Name Manager here.

How to Make It Very Hard to Find a Name with a Constant Value

  1. Create a Name with a constant value
  2. Use VBA to change the Visible property of that Name to FALSE

Watch the confusion.

Related Posts Plugin for WordPress, Blogger...

1 thought on “Enter a Constant Value in a Name

Comments are closed.