How to Use Named Range in Excel: Simple Guide
In Excel, a
named range is a label you assign to a cell or group of cells to make formulas easier to read and use. You create a named range by selecting cells and typing a name in the Name Box or using the Define Name feature. Then, use the name in formulas instead of cell references.Syntax
To use a named range in a formula, simply type the name instead of the cell address. For example, if you named cells A1:A5 as Sales, you can sum them with =SUM(Sales).
Parts explained:
Sales: The name you assigned to a range of cells.=SUM(): Excel function to add numbers.
excel
=SUM(Sales)
Output
Sum of all values in the named range Sales
Example
This example shows how to create a named range and use it in a formula to calculate the total sales.
excel
1. Select cells A1 to A5 with sales numbers.<br>2. Click the Name Box (left of the formula bar), type <code>Sales</code>, and press Enter.<br>3. In another cell, enter the formula <code>=SUM(Sales)</code>.<br>4. Press Enter to see the total of the sales values.
Output
Displays the sum of values in cells A1 to A5
Common Pitfalls
Common mistakes when using named ranges include:
- Using spaces or special characters in names (Excel does not allow this).
- Forgetting to update named ranges if you add more data outside the original range.
- Using names that conflict with Excel functions or cell references.
Always use simple names without spaces, like SalesData, and update ranges via the Name Manager if needed.
excel
Wrong: Naming a range as "Total Sales" (with space) causes errors.<br>Right: Use <code>TotalSales</code> without spaces.
Quick Reference
| Action | How to Do It |
|---|---|
| Create Named Range | Select cells → Type name in Name Box → Press Enter |
| Use Named Range in Formula | Type the name directly in formula, e.g., =SUM(Name) |
| Edit Named Range | Go to Formulas tab → Name Manager → Select name → Edit range |
| Delete Named Range | Go to Formulas tab → Name Manager → Select name → Delete |
Key Takeaways
Named ranges make formulas easier to read and manage by replacing cell references with meaningful names.
Create named ranges by selecting cells and typing a name in the Name Box or using Define Name.
Use named ranges directly in formulas like =SUM(Name) to simplify calculations.
Avoid spaces and special characters in names to prevent errors.
Manage named ranges anytime via the Name Manager under the Formulas tab.