Named ranges replace hard-to-read references like B2:B50 with meaningful names such as MonthlySales. Formulas become easier to understand and maintain.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when formulas would be clearer with names instead of raw cell references.
Create a named range with the Name Box
This is the fastest method.
- Select the cells to name.
- Click the Name Box to the left of the formula bar.
- Type a name with no spaces, such as MonthlySales or Monthly_Sales.
- Press Enter.
- Use the Name Box dropdown later to jump back to that range.
Create a named range with Define Name
Use this when you need more control.
- Select the range, or prepare to type it manually.
- Open the Formulas tab.
- Click Define Name.
- Enter the name.
- Choose Scope: Workbook or a specific sheet.
- Confirm the Refers to box points to the correct cells.
- Click OK.
Use and manage named ranges
Type the name wherever you would normally use a cell reference, such as =SUM(MonthlySales), =AVERAGE(TestScores), or =Revenue-Expenses. Open Formulas > Name Manager to edit names, adjust references, delete old names, or find broken names.
More control
Choose scope intentionally
Workbook scope lets the name work on any sheet. Sheet scope allows the same name to mean different ranges on different sheets, but it can confuse formulas if used casually.
Plain named ranges are fixed
A normal named range stays attached to the selected cells. If you add rows below it, they may not be included. Convert the data to an Excel Table if the range needs to grow automatically.
Fix #NAME and #REF
#NAME means Excel does not recognize the name, usually from a typo or deleted name. #REF means the name still exists but points to cells that were deleted. Check Name Manager for the exact problem.
Sources
- Microsoft Support – Define and use names in formulas (2025)
- Microsoft Support – Create a defined name (2025)
- Microsoft Support – Use the Name Manager (2024)