No Ticket Needed

Appearance

5 min fix 13 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Select the cells, click the Name Box to the left of the formula bar, type a name with no spaces such as Revenue, and press Enter. Now use it anywhere a range would go: =SUM(Revenue).

How to Use Named Ranges in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-07-29

Summary ​

A named range gives a cell or a block of cells a word instead of an address, so =SUM(Revenue) replaces =SUM(B2:B500). This article covers creating names two ways, the rules Excel enforces on them, naming a whole table of columns in one move, and repairing names in Name Manager when a formula comes back as #NAME?.

Prerequisites ​

  • Excel for Windows, Mac, or the web. Names work in all three, though Create from Selection and the full Name Manager are desktop features.
  • Edit permission on the workbook. Names cannot be created in a file opened read-only or in Protected View.
  • No admin rights required.

Instructions ​

Every example below uses the same workbook: a tab named Sales holding one row per order, with headers in row 1 and data down to row 500.

RowABCD
1RegionRepUnitsRevenue
2NorthAlvarez1412400
3SouthBoyd98150
4NorthChen2115900
500WestNowak64300

A second tab named Rates holds the commission rate in cell B1.

1. Name a Cell or Range with the Name Box ​

This is the fastest route and the one to use for a single cell holding a rate, a target, or a total.

  1. Open the Sales tab and select the cells you want to name — for the revenue figures, click D2 and drag down to D500, or click D2 and press Ctrl + Shift + Down arrow.
  2. Click inside the Name Box — the small white box immediately to the left of the formula bar, above column A. It currently shows the address of the active cell, D2.
  3. Type Revenue. The existing address disappears as you type.
  4. Press Enter. The selection stays put and the Name Box now reads Revenue.

Verification: Click any cell elsewhere on the sheet, then click the small arrow at the right edge of the Name Box. A dropdown lists every name in the workbook. Select Revenue and Excel jumps to the Sales tab and re-selects D2:D500.

Note: Pressing Enter is what commits the name. Typing into the Name Box and then clicking away with the mouse discards it, and the box reverts to the cell address. This is the single most common reason a name someone "definitely created" is missing.

2. Name a Range with Formulas > Define Name ​

The dialog route takes three more clicks and gives you two things the Name Box cannot: a scope setting and a comment.

  1. Select the cells you want to name.
  2. Click the Formulas tab on the ribbon.
  3. In the Defined Names group, click Define Name. The New Name dialog opens.
  4. Type the name into the Name box at the top.
  5. Leave Scope set to Workbook for now — Step 7 covers when to change it.
  6. Type a sentence into the Comment box explaining what the range holds and who maintains it. The comment appears as a tooltip in the formula autocomplete list.
  7. Check the Refers to box at the bottom. It shows the selection as an absolute reference, such as =Sales!$D$2:$D$500.
  8. Click OK. The dialog closes and the name is live everywhere in the workbook.

Tip: To name a single cell on the Rates tab, click B1, open Define Name, and type CommissionRate. A formula reading =D2*CommissionRate survives being read by a colleague next quarter. =D2*Rates!$B$1 does not.

3. Follow the Naming Rules Excel Enforces ​

Type an invalid name into the Name Box and Excel throws a dialog reading The name that you entered is not valid. It does not tell you which rule you broke. There are four in practice.

  • No spaces. Total Revenue is rejected. Use TotalRevenue or Total_Revenue. An underscore and a full stop are the only punctuation Excel accepts inside a name.
  • Cannot start with a number. 2026Budget is rejected; Budget2026 and _2026Budget are accepted. A name may start with a letter, an underscore, or a backslash.
  • Cannot look like a cell address. Q1 is a real cell on every worksheet, so Excel refuses to let a name shadow it. Use Q1Sales or Q1_Total. The same rule blocks the single letters R and C, which Excel reserves for its alternative row-and-column reference style.
  • Cannot duplicate an existing name at the same scope. Excel replaces the dialog with a prompt asking whether to redefine the existing name. Read that prompt before clicking through it — agreeing repoints the old name at your new selection, and every formula already using it changes answer.

