Enter a Constant Value in a Name

by Gregory on September 28, 2010

Post image for 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...
Lindsay Harris January 8, 2013 at 11:46 pm

Very useful, and so simple!

Comments on this entry are closed.

Previous post:

Next post: