Excel Named Range: How to Create One

How-to basics · Updated November 18, 2026

To create a named range in Excel, select the cells, type a clear name in the Name Box to the left of the formula bar, and press Enter. A named range gives a cell or group of cells an easy-to-remember label, so you can select it quickly and refer to it in formulas instead of typing cell addresses.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. Excel calls these labels defined names; a name that refers to cells is commonly called a named range. You can also create a name through the Formulas tab when you need to set its scope or review its reference.

What You’ll Need

  • An Excel workbook with the cells you want to name. The range can contain numbers, text, dates, or formulas.
  • A short name that describes the cells, such as TaxRate, ProductList, or Q1Sales. Avoid spaces and names that look like cell addresses.
  • Access to Excel for Microsoft 365. The Name Box method is also available in Excel for Mac and Excel for the web, although ribbon layouts can differ.

Step-by-Step

  1. Choose the cells to name. Open the workbook and go to the worksheet containing the data. Select the exact cell or range you want to refer to. For example, select B2:B4 if those three cells contain quarterly sales figures. You can select by dragging across the cells, or click the first cell, hold Shift, and click the last cell. Check the highlighted selection before naming it; an extra header or blank cell may change what later formulas include. You can name a single cell as well as a multi-cell range.
  2. Enter a name in the Name Box. Find the Name Box just left of the formula bar, above the worksheet grid. It normally displays the active cell address, such as B2. Click inside the box, replace the address with a name such as QuarterSales, and press Enter. Do not leave the insertion point in the box without pressing Enter: until you confirm the entry, Excel has not created the name. If Excel accepts it, the selected cells remain highlighted and the name is available for use.
  3. Follow Excel’s naming rules. A name must begin with a letter, an underscore, or a backslash. After the first character, it can include letters, numbers, periods, and underscores. Names cannot contain spaces, and they cannot be written like a cell reference, such as A1 or R3C2. For a multiword label, use a form such as Annual_Total or AnnualTotal. Names are not case-sensitive, so Sales and SALES refer to the same name. Keep names descriptive but reasonably short so they are easy to read in formulas and the Name Box.
  4. Use Define Name if you need a particular scope. Select the cells, then go to Formulas > Define Name. In the New Name dialog, enter the name in the Name field, check that Refers to points to the intended cells, and choose Workbook or a specific worksheet in the Scope list. Click OK. Workbook scope makes the name available throughout the workbook. Worksheet scope limits it to that sheet and can allow the same name to exist on another sheet. Choose the scope deliberately: Excel does not let you change a name’s scope later in Name Manager. If you used the Name Box, Excel creates a workbook-scoped name; use Define Name when you need a sheet-scoped one.
  5. Check the range in the dialog before saving. In Define Name, the Refers to field shows the cells or expression associated with the name. For the example above, it should point to the selected B2:B4 cells on the correct worksheet. If the dialog opened with an unexpected reference, select the correct cells again or edit the reference before clicking OK. A name can refer to a range on a different worksheet, but confirm the sheet name as well as the cell addresses. This check helps prevent a name that looks correct but points to the wrong data.
  6. Use the named range in a formula. Suppose B2:B4 contain 120, 180, and 200, and the range has the name QuarterSales. With those values in B2:B4, enter this formula in another cell: =SUM(QuarterSales). It returns 500 because 120 + 180 + 200 = 500. Excel may suggest the name as you type; you can select the suggestion or finish typing it. You can also use the Name Box drop-down arrow to choose a name and jump to its cells. In many formula entry situations, pressing F3 on Windows opens the Paste Name list so you can insert a defined name; if that shortcut is unavailable on your keyboard, use the Name Box or type the name directly.
  7. Create names from column or row labels when useful. If a table has clear labels beside the data, select the labels and the associated cells, then choose Formulas > Create from Selection. In the dialog, tick the location of the labels, such as Top row or Left column, and click OK. Excel creates names based on those labels and refers them to the related cells. Review the results in Name Manager, especially if labels contain spaces or punctuation; Excel adjusts labels to make valid names, so the final name may differ from the heading as written. This option is handy for several related ranges, but the Name Box is usually simpler for one range.
  8. Review, edit, or remove a name. Go to Formulas > Name Manager, or press Ctrl+F3 on Windows. Select a name to inspect its scope and Refers to address. Choose Edit to change its name or reference, then confirm the change. Choose Delete only when you are sure formulas no longer need it; formulas that depend on a deleted name can return #NAME?. If the name points to the wrong cells, correct the Refers to field rather than creating a second name with a similar label. On Mac, use Formulas > Name Manager; keyboard shortcuts can vary by keyboard and Excel settings.

