To name a range in Excel for Microsoft 365 on Windows, select the cells, click the Name Box to the left of the formula bar, type a valid name, and press Enter. You can also use Formulas > Define Name to give the range a name and choose whether it is available throughout the workbook or only on one worksheet. A named range makes important cells easier to identify, select, and use in formulas.
What You’ll Need
- An Excel workbook with the cells you want to name. Save the workbook if you want to keep the name for future sessions.
- A clear name that follows Excel’s naming rules. For example, use
MonthlySalesorTax_Rate, rather than a name with spaces. - Microsoft Excel for Microsoft 365 on Windows for the steps below. The Name Box method is also available in Excel for Mac; ribbon labels and dialog layouts can vary slightly by platform. Excel for the web has a more limited set of name-management controls, so use the desktop app if you need to set or change a name’s scope.
Step-by-Step
- Choose the cells to name. Open the worksheet and select the exact range you want to refer to later. For example, if a table has a heading in A1 and twelve monthly figures in A2:A13, select A2:A13 if the name should refer only to the figures. Including a heading is appropriate only if you deliberately want the heading included in the named range.
- Check the selection before naming it. Look at the highlighted cells and confirm that the first and last rows and columns are right. A named range refers to the selected cells, not to a description of your intent. If you select A2:A12 by mistake, the last cell will not be included just because the name suggests a full year.
- Use the Name Box for the quickest method. Find the Name Box at the left end of the formula bar, above the worksheet grid. It normally displays the active cell address, such as A2. Click inside it, replace the address with a name such as
MonthlySales, and press Enter. Make sure you press Enter; simply typing into the box does not finish creating the name. - Confirm that Excel accepted the name. Click another cell, then open the Name Box drop-down arrow. The name should appear in the list. You can also click the Name Box, type the name, and press Enter; Excel should select the cells assigned to it. If the name is not listed or Excel returns an error, review the naming rules and the troubleshooting section below.
- Alternatively, open Define Name. Select the range, then choose Formulas > Define Name. In the New Name dialog, type the name in the Name field. Use a descriptive name that tells you what the cells contain, such as
ProductPricesorStartDate. This route is useful when you want to specify the name’s scope and check its cell reference as you create it. - Choose the scope. In the Define Name dialog, use the Scope list to choose Workbook or a worksheet. Workbook scope makes the name available from formulas and references across the workbook. Worksheet scope limits it to that particular sheet. If this is your first named range or it will be used on multiple sheets, Workbook is usually the simpler choice.
- Check the Refers to field. Confirm that the field points to the intended cells and worksheet. For example, a range on a sheet named Sales might appear as
=Sales!$A$2:$A$13. Excel may put apostrophes around a worksheet name when needed, especially if it contains spaces. Do not remove the sheet name or dollar signs unless you understand how changing the reference will affect the name. - Save the name. In the dialog, select OK. The name is now stored in the workbook. Save the workbook to preserve the change. You do not need to save after every small edit, but unsaved changes can be lost if the file closes unexpectedly.
- Use the name to go back to the range. Open the Name Box drop-down and choose the name, or type it into the box and press Enter. Excel jumps to the named cells and selects them. This is handy in a large worksheet where scrolling to the range would take time.
- Use the name in a formula when appropriate. A named range can replace a cell address in many formulas. For example, with the figures 120, 150, and 130 in B2:B4 and that exact range named
QuarterlySales, enter=SUM(QuarterlySales)in another cell. The result is 400. If the range contains different values, the result will reflect those values instead.
Common Mistakes
Using spaces in the name. Excel does not allow spaces in a defined name. Use an underscore or join the words, as in First_Quarter or FirstQuarter. This makes the name usable in formulas without adding special punctuation.
Choosing a name that looks like a cell address. A name cannot be an ordinary cell reference such as A1 or R1C1. Names must start with a letter, an underscore, or a backslash. After the first character, they can include letters, numbers, periods, and underscores, subject to Excel’s naming rules. Names are not case-sensitive, so Sales and SALES are treated as the same name.
Selecting the wrong cells. A name does not automatically include future rows just because the range is next to a table. If you name A2:A13 and later add a value in A14, the name may still refer only to A2:A13. If the data will grow, consider converting it to an Excel table and using structured references, or update the named range when the data changes.
Confusing worksheet scope with workbook scope. A worksheet-scoped name may not work in a formula on another sheet unless you qualify it with the worksheet name. If two sheets have names with the same spelling, Excel can resolve references differently depending on where a formula is entered. Choose the scope intentionally and avoid duplicate names when possible.
Expecting the name to change the displayed cells. Naming a range does not format, calculate, or alter its values. It creates a label that points to cells. The selected values stay as they were unless you separately edit or format them.
Tips
Use short, meaningful names that describe the contents or purpose of the range. CustomerIDs is easier to recognize than Range2, while Rate2026 is more informative than Rate if several rates exist. A name should be understandable months later, not just convenient at the moment you create it.
Use a consistent style across the workbook. For example, use descriptive words joined together, such as AnnualBudget, or separate words with underscores, such as Annual_Budget. Excel ignores capitalization when matching names, but consistent capitalization can make names easier for people to read.
For a quick audit, choose Formulas > Name Manager. Name Manager lists defined names and their scope and reference. Select a name to inspect it; depending on the action you need, you can edit its reference or delete it. Be careful when editing a name used by many formulas: changing its reference can change the results throughout the workbook.
Remember that a normal named range points to a fixed address unless its reference is changed or designed to expand. If a named range needs to cover a changing list, a formatted Excel table is often easier to maintain. Tables expand when you add rows and provide named columns that can be used in formulas.
On Excel for Mac, you can use the Name Box in the same basic way: select the cells, type the name into the Name Box, and press Return. The Define Name and Name Manager commands are on the Formulas tab, although their placement or dialog appearance can differ from Windows. Keyboard shortcuts can also vary with Mac keyboard settings, so the ribbon and Name Box are dependable alternatives.
Troubleshooting
Excel says the name is not valid. Check for spaces, a leading number, or a name that resembles a cell address. Start with a letter or underscore and replace spaces with underscores. Also check for punctuation that is not permitted. If you are using the Name Box, ensure you typed the name there rather than into the worksheet cell or formula bar.
The name does not appear in the drop-down list. Press Enter after typing it in the Name Box. If it still does not appear, open Formulas > Name Manager to see whether it was created. Check whether the name has worksheet scope and whether you are looking from the sheet where that local name applies. You can create it again with Workbook scope if you need it throughout the file.
The name selects the wrong cells. Open Name Manager, select the name, and inspect the Refers to field. Correct the sheet or cell addresses, then confirm the edit. You can also select the right cells and create a replacement name, but first check whether the intended name already exists. If it does, edit or delete the old entry rather than creating a confusing duplicate.
A formula using the name returns an unexpected result. Confirm that the name points to the correct cells and that the formula uses the intended name. A name may include a heading, a blank cell, or an extra row that changes the calculation. Also check whether a local worksheet name is taking precedence over a workbook-scoped name with the same spelling.
You cannot change the scope of an existing name. Excel does not let you switch a defined name from worksheet scope to workbook scope, or the reverse, by editing the scope field. Create a new name with the desired scope, update formulas that use the old name, and then remove the old entry if it is no longer needed. Check formulas carefully before deleting a name.
The name stops including new data. This is expected if it refers to a fixed cell range. Open Name Manager and expand the Refers to address to include the new cells, or convert the data to a table if rows will be added regularly. Verify the updated selection before relying on formulas that use it.
Frequently Asked Questions
Can a named range contain multiple cells?
Yes. Select a rectangular group of cells and assign it a name. The name then refers to the entire selected range, not just the active cell.
Can I use the same name more than once?
Names must be unique within the same scope. A workbook-scoped name and a worksheet-scoped name can share a spelling, but that can make formulas harder to understand. Using distinct names is usually clearer.
Can I rename a range after creating it?
Open Formulas > Name Manager, select the name, and choose Edit to change its definition. If the dialog does not allow the name itself to be changed, create a new name, update any formulas that use the old one, and delete the old entry when safe.
Will a named range update when I insert rows?
Excel adjusts many references when rows or columns are inserted within or around a referenced range, but a fixed range will not necessarily include data appended beyond its end. Check the Refers to field after structural changes, or use a table for a list that grows.
Can I name a range in Excel for the web?
Excel for the web supports working with defined names in many workbooks, but available name-management options can differ from the desktop app. If you cannot create, edit, or set the scope you need, open the workbook in Excel for Microsoft 365 on Windows or Mac and use the Name Box or Formulas tab.
Related
- How to Add the Same Header in Excel on Every Sheet
- Add a Header in Excel for the Web
- Add a Header in Excel for Microsoft 365: Page Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Themes: Styles, Colors, and How to Change ThemIn Excel, “table themes” usually means the built-in table styles that control a table’s colors, borders, and visual…
- How to Add a Border Under a Header in Microsoft WordTo add a line under a header in Word, open the header, click in the paragraph you want…
- Find and Replace Enter in Excel: Remove Line BreaksTo find and replace an Enter line break inside Excel cells, press Ctrl+H, click the Find what box,…
- VLOOKUP Did Not Find Value in Google Sheets?If VLOOKUP did not find a value in Google Sheets, the usual cause is that the lookup key…
- Excel Import Multiple CSV Files: Methods and TipsTo import multiple CSV files into Excel for Microsoft 365 on Windows, put the files in one folder,…
- How to Keep Duplicate Values in Excel Without Losing DataTo keep repeated entries in Excel, do not run Data > Remove Duplicates on the data you need…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.