Excel Named Range Table: How They Work and When to Use

How-to basics · Updated November 19, 2026

In Excel, a “named range table” is not one combined feature: a named range gives a name to cells or a formula, while an Excel Table is a structured data object that can expand as you add rows. To make a table, select your data and choose Insert > Table; to name a range, use Formulas > Define Name.

What “named range table” means

People use this phrase to ask a few different questions: whether an Excel Table has a name, how to turn a named range into a table, or whether a named range can refer to table data. The distinction matters because the features solve related but different problems. A name is a label you can use in formulas and other workbook features. A Table organizes a rectangular block of data into columns and rows with headers and built-in behavior.

For example, a workbook could contain a Table named SalesData and a named range called SalesAmounts. SalesData refers to the entire table object. SalesAmounts might refer only to its Amount column, or to a fixed block of cells elsewhere. Naming a Table does not automatically create a separate named range, and naming a range does not convert its cells into a Table.

In Microsoft 365 for Windows, Table names are managed from the Table Design tab, while defined names are managed from the Formulas tab or the Name Box. The two features can be used together, which is often more useful than choosing one and ignoring the other.

Create and name an Excel Table

A Table is a good starting point when your data is a list of records, such as orders, inventory, or survey responses. Each column should have one clear header, and each row should represent one record. Avoid blank header cells and merged cells in the data area.

  1. Select a cell in the data, or select the complete range.
  2. Choose Insert > Table.
  3. Check the range in the dialog box. Select My table has headers if the first row contains column names, then choose OK.
  4. Click inside the new Table. On the Table Design tab, enter a meaningful name in the Table Name box and press Enter.

Excel supplies a default name such as Table1. A useful name such as SalesData or Inventory is easier to recognize in formulas. Table names cannot contain spaces, must begin with a letter, an underscore, or a backslash, and cannot look like a cell reference. Use a compact name that describes the data rather than a temporary purpose.

Tables also provide filter buttons, consistent header formatting, and options such as a Total Row on the Table Design tab. When you enter data directly beneath or beside a Table, Excel will usually extend it to include the new records. This makes Tables particularly helpful for source data that grows over time. Check that new entries have joined the Table before relying on a formula or chart to include them.

Create a named range

A named range can refer to one cell, a rectangular range, or—in some cases—a formula or constant. To define one in Excel for Microsoft 365 on Windows, select the cells, choose Formulas > Define Name, enter a name, confirm the scope and Refers to entry, and choose OK. The Name Manager, opened from Formulas > Name Manager, lets you review, edit, and delete existing names.

For a quick name that refers to selected cells, select the cells, click the Name Box to the left of the formula bar, type a name without spaces, and press Enter. You can later use that name in a formula or select it from the Name Box to jump to the cells. When defining a name through the dialog, check Scope: Workbook makes it available throughout the workbook, while a worksheet scope limits it to that sheet.

Suppose a worksheet called Sheet1 has monthly sales amounts in B2:B100. In the Refers to box, a name called SalesAmounts could refer to =Sheet1!$B$2:$B$100. The dollar signs make the cell references absolute, so they do not shift when the name is used in a formula. A name can make a formula easier to read, but a fixed range like this will not automatically include entries added below row 100.

Names have rules: they cannot contain spaces, and they cannot be written like a cell address such as A1. Use a descriptive label that starts with a letter or underscore and avoid punctuation that Excel does not allow. Names are not case-sensitive, so SalesAmounts and salesamounts are treated as the same name.

Use a named range with a Table

Tables and names work well together. Table formulas can use structured references, which identify columns by their headers instead of by worksheet coordinates. This can be easier to understand than a formula pointing to a column letter, and references to a Table column generally adjust as Table rows are added or removed.

Assume a Table named SalesData has a column headed Amount. To total that column, enter this formula in a cell outside the Table: =SUM(SalesData[Amount]). Excel adds the values in the Amount column. If the Table contains 125 sales records, the formula totals those 125 amounts; adding a record to the Table normally brings its amount into the structured reference automatically.

You can also define a workbook name that refers to the Table column. In Name Manager, create a name such as SalesAmounts and enter =SalesData[Amount] in Refers to. With the same SalesData Table and Amount header, the formula =SUM(SalesAmounts) returns the same total. This extra name can be useful if a long or technical header would make formulas awkward, or if you want to change the underlying reference later without editing every formula.

A name that refers to a Table column is different from a name that refers to fixed cells. The Table-based reference follows the Table; a fixed address such as B2:B100 has a defined endpoint. If a workbook must accommodate changing records, using a Table column directly is often simpler than maintaining a manually sized named range.

Which one should you choose?

