Yes. In Excel for Microsoft 365 on Windows, use a table name and its column header in a structured reference, such as =SUM(Sales[Amount]), instead of a cell range. Excel adjusts these references as table rows are added or removed, making them useful for formulas that need to keep tracking a changing list.
What a table name means in Excel
An Excel table is a formatted, filterable data range with named columns. A table name identifies the whole table, while its column headers identify fields within it. Together, they let a formula describe the data it needs: Sales[Amount] means the Amount column in the table named Sales. This style of reference is called a structured reference.
Structured references are different from defined names created in Name Manager. A table name belongs to an Excel table and is available to formulas throughout the workbook; a defined name can refer to a cell, range, formula, or constant. You do not need to create a separate defined name for each table column.
When Excel creates a table, it assigns a default name such as Table1. You can give it a more descriptive name, such as Sales or Orders, so its purpose is clear in formulas. Table names are workbook-wide identifiers, so each table in a workbook needs a distinct name.
Create and name a table
On Windows, select a cell in the data range and choose Insert > Table, or press Ctrl+T. Check that Excel has selected the correct range and select My table has headers if the first row contains column names. Use one clear, unique header for each column; headers become part of the references used in formulas.
To change the table name, select a cell in the table and open Table Design > Table Name. Type a name without spaces, then press Enter. For example, use SalesData or MonthlySales rather than Monthly Sales. Table names can contain letters, numbers, periods, and underscores, but they must begin with a letter, underscore, or backslash and cannot resemble a cell reference.
Excel for Mac and Excel for the web also support tables and structured references. The relevant contextual tab or controls can be labeled differently depending on the platform, but the reference syntax is the same. In particular, a formula such as =SUM(Sales[Amount]) works as a structured reference wherever the workbook supports Excel tables.
How structured references are written
The basic pattern is the table name followed by a column name in square brackets. A header with a space is still referenced by its full displayed name inside the brackets, as in Sales[Net Sales]. Excel usually writes the reference for you when you select a table column while building a formula.
By default, a reference to a table column refers to its data cells, not the header or totals row. Structured references also have special item specifiers for particular parts of a table:
Sales[#Headers]refers to the header row.Sales[#Data]refers to the table’s data rows.Sales[#Totals]refers to its totals row.Sales[#All]refers to the full table, including headers and totals if present.[@Amount]refers to the Amount cell in the current row when the formula is entered in a table.
The at sign in a row reference is shorthand for the current row. It is especially useful for formulas in calculated columns, where each row should use values from that same row. A table name can also be used with a column reference in a larger formula, for example when summing a column or counting entries that meet a condition.
Examples for common tasks
Assume a table named Sales has an Amount column with three data values: 120, 80, and 50. To total the column, enter this formula in a cell outside the table:
=SUM(Sales[Amount])
The result is 250. If more sales rows are added to the table, the formula includes their Amount values automatically.
For a conditional count, assume Sales has a Region column containing West, East, and West. Enter this formula in a worksheet cell:
=COUNTIF(Sales[Region],"West")
The result is 2 because two data rows have West in Region. The quotation marks around West are part of the text criterion.
For a row calculation, assume an Orders table has Quantity, UnitPrice, and LineTotal columns. In the LineTotal column, use:
=[@Quantity]*[@UnitPrice]
If the current row has a quantity of 3 and a unit price of 12.50, that row’s result is 37.50. Excel can copy the formula down the calculated column, applying the current-row references separately to each order.
You can also use structured references for selected parts of a table. For example, with a Sales table that has an Amount header, =Sales[[#Headers],[Amount]] returns the header text Amount. This kind of reference is less common in everyday calculations, but it can help when a formula needs to refer to a particular table component rather than its data values.
Why use table names instead of cell ranges?
A conventional range reference such as B2:B4 identifies specific worksheet positions. A structured reference such as Sales[Amount] identifies a field by its table and header. That difference matters when a list grows: adding a row directly to a table normally expands the table, and formulas that refer to its column follow the expanded data.
Structured references are also easier to interpret in a long formula. Compare a reference to a column called Amount with a range whose worksheet location may not reveal what the values represent. Table headers give the reference meaning, and renaming a table through Excel’s table controls updates references to it in formulas.
Cell ranges are still useful. They can be a straightforward choice for a small fixed calculation, a one-off analysis, or a range that is not organized as a table. A table is more helpful when data has headers, repeated records, filters, and a likelihood of being extended. Choosing a table does not change the underlying arithmetic; it changes how the formula identifies the inputs.
Calculated columns and automatic expansion
When you enter a formula in a table column, Excel may treat it as a calculated column and fill the formula through the other rows in that column. If you later add a row, Excel can extend the formula to it as well. This behavior helps keep repeated row calculations consistent, but it can be surprising if you intended to put a one-off formula inside the table.
Excel’s setting for automatically filling formulas in tables is under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. The relevant option is Fill formulas in tables to create calculated columns. If Excel does not fill a formula as expected, check that setting and confirm the formula was entered in a table column. Excel for Mac has its own Preferences interface, so the Windows path does not apply there exactly.
Calculated columns can be especially handy for standard measures such as line totals, tax amounts, or status labels. They are not a substitute for checking the formula: verify that the row references match the intended fields, and inspect several rows after a change to ensure the results remain appropriate.
Common problems and how to fix them
- The table name is not recognized: Select a cell in the table and check its name on Table Design. Confirm spelling and remember that table names cannot contain spaces.
- A column reference is not recognized: Check the header text. Renamed or misspelled headers can make a reference invalid; selecting the column while editing a formula is a practical way to insert its current name.
- The formula returns an unexpected total: Check whether the column contains text, blanks, or values stored as text. Also confirm that the formula references the intended table and column rather than a similarly named one.
- A row formula shows the wrong row’s value: In a formula inside a table, use a current-row reference such as
[@Quantity]when you want the value from the row containing that formula. - Excel changes the formula when you fill it down: This is usually the calculated-column behavior. Review the formula and the table’s fill-formulas setting rather than assuming the change is an error.
- A reference looks unusually complicated: Headers with spaces or punctuation can require extra structured-reference syntax. Let Excel build the reference by selecting the header or column during formula entry instead of guessing the bracket structure.
One common misconception is that a table name can be typed anywhere a cell address is expected. A table reference has its own syntax: use the table name and, for a particular field, the bracketed header. Also, a structured reference does not make bad or missing source data correct; it only identifies which table data the formula should use.
When to choose a table reference
Use structured references when the worksheet contains a continuing list of records and formulas should track its columns as it changes. They are often a good fit for sales logs, inventory lists, survey responses, and task registers. Use ordinary cell references when the calculation is intentionally tied to fixed positions or when the source is a small, unrelated block rather than a table.
For an existing range, converting it to a table is not required just to use formulas, but it gives the data table features and structured-reference syntax. Before converting, check that the first row really contains headers and that the selected range does not accidentally include unrelated cells. If the data has blank rows or inconsistent headers, tidy it first; a well-formed table is easier to reference and maintain.
Frequently Asked Questions
Do Excel table names work in formulas?
Yes. Use the table name by itself when referring to a table area, or pair it with a column header in brackets to refer to that column. For example, Sales[Amount] identifies the Amount data in Sales.
How do I find a table’s name?
Select a cell in the table and look for the Table Name box on the Table Design tab in Excel for Windows. You can rename the table there. On other platforms, select the table and look for its contextual table controls.
Do structured references update when I add rows?
In normal table use, references to table columns expand with the table as rows are added. Make sure new records are actually included in the table; typing data far below it with a blank gap may not extend the table in the way you expect.
Can I use a table name in a formula outside the table?
Yes. A formula outside the table can refer to a whole column, for example inside SUM or COUNTIF. The current-row shorthand with the at sign is intended for a row within the table, because it depends on the table’s current row.
Are Excel table names the same as named ranges?
No. Both can be used in formulas, but a table name identifies an Excel table, whose columns can be referenced by their headers. A defined name is a separate workbook feature that can point to a range or another formula result.
Related
- Excel Shortcut to Move Between Sheets in a Workbook
- Excel Shortcut to Insert a Row Every Other Row in a List
- Excel Shortcut to Insert a Table Row on Windows and Mac
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Calculating Hours Worked: Formulas and TipsTo calculate hours worked in Excel, subtract the start time from the end time; for a shift that…
- Excel Data Validation INDIRECT: Dependent Drop-DownsYes. In Excel, use INDIRECT in a data-validation list to make a drop-down depend on a selection in…
- How to Add a Filter to a Column in ExcelTo add a filter in Excel for Microsoft 365 on Windows, click a cell in your data, choose…
- Excel Flash Fill: Fill a Column Without a FormulaExcel Flash Fill is not a formula: it recognizes a pattern in examples you type and fills the…
- SUMPRODUCT in Excel: What It Contains and How It WorksExcel’s SUMPRODUCT contains one required array argument and optional additional arrays; it multiplies corresponding entries across the arrays,…
- Combine HLOOKUP and VLOOKUP in ExcelYes. You can nest VLOOKUP inside HLOOKUP so VLOOKUP finds the row number and HLOOKUP finds the column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.