How to name a table in Excel and use it in formulas

Summary. To name a table in Excel, click any cell in the table, open the Table Design tab, type a new name in the Table Name box and press Enter. On a Mac the box is on the Table tab; in Excel for the web, double-click Table Name on the Table Design tab. A name must start with a letter, an underscore or a backslash, use only letters, numbers, periods and underscores, contain no spaces, not match a cell reference such as Z$100, not be C, c, R or r, stay within 255 characters and be unique regardless of case. On Windows, Formulas > Name Manager lists every table and defined name and can rename them too. No table yet? Press Ctrl+T on Windows or Cmd+T on a Mac. Renaming a table or a column updates every structured reference that uses it, so a name such as tbl_Sales lets you write =SUM(tbl_Sales[Sales]) instead of =SUM(C2:C5) and keep it working.

About this article. For anyone who builds or maintains Excel workbooks, including IT teams setting template standards. Every step, label, shortcut and naming rule was checked on 15 November 2026 against Microsoft Support's Excel articles and Microsoft Learn's release notes and lifecycle pages. Labels can differ slightly between versions; no vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.

Where to name or rename a table

The steps apply to Excel for Microsoft 365 on Windows (Current Channel Version 2610, build 20527.20196, released 10 November 2026) and Mac (Version 16.115, same day), Excel 2024 and Excel for the web. The shortcuts below come from Microsoft's keyboard reference, and its names overview shows Formulas > Name Manager on all three platforms.

Excel 2021 reached end of support on 13 October 2026, and Excel 2016 and 2019 on 14 October 2025, but the steps still work in them. Microsoft's rename article, illustrated in Excel 2016, still gives the older path Table Tools > Design > Properties > Table Name.

Step 1: Turn your data into an Excel table

Only an Excel table has a table name, so check you have one first. By default a table shows banded rows and filter arrows in its header row.

1. Click any cell inside your data.

1. Press Ctrl+T on Windows, or select Home > Format as Table and pick a style.

1. In the Create Table dialog, check the range and tick My table has headers.

1. Select OK.

On a Mac the dialog is called Format as Table; in Excel for the web, leaving the headers box clear adds headers named Column1, Column2 and so on. The new table is named Table1, or the next free number. It is not the same thing as a What-If Analysis data table.

Step 2: Name the table on Windows, Mac or the web

Excel for Windows

1. Click any cell in the table. The Table Design tab appears on the ribbon.

1. In the Properties group, click in the Table Name box.

1. Type the new name, for example tbl_Sales, and press Enter.

Excel for Mac

1. Click any cell in the table.

1. Open the Table tab and find Table Name.

1. Highlight the current name, type the new one and press Return.

Excel for the web

1. Click any cell in the table.

1. On the Table Design tab, double-click Table Name and type the new name.

Microsoft's web instructions still carry an old note calling this a beta for some Office Insiders.

Tip for Windows and Mac. Once tables have names, open the Name Box drop-down to the left of the formula bar (the rename article calls it the address bar). It lists every table, and choosing one jumps straight to it, even on another worksheet.

Excel table naming rules

Microsoft applies these rules to table names:

• First character: a letter, an underscore (_) or a backslash (\\).

• Other characters: letters, numbers, periods and underscores only.

• No spaces: write DeptSales, Sales_Tax or First.Quarter instead.

• No cell references: a name cannot match a reference such as Z$100 or R1C1. Q1 and FY2027 are both valid cell addresses, so Excel refuses them as table names.

• Not C, c, R or r: Excel already uses these to select the active cell's column or row when typed into the Name or Go To box.

• Up to 255 characters.

• Unique, ignoring case: Sales and SALES count as the same name.

Microsoft also suggests prefixing each name with its object type, such as tbl_ for tables, pt_ for PivotTables and chrt_ for charts, so related names sit together in Name Manager.

Rename a table in Name Manager (Windows)

Name Manager is the one place to see and edit every defined name and table name in a workbook.

1. On the Formulas tab, in the Defined Names group, select Name Manager.

1. To show tables only, open Filter and choose Table Names.

1. Double-click the table name, or select it and choose Edit.

1. In the Edit Name dialog, type the new name in the Name box and select OK.

When you change a defined name or table name here, every use of it in the workbook changes too. Name Manager cannot be used while you are editing a cell, and it does not show names defined in VBA or hidden names. Microsoft's Name Manager article covers Windows only; on a Mac or the web, use the Table Name box.

Use the table name in formulas: structured references

A structured reference combines the table name with a column name in square brackets. You rarely type one by hand: start a formula, select table cells with the mouse, and Excel writes the reference for you.

Worked example

Enter this data in A1:D5, press Ctrl+T, then rename the table tbl_Sales. Type a fifth header, Commission, in E1; the table expands to include the new column.

