Named Range

 

Create a named range or a named constant and use these names in your Excel formulas. This way you can make your formulas easier to understand.

Create a Named Range

To create a named range in Excel, execute the following steps.

1. For example, select the range A1:A4.

Select Range

2. On the Formulas tab, in the Defined Names group, click Define Name.

Define Name

3. Enter a name and click OK.

New Named Range

There's an even quicker way of doing this.

4. Select the range, type the name in the Name box and press Enter.

Name Box

5. Now you can use this named range in your formulas. For example, sum Prices.

Named Range in Excel

Named Constant

To create a named constant, execute the following steps.

1. On the Formulas tab, in the Defined Names group, click Define Name.

Define Name

2. Enter a name, type a value, and click OK.

New Named Constant

3. Now you can use this named constant in your formulas.

Named Constant in Excel

Name Manager

Use the Name Manager in Excel to view, edit and delete named ranges and named constants.

1. On the Formulas tab, in the Defined Names group, click Name Manager.

Click Name Manager

2. For example, select TaxRate and click Edit.

Name Manager

3. Change the tax rate from 0.1 to 0.2.

4. Click OK.

Edit Name

Result: Excel automatically updates all the formulas that use TaxRate.

Updated Formulas

Create from Selection

If your data has labels, you can quickly create named ranges in Excel.

1. For example, select the range A1:D13.

Selection

2. On the Formulas tab, in the Defined Names group, click Create from Selection.

Create Named Ranges

3. Check Top row and Left column and click OK.

Create Names from Selection

4. Excel created 12 + 3 = 15 named ranges! Simply select a range and look at the Name box.

Name

5. Use the intersect operator (space) to return the intersection of two named ranges.

Intersection of Named Ranges

Note: try it yourself. Download the Excel file, create the named ranges quickly and easily (Sheet2) and lookup any value in this two-dimensional range.