How To Define Named Constant In Excel And Create Excel Named Range

In this article, I will tell you how to define a named constant and a named range in excel and then reference them in excel formulas. In this way, you can make your excel formula more readable, and easier to maintain.

1. How To Define Named Constant To Reference Excel Cell.

1.1 How To Define Excel Named Constant.

1. Open the excel worksheet, and click the Formulas tab.
2. Then click the Define Name item in the Defined Names group.
3. It will pop up a New Name dialog window.
4. Input the constant name ( for example Discount ) in the Name text box.
5. Select the constant scope from the Scope drop-down list, then the named constant can be used either in the whole workbook or selected worksheet.
6. Input the number value 0.8 in the text box Refers to, then the named constant’s value will be 0.8 when you use it later.
7. You can also input a formula such as =sheet1!$A$1 in the Refers to text box so that the constant’s value can be changed when the cell A1‘s value is changed in the worksheet sheet1.

1.2 How To Use The Defined Excel Named Constant In Formula.

1. After you defined the named constant, you can use it in your excel formula like below.
2. You can reference the named constant in your formula directly like =Discount*100, or =Discount*A1.
3. You can also click the Formulas tab —> Defined Names group —> Use in Formula item to select the existing names to use when you input a formula.

2. How To Create Excel Named Range.

2.1 How To Create Excel Named Range Steps.

1. Suppose there are the below data in the cells of column A.
   
2. If we want to give the above cell region A2:A6 a name, then we can follow the below steps.
3. Select the cell region A2:A6.
4. Click the Formulas tab —>Define Name item in the Defined Names group.
5. Input the name value ( such as Scores) in the Name text box.
6. Select the named range scope from the Scope drop-down list.
7. Then the named range object can be used in the whole Workbook wide or in a single worksheet that you select from the Scope list.
8. Click the OK button to create the named range object.

2.2 How To Use The Named Range Object In Excel.

1. Now you can use the above range name to reference the cell range in the excel formula.
2. For example, you can use the formula =SUM(Scores) to calculate the summary number value of the cell’s value in the range.
3. You can also use the formula =AVERAGE(Scores) to calculate the average number value of the cell’s value in the range.

2.3 How To Create Named Range Based On Row Or Column Labels.

1. If the excel data contains labels on the top row, bottom row, left column, or right column, then you can quickly create the named range object based on those labels.
2. Below is an example of excel cell region data.
   
3. In the above data cells, the row labels are Workday and Weekend, and the column labels are WorkHour and SleepHour.
4. Select all the above cells including the labels row and column.
5. Click the Formulas tab —> Defined Names group —> Create from Selection item.
6. Select the checkbox Top row and Left column in the pop-up Create Names from Selection dialog window.
7. Because the data label are located on the data cells’ top row and left column, so we select the above 2 checkboxes.
8. Click the OK button, then it will create 4 named range objects with the name of the labels.

3. How To Manage Existing Named Object In Excel.

1. Click the Formulas tab —> Name Manager item in the Defined Names group, then it will open the Name Manager dialog window.
2. It will list all the existing names in the dialog, you can see the name objects that use the row or column labels as the name.
3. you can click the New button to create a new name.
4. Click the Edit button to edit one existing name.
5. Click the Delete button to delete an existing name.