Named Range in Excel
Assign a name to a range or constant, and then use this name in your Excel formulas. This method makes your formulas easier to read and understand.
💎 Create a Named Range
To define a named range in Excel, complete the following steps.
1. For example, select the range A1:A4.
2. Click the Define Name option from the Defined Names group inside the Formulas tab,
3. Enter a name and click OK.
There’s an even quicker way of doing this.
4. Select the cells you want to include in the range, write a name in the Name box, and press Enter.
5. You are now able to use this named range in your formulas. For example, sum Prices.
💎 Named Constant
To define a named constant, follow these steps.
1. Click the Define Name option from the Defined Names group inside the Formulas tab.
2. Provide a name, enter any value, and select OK.
3. You can now insert this named constant into your formulas.
💎 Name Manager
In Excel, the Name Manager allows you to manage named ranges and constants by viewing, editing, or deleting them.
1. Click Name Manager in the Defined Names group on the Formulas tab.
2. For example, select TaxRate and click edit.
3. Set the tax rate to 0.2 instead of 0.1.
4. Click OK.
Result: Excel updates all formulas that use TaxRate automatically.
💎 Create from Selection
When your data contains labels, Excel allows you to quickly create named ranges.
1. For example, select the range A1:D13.
2. Click Create from Selection in the Defined Names group on the Formulas tab.
3. Mark the checkboxes for the Top row and Left column, and press OK.
4. Excel shows a total of 15 named ranges (12 + 3). To check, highlight a range and look at the Name Box.
5. Use the intersect operator (a space) between two named ranges to display the cells they have in common.
Note: Try it yourself. Download the Excel file, set up the named ranges on Sheet2, and easily find any value in the two-dimensional range.
8/11 Completed! Learn more about formulas & functions ➝
Next Chapter: Ribbon