Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.

You can store text, numbers, formulas, and functions inside of a Defined Name all without referencing any cell in the worksheet or workbook.

This allows you to hard-code values into a Name that you can then use within your formulas and functions throughout Excel.

This is very easy to do - lets say that we want to store an interest rate in a Defined Name in Excel so we can use it in a calculation.

First, go to the Formulas tab > Define Name

Once you click that, a window will open that allows you to enter all of the required data for your new Name:

The name is what you will use to reference this Name and it should be simple and representative of the data that it will store.  You can only use text, numbers, and underscores for a name.

The scope allows you to limit a Names usability to a specific worksheet or the entire workbook but, usually, its best to keep this option as Workbook.

Comment is a section where you can write something that will explain what this Name should be used for or what data it is storing or referencing.  This comment will appear when you go to enter the name in the worksheet, as I will show you below.

The Refers to: section is the most important as this is where the data, text, numbers, functions, formulas, or cell references will be entered.  I will talk more about this section below as it can be a bit tricky.

Here is what this box looks like when I fill-in the interest rate information that I want to use:

Once you hit OK, you have then created a name that can be used in any formula or function in Excel.

Storing Different Types of Data in a Defined Name in Excel

Now, lets talk about the Refers to: section.

By default, this will have a cell reference of whichever cell is selected in the worksheet, like this:

And that is how you should reference a cell, using Absolute Cell References (note the dollar signs), for a Defined Name.

Now, if you want to input text in the Defined Name, you need to surround it with quotation marks in the Refers to section like this:

If you store numbers you do not need quotation marks, as shown in one of the examples above where I input an interest rate, which is just a decimal number.

If you want to store a formula or function, you input it in the Refers to section just like you would in a cell in Excel:

 That is all there is to storing constant values and hard-coded values, numbers, text, etc. in a Defined name in Excel.

Here is an example of using the interest_rate name in a function:

Notice in the FV function the first argument is interest_rate which is what we named the interest rate when we created that Defined Name.

Also note that when you go to enter a Defined Name, it will appear in the drop-down menu that shows in Excel along with the comment that you entered when you created the Name:

The comment text only appears when you select the name from the drop down so, if you have many potential choices, the comments for each one dont appear until individually highlighted.

As you can see, Defined Names are quite versatile and co do a lot more than just reference a boring old cell in Excel.  These feature is very useful when creating large and complex spreadsheets.

Note: If you click the Name Manager button on the formulas tab, you will be able to see all of the names in that are in the spreadsheet and you can then edit, delete, or add them from there.

I hope this was a helpful tutorial!

Make sure to download the accompanying Excel spreadsheet so that you can follow along and better understand how to use Names in Excel.