Choose an Excel Table when you have a structured list that will be sorted, filtered, analyzed, or extended. Choose a named range when you want a memorable label for a particular cell or range, or when a formula, chart, or workbook feature is easier to manage using a short name. They are not competing versions of the same thing.

  • Use a Table for transaction lists, product catalogs, staff directories, and other datasets with consistent columns.
  • Use a named range for a single assumption cell, a fixed input range, or a range that needs a convenient label in a formula.
  • Use both when a growing dataset benefits from Table behavior but readers would benefit from a short name for a particular column or calculation input.

For instance, a tax-rate assumption stored in one cell can have a name such as TaxRate, making a calculation more readable. An order list, by contrast, is usually better represented as a Table because its rows grow and users may want filters. A name can label the tax-rate cell, but it does not provide the order list with Table headers, filtering, or structured references.

Scope and how references change

Workbook-scoped names are available from any worksheet in the file. A worksheet-scoped name is associated with one sheet and can be useful when the same label has a different meaning on different sheets. If a formula uses a name that exists at both worksheet and workbook scope, the local sheet-level name can take precedence on that sheet. This can make a workbook confusing, so use local scope deliberately and inspect the Scope column in Name Manager when a name behaves unexpectedly.

A named range does not inherently grow just because nearby cells are filled in. If it refers to B2:B100, adding a value in B101 does not necessarily make that value part of the range. You can edit the reference in Name Manager, use a Table-based reference, or build a dynamic named formula when there is a specific reason to do so. Dynamic formulas can be harder for other people to maintain; for ordinary expanding lists, a Table is usually the more straightforward choice.

Tables have their own naming rules and are separate from worksheet tab names. Renaming a worksheet does not mean you have renamed the Table. When you use a Table in formulas, Excel may update structured references after a Table or column is renamed, but review dependent formulas if you make substantial structural changes.

Common problems and fixes

If Excel rejects a name, first check for spaces, an invalid first character, or a name that resembles a cell reference. Also check whether that name is already in use at the relevant scope. A name can be valid yet still be difficult to understand, so avoid short labels such as Data or Value when a workbook contains many unrelated ranges.

If a formula using a name returns an unexpected result, open Formulas > Name Manager and inspect both Scope and Refers to. A worksheet-level name may point somewhere different from a workbook-level name, or a fixed range may stop before the latest records. Confirm that the referenced cells contain the expected values and that the formula is using the intended name.

If a Table formula or chart misses new records, check whether the records were actually added inside the Table. A blank row between the Table and new data, pasting into an unexpected location, or changed Table boundaries can prevent expansion. Click in the Table and look at the range shown when choosing Table Design or verify the Table’s visible outline. Extend or resize the Table if needed, then check that the structured reference includes the new rows.

Another common source of confusion is treating the Table’s display name as a named range. The Table name identifies the structured data object; a defined name appears in Name Manager and has a Refers to entry. If you need a specific column reference for a formula, use the Table’s column reference or create a defined name that points to it—do not assume that naming the Table created a name for every column.

Excel for Mac and the web

The steps above use Excel for Microsoft 365 on Windows. Excel for Mac has the same core concepts: create a Table with Insert > Table, and manage defined names through the Formulas tab or the Name Box. Ribbon labels and dialog layouts can vary slightly by release. Keyboard shortcuts can also differ by keyboard and version, so the menu path is the dependable route when a shortcut does not work.

Excel for the web supports Tables and names, but its ribbon and name-management options can differ from the Windows desktop application. If you do not see a particular Name Manager command in the web interface, open the workbook in desktop Excel to inspect or manage the name. Workbooks shared between versions retain the distinction: a Table remains a Table, and a defined name remains a defined name.

Frequently Asked Questions

Is an Excel Table the same as a named range?
No. A Table is a structured dataset with headers and Table features. A named range is a label that refers to cells, a range, or another supported reference. A Table can be referenced by a name, but the two objects are not interchangeable.

How do I find all named ranges in a workbook?
In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager. The list shows defined names, their references, and their scope. The Name Box is useful for navigating to a known name, but Name Manager is better for reviewing the complete list.

Will a named range expand when I add rows?
Not automatically in every case. A name pointing to a fixed address may continue to cover only that address. A name referring to a Table column can follow the Table as it grows, which is one reason Tables are a practical choice for expanding lists.

Can I convert a named range into a Table?
You can turn the cells it refers to into a Table, but the name itself does not convert. Select the range’s cells and choose Insert > Table, confirm whether the first row contains headers, then assign a Table name on Table Design. Review existing formulas afterward because they may still refer to the original defined name.

Can a Table have spaces in its name?
No. Use a compact name without spaces, such as SalesData or Sales_2026. Table column headers can contain spaces; structured references handle them, though long headers may make formulas less convenient to read.

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.