In E2 type =[@Sales]*[@[Commission %]] and press Enter. Excel creates a calculated column and fills the formula down every row. Commission % needs the extra brackets because the percent sign is one of the special characters that require them. Check: 1200 x 10% = 120, 950 x 12% = 114, 1800 x 15% = 270 and 700 x 10% = 70.

Now refer to the table from cells outside it:

            Formula (outside the table)                                Result  Note
=SUM(tbl_Sales[Sales])                                     4650    1200 + 950 + 1800 + 700
=SUM(tbl_Sales[Commission])                                574     120 + 114 + 270 + 70
=SUMIFS(tbl_Sales[Sales],tbl_Sales[Region],"North")        3000    1200 + 1800
=ROWS(tbl_Sales)                                           4       table name alone = data rows only
=XLOOKUP("Chloe",tbl_Sales[Rep],tbl_Sales[Sales])          1800    Microsoft 365, Excel 2024, Excel 2021
=INDEX(tbl_Sales[Sales],MATCH("Chloe",tbl_Sales[Rep],0))   1800    also works in Excel 2019 and 2016
          

The table name alone means the data rows, without header or total row. XLOOKUP is not available in Excel 2016 or 2019, hence the INDEX and MATCH line; our XLOOKUP guide and VLOOKUP guide cover both lookups.

Type in the row directly under the table and the table expands to include it, so the totals above update without any edits; rename tbl_Sales later and every formula switches to the new name.

Structured reference cheat sheet

• tbl_Sales[Sales]: the data in one column, without header or total row.

• tbl_Sales[[Region]:[Sales]]: all the cells in adjacent columns.

• [#All], [#Data], [#Headers], [#Totals]: the whole table, the data rows, the header row or the total row. [#Totals] returns null if there is no total row.

• [@Sales] or tbl_Sales[@Sales]: the value in the same row as the formula.

• Use the short, unqualified form ([@Sales]) inside the table and the fully qualified form (tbl_Sales[Sales]) outside it.

• Column headers that contain special characters, such as %, #, $, &, a comma or a period, need an extra pair of brackets. Five of them, [, ], #, ' and @, also need a single quotation mark in front as an escape character.

Common problems and fixes

• Excel inserts C2:C5 instead of tbl_Sales[Sales]. On Windows, tick Use table names in formulas under File > Options > Formulas > Working with formulas. Converting a range into a table also leaves older formulas with their A1 references.

• The name is rejected. It probably contains a space, starts with a number, or matches a cell address such as Q1. Try tbl_Q1 instead.

• Formulas changed to $C$2:$C$5. The table was converted back to a range, and Excel turns every structured reference into its absolute A1 equivalent when that happens.

• #REF! in a formula that points at another workbook's table. Open the source workbook and the errors resolve.

• A calculated column misbehaves after you add rows. In a one-row table Excel keeps the long #This Row form, which can give unexpected results later, so enter at least two rows before writing structured reference formulas.

• Merge & Center is greyed out. Microsoft lists cells formatted as an Excel table as one reason the command is disabled. Put titles above the table instead; our guide to merging cells in Excel covers the alternatives.

For IT teams and businesses

Table names are the cheapest documentation a shared workbook can have. Agree a convention (tbl_ for tables, pt_ for PivotTables, nouns rather than dates) for your templates, so a formula such as =SUM(tbl_Invoices[Net]) explains itself at audit or handover. Microsoft notes that defined names stay important when workbooks feed PivotTables, the Data Model and Power BI, so stable table names also make downstream reports less fragile.

Add three checks to your spreadsheet review: links to tables in other workbooks need the source file open; converting a table to a range silently swaps structured references for fixed ranges; and XLOOKUP formulas will not work for anyone still on Excel 2016 or 2019.

How eCorpIT can help

eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team turns fragile spreadsheets into named, structured tables and reporting pipelines that survive handovers and audits. Talk to us at /contact-us/.

Last updated: 15 November 2026.

References

1. Using structured references with Excel tables — Microsoft Support

1. Rename an Excel table — Microsoft Support

1. Use the Name Manager in Excel — Microsoft Support

1. Keyboard shortcuts in Excel — Microsoft Support

1. Release notes for Current Channel releases — Microsoft Learn

1. Release notes for Office for Mac — Microsoft Learn

1. Define and use names in formulas — Microsoft Support

1. Ending support in 2026 — Microsoft Lifecycle, Microsoft Learn

1. Ending support in 2025 — Microsoft Lifecycle, Microsoft Learn

1. Overview of Excel tables — Microsoft Support

1. Create and format tables — Microsoft Support

1. Resize a table by adding or removing rows and columns in Excel — Microsoft Support

1. XLOOKUP function — Microsoft Support

1. Merge and unmerge cells in Excel — Microsoft Support