What a name range is and why you'd use one

A name range is a label you assign to one or more cells so you can refer to them by that label instead of by their cell address. Instead of writing a formula that says =SUM(B2:B47), you can name that range "MonthlyRevenue" and write =SUM(MonthlyRevenue) instead.

Name ranges make your formulas readable. When you come back to a spreadsheet three months later, a formula that says =MonthlyRevenue*TaxRate tells you what it does when ready. A formula that says =B2:B47*D5 does not.

They also protect you from mistakes. If you insert a row in the middle of your data, a name range automatically adjusts to include the new row. A cell address like B2:B47 does not — you have to fix it manually or your formula becomes wrong.

Key Takeaways

  • You create a name range by selecting the cells you want to name, then typing the name into the Name Box (the field to the left of the formula bar that normally shows the cell address).
  • A name range can refer to a single cell, a row, a column, or any rectangular block of cells, but not to cells scattered in different places.
  • Once you create a name range, you can type it into any formula on any sheet in the workbook and Excel will find those cells automatically.
  • You can see all the name ranges in your workbook and edit or delete them through the Name Manager, which you open from the Formulas tab.

The fastest way: select cells and type into the Name Box

Open your spreadsheet and select the cells you want to name. Click and drag to highlight them, or click the first cell, hold Shift, and click the last cell to select a range.

Look at the top left of your screen, just above the column letters. You will see a field that currently shows the cell address of what you selected — something like "B2:B47" or just "A1". This is the Name Box. Click on it. The text inside will highlight.

Type the name you want to give these cells. Use a single word with no spaces — "MonthlyRevenue" works, but "Monthly Revenue" does not. Excel allows letters, numbers, and underscores, but the name must start with a letter. Press Enter. The name is now created.

To test it, click on any empty cell and type a formula that uses your new name — for example, =SUM(MonthlyRevenue). Excel will calculate it using the cells you named. You can now use that name in any formula anywhere in the workbook.

Creating a name range from the Formulas tab

If you prefer to use the menu instead of the Name Box, select your cells and go to the Formulas tab at the top of the screen. Click Define Name (in some versions of Excel, it says "New Name"). A dialog box will open.

The dialog shows the cells you selected in the "Refers to" field. Type your name in the "Name" field at the top. You can also choose which sheets this name range applies to — by default it applies to the whole workbook, which is usually what you want. Click OK.

This method is slower than using the Name Box, but it gives you more options. You can use it if you want to create a name range that refers to cells on a different sheet without having to navigate there first.

Using the Name Manager to see, edit, or delete name ranges

Once you have created several name ranges, you may want to see them all in one place or change what cells a name refers to. Go to the Formulas tab and click Name Manager. A window opens showing every name range in your workbook, what cells each one refers to, and which sheet it applies to.

To edit a name range, click on it in the list and click Edit. Change the name or the cell range and click OK. To delete a name range you no longer use, click on it and click Delete. This does not delete the cells themselves — it only removes the label.

If you have a formula that uses a name range you delete, that formula will break and show #NAME? as the result. For this reason, check your formulas before deleting a name range, or use Find and Replace to search for the name and see where it is being used.

Name ranges that refer to single cells or entire columns

You can name a single cell the same way you name a range. Select the cell, type the name into the Name Box, and press Enter. This is useful for values that appear in only one place and are used in many formulas — for example, a tax rate or a discount percentage.

You can also create a name range that refers to an entire column or row. Select the column by clicking its letter at the top, or the row by clicking its number on the left. Then type the name into the Name Box. Any formula using that name will include every cell in that column or row, even if you add new data later.

Be careful with this approach — if your column contains headers or labels that are not numbers, a formula like =SUM(EntireColumn) may produce unexpected results or errors. It is usually safer to name only the data cells you actually want to calculate.

Name ranges that refer to cells on different sheets

You can create a name range that refers to cells on a sheet other than the one you are currently viewing. Select the cells on that sheet first, or use the Name Manager to type the reference manually.

If you use the Name Box, select the cells on the other sheet, and the Name Box will show the full reference — something like Sheet2!B2:B47. Type your name and press Enter. When you use that name in a formula, Excel will automatically look on the correct sheet.

If you use the Name Manager, you can type the reference directly in the "Refers to" field. The syntax is SheetName!CellRange. For example, Data!A1:A100 refers to cells A1 through A100 on the sheet named "Data".

Common mistakes and how to avoid them

The most common mistake is trying to create a name range with spaces or special characters. Excel will reject names like "Monthly Revenue" or "Revenue-2024". Use underscores or no separation at all: "Monthly_Revenue" or "MonthlyRevenue".

Another mistake is naming a range with a word that Excel already uses, like "Sum" or "Total". Excel will usually warn you, but it can cause confusion. Avoid short, common words and use descriptive names that make sense in your formulas.

If you select non-rectangular cells — for example, B2:B5 and D2:D5 with a gap between them — Excel will not let you create a single name range. You have to name each block separately, or select a larger rectangle that includes the gap.

Frequently Asked Questions

Can I use a name range in a formula on a different sheet?

Yes. By default, a name range you create applies to the entire workbook, so you can use it in any formula on any sheet. If you want a name range to explore only to one sheet, you can set that in the Name Manager when you create it, but most of the time you want it available everywhere.

What happens if I delete a sheet that contains cells in a name range?

The name range will break. Any formula using that name will show #REF! as the result. You can see which name ranges are broken by opening the Name Manager — broken ranges will show an error in the "Refers to" column. Delete them or edit them to point to cells that still exist.

Can I create a name range that includes a header row?

Yes, but it is usually better not to. If your data has headers, name only the data cells below the header, not the header itself. This way, when you use the name in a formula like =SUM(MonthlyRevenue), Excel sums only the numbers, not the text header.

How do I change what cells a name range refers to?

Open the Name Manager from the Formulas tab, click the name you want to change, and click Edit. Change the cell range in the "Refers to" field and click OK. Any formula using that name will now refer to the new cells.

Can I copy a name range to a different workbook?

Not directly. Name ranges are stored in the workbook where you create them. If you need the same names in another workbook, you have to create them again. You can copy the formulas that use the names, but the names themselves will not transfer.