What is a Defined Name?

 In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.

To create a Defined Name,

 you have multiple ways:

• you can simply right click the cell or the range and select Define Name from the list:

This action will bring up the New Name Dialog Box:All you have to do now is to type a relevant name to your chosen range into the “Name:” field and click the OK button.

• Or, you can choose Define Name from the Ribbon, from Formulas tab, the Defined Names group:

This action will open the same New Name Dialog Box you’ve seen above: Add New Name

• Another way to create a Defined Name is to select the range, then simply type into the Name Box the name of your selected range. The Name Box is located to the left of the Formula Bar:

Note: This method will create a name scoped to the workbook level! (also known as global name) 

The Scope of a Defined Name:

 All Defined Names have a scope, the scope can be:

• a specific worksheet (a local name, defined at worksheet level)
• the entire workbook (a workbook name, defined at workbook level).

If you create a Defined Name at worksheet level, you will not be able to use it in other worksheet, unless you provide a qualified reference (the worksheet name needs to be typed before the Defined Name):=SUMPRODUCT(‘Sales’!Units_Sold*Price_Per_unit) 

You Can Edit The Defined Names anytime!

 All you have to do is to open the Name Manager, which is located in the same Formulas Tab, Defined Names group:Select the name from the list and click the Edit button, or simply double click the name. 

Syntax Rules for Defined Names:

 The following list of rules is not absolutely necessary to know, but it may be useful to avoid errors when creating new names:

• Always make sure that the first character of a name is a letter, an underscore character (_), or a backslash (\). Never start with a number.
• Avoid names that have the same structure as Cell references: AB2355 is not a valid name, for example. Keep in mind that the names are case insensitive, Excel will not make a difference between TotalSales and totalsales
• Do not use Spaces, always use an underscore character  (_) or a period character (.) as a word delimiter! Or, just start the name with uper case letters, like: TotalSales
• A name can contain up to 255 characters. For me, is far more than necessary, I prefer short and meaningful names 🙂

 

Tip:

 If your formulas are using cell references instead of Defined Names references, you can replace the cell references with the corresponding Defined Names very easy:From Formulas Tab, Defined Names group, expand the Dropdown from Define Name button, and click on Apply Names. If the formula contains a range reference which has a Defined Name, the range from the formula will be replaced with the name.For example, if the Defined Name: Units_Sold refers to range D1:D10, and you have a formula in your worksheet referring to the same range of cells:=SUMPRODUCT($D$1:$D$10*Price_Per_unit), clicking on Apply Names will change the formula to:=SUMPRODUCT(Units_Sold*Price_Per_unit)