Defined Names and Spreadsheet Variables in Excel

Create a Basic Defined Name

In Excel, there is a way to create defined names that you can use as often as needed. You can think of a defined name as a spreadsheet variable or stored formula. Now, a defined name can contain something as basic as “hello” or an advanced formula. This very short tip shows you how to create a basic defined name in Microsoft Excel.

For the purpose of this tutorial, we used Microsoft Excel 2016 for Windows. However, these instructions should work on any version of Excel, 2007 or higher.

The Steps for Creating Defined Names

Formulas toolbar in Microsoft Excel.
Formulas toolbar in Microsoft Excel.
New Name dialog box in Microsoft Excel.
New Name dialog box in Microsoft Excel.

Follow the steps below to create a basic defined name in Microsoft Excel.

  1. Access Formulas ribbon.

    Select Formulas from the main menu.

  2. Show define name dialog box.

    Under the formulas ribbon, click Define Name. The New Name dialog box appears.

  3. Create a name.

    Under the New Name dialog box, give your variable a Name. In our case, we entered myName.

  4. Type in a value for the name.

    In the Refers to field, type =”[Name]”, where [Name] is your own name. As you see, we entered =”Excel Effects”.

  5. Press OK.

    Press OK. You name is now created. Let us test it.


Test It

Blank spreadsheet with defined name entered in empty cell.
Blank spreadsheet with defined name entered in empty cell.

In the same workbook, find an empty cell, and type =myName. Otherwise, type the name you created. Press enter after typing your defined name.

Contents of the defined name.
Contents of the defined name.

Success!

If your name shows up, then you just successfully created a defined name in Microsoft Excel.


Shortcut

To see a list of defined names, press Ctrl-F3. This opens the Name Manager.

Under Name Manager, you can create, edit, and delete defined names.
Under Name Manager, you can create, edit, and delete defined names.

For more information on the name manager and defining names, see use the name manager in Excel.


Notes

Keep in mind, a defined name is a stored formula. This means, you can assign cell references, store custom formulas, and even assign control arrays.


More Small Shots


Learn Excel

Learn PowerPoint

Learn SQL and Databases

Save 16% on Office 365 Yearly Subscription