Common Mistakes

Typing a space or invalid character. Excel rejects names with spaces and some punctuation. Use underscores or combine words instead. A message that says the name is not valid usually means the text breaks one of the naming rules or resembles a cell reference.

Forgetting to press Enter in the Name Box. Typing a label and clicking somewhere else does not reliably confirm a new name. Press Enter while the Name Box is active, then check the Name Box list or Name Manager to verify it exists.

Selecting the wrong cells. If you include a heading or omit the last data row, the name will faithfully point to the wrong selection. Before creating it, check the highlighted range and then verify the Refers to address in Name Manager.

Assuming every name is workbook-wide. The Name Box creates a workbook-scoped name, while Define Name lets you choose worksheet scope. A sheet-scoped name may behave differently from a workbook-scoped name with a similar label. Check the Scope column in Name Manager when a name is missing or formulas resolve unexpectedly.

Renaming a heading and expecting the name to update. A name created from a label is not necessarily kept in sync with later heading edits. Review Name Manager after changing headings, and update the name or reference there if needed.

Tips

Use names that explain what the cells represent, not merely where they are. For example, CommissionRate is easier to understand in a formula than F7. Agree on a consistent style, such as PascalCase or underscores, if several people maintain the workbook. Avoid names that differ only by capitalization because Excel treats capitalization as equivalent.

Keep related data in a continuous range where practical. A name referring to one tidy column is easier to review and maintain than a selection assembled from scattered cells. If you add rows later, check whether the named reference includes them. A standard range does not always expand as you expect when data is added elsewhere; converting a well-structured dataset to an Excel table may be more suitable when you need expanding columns, though tables and named ranges are separate features.

Use the Name Box as a navigation tool after creating names. Open its drop-down and select a name to jump to the referenced cells, which can be quicker than scrolling through a large sheet. For a workbook with many names, Name Manager is the better place to check scope, references, and names that are no longer used.

Be cautious with relative references when defining a name manually. A name that refers to an address without fixed row or column markers can behave relative to the cell using it. For a named range intended to point to the same cells wherever it is used, confirm that its Refers to reference is fixed, as Excel normally creates fixed references when you name a selected range. If you edit the reference by hand, verify the behavior in a formula before relying on it.

Troubleshooting

Excel says the name is invalid. Remove spaces and unsupported punctuation, start with a letter or underscore, and make sure the text is not an address such as D5. Try a simple name such as MonthlyTotal. If the dialog still rejects it, check whether a conflicting name already exists in the same scope using Name Manager.

The name does not appear or cannot be selected. Press Enter after typing in the Name Box, then open its drop-down again. If you used Define Name, confirm the dialog was accepted and inspect Name Manager. Also check the worksheet scope: a sheet-scoped name is not generally available in the same way as a workbook-scoped name on other sheets.

A formula returns #NAME?. Check the spelling of the name in the formula and in Name Manager. Confirm that the name still exists and that it has not been deleted or misspelled. If the name is sheet-scoped, make sure the formula is being entered on the appropriate sheet. A name in a formula is not text, so do not put quotation marks around it.

The formula uses the wrong values. Select the name from the Name Box or inspect its Refers to field in Name Manager. If it points to the wrong worksheet or excludes cells, edit the reference and test the formula again. If the reference displays #REF!, the original cells or sheet may have been deleted; edit the reference to a valid range.

You cannot change the scope. Scope is fixed when a name is created. Create a new name with the desired scope through Formulas > Define Name, update formulas that use the old name, and then delete the old name only after checking its dependencies.

Frequently Asked Questions

What is a named range in Excel?

It is a descriptive name assigned to a cell or range of cells. You can use it to navigate to those cells or refer to them in formulas, which can make a workbook easier to read than one that relies only on cell addresses.

Can I name a single cell?

Yes. Select the cell and create the name using the Name Box or Formulas > Define Name. A single-cell name can be used in formulas just like a name that refers to multiple cells.

Can a named range include cells on more than one worksheet?

A normal named range refers to a particular reference, which can be on a worksheet other than the one where the name was created. For separate, non-adjacent references, define or edit the name through Name Manager and check the reference carefully. A name does not automatically mean every cell with a matching label on multiple sheets.

How do I see all the named ranges in a workbook?

Choose Formulas > Name Manager. The list shows names, their values or references, and their scope. You can select entries to edit or delete them, but check formulas that may rely on a name before removing it.

Can I rename a named range?

Yes. Open Formulas > Name Manager, select the name, and choose Edit. Change the name and confirm. Excel updates formulas that refer to that defined name in the workbook, but review important formulas afterward to make sure they still work as intended.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.