Two more rules rarely bite but are worth knowing: a name can run to 255 characters, and names ignore capitalisation. Revenue, revenue, and REVENUE are one name, not three, which means you cannot use the same word twice with different capitals.

Tip: Pick one house style and stay on it — SalesTotal and Sales_Total are both fine, a workbook containing both is not. Autocomplete makes long descriptive names cheap to type, so favour NorthRegionTarget over NRT.

4. Use a Name in a Formula ​

A name goes anywhere a cell reference or range would go.

  1. Click an empty cell — F1 on the Sales tab.
  2. Type =SUM( and then start typing Rev. An autocomplete list drops down under the cell. Names carry a small tag icon to distinguish them from functions, which carry an fx icon.
  3. Press the Down arrow to highlight Revenue in the list, then press Tab. Excel inserts the full name into the formula.
  4. Type ) and press Enter. The cell returns the total of D2:D500.
=SUM(Revenue)

Both of these return the identical number:

=SUM(Revenue)
=SUM(D2:D500)

The second one makes you open the Sales tab and look at column D to find out what it counts. The first tells you at a glance, and that difference compounds across a workbook of forty formulas. Three more reasons the named version wins:

  • Names are absolute. Revenue never shifts when you copy the formula sideways or drag it down a column, so there is no dollar sign to remember and none to forget. This is the same problem covered in Understanding Absolute and Relative Cell References in Excel, solved at the source.
  • One place to repair. When the revenue column moves to column E, edit the name once in Name Manager and every formula using it follows. Hunting down D2:D500 across four sheets is the alternative.
  • They cross sheets without the tab name. =SUM(Revenue) works from any tab in the workbook. The address version needs =SUM(Sales!D2:D500).

Names combine with the usual functions exactly as a range would:

=SUMIF(Region,"North",Revenue)
=COUNTIF(Region,"North")
=AVERAGE(Revenue)
=VLOOKUP(A2,RepTable,3,FALSE)

Tip: Press F3 in the middle of typing any formula to open the Paste Name dialog, which lists every name in the workbook. Double-click one and Excel types it in with the exact spelling. This beats typing from memory, and it is the fastest cure for a #NAME? error caused by a typo.

5. Name Every Column at Once with Create from Selection ​

Naming eleven columns one at a time is a chore. Excel reads your header row instead.

  1. On the Sales tab, select the whole block including the header row — click A1 and press Ctrl + Shift + End, or select A1:D500 by hand.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection. The keyboard route is Ctrl + Shift + F3.
  3. The Create Names from Selection dialog opens with four checkboxes: Top row, Left column, Bottom row, Right column. Excel pre-ticks the ones it thinks apply.
  4. Tick Top row only, and clear any other box.
  5. Click OK. The dialog closes with no visible change to the sheet.

You now have four names — Region, Rep, Units, Revenue — each covering its own column from row 2 down, with the header cell itself excluded.

Verification: Click the arrow at the right of the Name Box. All four names are listed alphabetically. Select Units and Excel highlights C2:C500.

Excel repairs header text that breaks the naming rules rather than refusing:

  • A header reading Order Date becomes the name Order_Date — spaces turn into underscores.
  • A header reading % of Total becomes _of_Total — invalid characters turn into underscores too.
  • A header that is a number, such as 2026, is skipped entirely with no warning. Rename the header first if you need that column named.

6. Edit, Delete, and Audit Names in Name Manager ​

Name Manager is the one screen that shows you every name in the file at once, and it is where broken names get found.

  1. Press Ctrl + F3, or click Formulas > Name Manager in the Defined Names group. The Name Manager window opens.
  2. Read the four columns: Name, Value (a preview of what the range currently holds), Refers To (the address), and Scope.
  3. To rename, select the row and click Edit. The Edit Name dialog opens. Change the text in the Name box and click OK — Excel rewrites every formula in the workbook that used the old name. Nothing breaks and nothing needs finding.
  4. To repoint a name at different cells, select the row and click in the Refers to box at the bottom of the window, then drag over the new range on the sheet. Click the tick mark to the left of the box to commit.
  5. To remove a name, select the row and click Delete, then confirm. Do this only after Step 7 below.
  6. Click Close when you are done.

Finding names that are already broken: click the Filter dropdown at the top right of Name Manager and choose Names with Errors. The list narrows to names whose Refers To column shows #REF! — the signature of a name pointing at a row, column, or sheet that somebody deleted. Select each one, fix the Refers to box, and the formulas using it recover on the next recalculation.

Warning: Deleting a name that formulas still use does not warn you, and it does not undo cleanly across a save. Every formula referencing it turns into #NAME? immediately. Before deleting, press Ctrl + F, click Options, set Within to Workbook and Look in to Formulas, and search for the name. If the search finds nothing, the name is genuinely unused.

The same #NAME? error appears for two other reasons worth ruling out first, because neither needs a deletion: the name is misspelled in the formula, or it is scoped to a different worksheet than the formula sits on. Press F3 to insert the name from the list rather than retyping it, and check the Scope column before you conclude anything is missing. Other causes of that error are covered in How to Fix Common Excel Formula Errors.

7. Choose the Scope — Worksheet or Workbook ​

Scope decides where a name can be used, and it is set once, when the name is created.

  • Workbook scope is the default and the right answer most of the time. The name works in a formula on any sheet in the file, and there can be only one of it.
  • Worksheet scope confines the name to formulas on the sheet that defines it. Its real use is repetition: twelve monthly tabs that each need a name called Total pointing at their own numbers. Each tab defines its own Total, and each sheet's formulas pick up the local one.

To create a worksheet-scoped name:

  1. Select the cells on the sheet in question.
  2. Click Formulas > Define Name.
  3. Type the name, then click the Scope dropdown and select the sheet name instead of Workbook.
  4. Click OK. In Name Manager, the Scope column now shows the sheet name rather than Workbook.

To use a worksheet-scoped name from a different sheet, put the sheet name and an exclamation mark in front of it:

=SUM(January!Total)

Note: The Scope dropdown is greyed out when you edit an existing name — Excel does not convert between the two. To change a name's scope, note what it refers to, delete it, and create it again with the scope you want. Do the formula search from Step 6 first, so you know what will break in between.

When both a workbook name and a worksheet name share a word, the worksheet one wins on its own sheet. That is useful when it is deliberate and confusing when it is not, so keep the overlap rare and give the sheet-level names an obvious purpose.

Troubleshooting ​

TIP

Build the names before the formulas, not after. Convert the data to a table with Ctrl + T, then run Create from Selection over the header row, and every formula you write from that point reads in plain English at no extra effort. Retrofitting names to a finished workbook means editing formulas by hand, which is where the mistakes come from.

Symptom / ErrorPotential CauseSolution
#NAME? in a formula that worked yesterdayName deleted, or misspelledPress F3 to open Paste Name and insert the exact spelling. If the name is absent from that list, it was deleted — recreate it in Name Manager (Step 6).
The name that you entered is not valid.Space, leading digit, or a cell-like nameRemove the space, move the digit to the end, and avoid anything shaped like an address: Q1Sales, not Q1 (Step 3).
The name vanished right after typing itClicked away instead of pressing EnterRetype it in the Name Box and press Enter to commit (Step 1).
New rows at the bottom are missing from the totalThe name covers a fixed rangeExtend the range in Name Manager, or convert the data to a table with Ctrl + T so column references grow automatically. See How to Format Data as a Table in Excel.
#REF! in the Refers To column of Name ManagerThe named rows, column, or sheet was deletedFilter Name Manager to Names with Errors, select the name, and drag over the correct cells to repoint the Refers to box (Step 6).
A name works on one sheet and returns #NAME? on anotherThe name has worksheet scopeCheck the Scope column in Name Manager. Prefix it with the sheet name — =SUM(January!Total) — or recreate it with Workbook scope (Step 7).
Create from Selection skipped a columnThe header is a number or an empty cellGive the column a text header that starts with a letter, then run Create from Selection again (Step 5).