Using structured references with Excel tables

When you create an Excel table, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:

Instead of using explicit cell references

Excel uses table and column names

That combination of table and column names is called a structured reference. The names in structured references adjust whenever you add or remove data from the table.

Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook.

To include structured references in your formula, click the table cells you want to reference instead of typing their cell reference in the formula. Let’s use the following example data to enter a formula that automatically uses structured references to calculate the amount of a sales commission.

Sales Person

Sales Amount

% Commission

Commission Amount

1. Copy the sample data in the table above, including the column headings, and paste it into cell A1 of a new Excel worksheet.
2. To create the table, select any cell within the data range, and press Ctrl+T.
3. Make sure the My table has headers box is checked, and click OK.
4. In cell E2, type an equal sign (=), and click cell C2. In the formula bar, the structured reference [@[Sales Amount]] appears after the equal sign.
5. Type an asterisk (*) directly after the closing bracket, and click cell D2. In the formula bar, the structured reference [@[% Commission]] appears after the asterisk.
6. Press Enter. Excel automatically creates a calculated column and copies the formula down the entire column for you, adjusting it for each row.

What happens when I use explicit cell references?

If you enter explicit cell references in a calculated column, it can be harder to see what the formula is calculating.

1. In your sample worksheet, click cell E2
2. In the formula bar, enter =C2*D2 and press Enter.

Notice that while Excel copies your formula down the column, it doesn't use structured references. If, for example, you add a column between the existing columns C and D, you'd have to revise your formula.

How do I change a table name?

When you create an Excel table, Excel creates a default table name (Table1, Table2, and so on), but you can change the table name to make it more meaningful.

1. Select any cell in the table to show the Table Tools >Design tab on the ribbon.
2. Type the name you want in the Table Name box, and press Enter.

In our example data, we used the name DeptSales.

Use the following rules for table names:

• Use valid characters Always start a name with a letter, an underscore character (_), or a backslash (\). Use letters, numbers, periods, and underscore characters for the rest of the name. You can’t use "C", "c", "R", or "r" for the name, because they’re already designated as a shortcut for selecting the column or row for the active cell when you enter them in the Name or Go To box.

• Don’t use cell references Names can’t be the same as a cell reference, such as Z$100 or R1C1.
• Don’t use a space to separate words Spaces can’t be used in the name. You can use the underscore character (_) and period (.) as word separators. For example, DeptSales, Sales_Tax or First.Quarter.
• Use no more than 255 characters A table name can have up to 255 characters.
• Use unique table names Duplicate names aren’t allowed. Excel doesn’t distinguish between upper and lowercase characters in names so if you enter “Sales” but already have another name called “SALES" in the same workbook, you’ll be prompted to choose a unique name.
• Use an object identifier If you plan on having a mix of tables, PivotTables and charts, it's a good idea to prefix your names with the object type. For example: tbl_Sales for a sales table, pt_Sales for a sales PivotTable, and chrt_Sales for a sales chart, or ptchrt_Sales for a sales PivotChart. This keeps all of your names in an ordered list in the Name Manager.

Structured reference syntax rules

You can also enter or change structured references manually in the formula but to do that, it will help to understand structured reference syntax. Let’s go over the following formula example:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

This formula has the following structured reference components:

• Table name:DeptSales is a custom table name. It references the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name.
• Column specifier:[Sales Amount]and[Commission Amount] are column specifiers that use the names of the columns they represent. They reference the column data, without any column header or total row. Always enclose specifiers in brackets as shown.
• Item specifier:[#Totals] and [#Data] are special item specifiers that refer to specific portions of the table, such as the total row.
• Table specifier:[[#Totals],[Sales Amount]] and [[#Data],[Commission Amount]] are table specifiers that represent the outer portions of the structured reference. Outer references follow the table name, and you enclose them in square brackets.
• Structured reference:(DeptSales[[#Totals],[Sales Amount]] and DeptSales[[#Data],[Commission Amount]] are structured references, represented by a string that begins with the table name and ends with the column specifier.

To create or edit structured references manually, use these syntax rules:

• Use brackets around specifiers All table, column, and special item specifiers need to be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers. For example: =DeptSales[[Sales Person]:[Region]]
• All column headers are text strings But they don’t require quotes when they’re used in a structured reference. Numbers or dates, such as 2014 or 1/1/2014, are also considered text strings. You can’t use expressions with column headers. For example, the expression DeptSalesFYSummary[[2014]:[2012]] won’t work.

Use brackets around column headers with special characters If there are special characters, the entire column header needs to be enclosed in brackets, which means that double brackets are required in a column specifier. For example: =DeptSalesFYSummary[[Total $ Amount]]

Here’s the list of special characters that need extra brackets in the formula:

• Tab
• Line feed
• Carriage return
• Comma (,)
• Colon (:)
• Period (.)
• Left bracket ([)
• Right bracket (])
• Pound sign (#)
• Single quotation mark (')
• Double quotation mark (")
• Left brace ( <)
• Right brace (>)
• Dollar sign ($)
• Caret (^)
• Ampersand (&)
• Asterisk (*)
• Plus sign (+)
• Equal sign (=)
• Minus sign (-)
• Greater than symbol (>)
• Less than symbol ( <)
• Division sign (/)
• At sign (@)
• Backslash (\)
• Exclamation point (!)
• Left parenthesis (()
• Right parenthesis ())
• Percent sign (%)
• Question mark (?)
• Backtick (`)
• Semicolon (;)
• Tilde (~)
• Underscore (_)

• Use an escape character for some special characters in column headers Some characters have special meaning and require the use of a single quotation mark (') as an escape character. For example: =DeptSalesFYSummary['#OfItems]

Here’s the list of special characters that need an escape character (') in the formula:

• Left bracket ([)
• Right bracket (])
• Pound sign(#)
• Single quotation mark (')
• At sign (@)

Use the space character to improve readability in a structured reference You can use space characters to improve the readability of a structured reference. For example: =DeptSales[ [Sales Person]:[Region] ] or =DeptSales[[#Headers], [#Data], [% Commission]]

It’s recommended to use one space:

• After the first left bracket ([)
• Preceding the last right bracket (]).
• After a comma.

Reference operators

For more flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.

This structured reference:

By using the:

Which is cell range:

All of the cells in two or more adjacent columns

: (colon) range operator

=DeptSales[Sales Amount],DeptSales[Commission Amount]

A combination of two or more columns

, (comma) union operator

=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]

The intersection of two or more columns

(space) intersection operator

Special item specifiers

To refer to specific portions of a table, such as just the totals row, you can use any of the following special item specifiers in your structured references.

This special item specifier:

The entire table, including column headers, data, and totals (if any).

Just the data rows.

Just the header row.

Just the total row. If none exists, then it returns null.

Just the cells in the same row as the formula. These specifiers can’t be combined with any other special item specifiers. Use them to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column.

Excel automatically changes #This Row specifiers to the shorter @ specifier in tables that have more than one row of data. But if your table has only one row, Excel doesn’t replace the #This Row specifier, which may cause unexpected calculation results when you add more rows. To avoid calculation problems, make sure you enter multiple rows in your table before you enter any structured reference formulas.

Qualifying structured references in calculated columns

When you create a calculated column, you often use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column, called Commission Amount, that calculates the amount of commission in dollars, you can use the following formulas:

Type of structured reference

=[Sales Amount]*[% Commission]

Multiplies the corresponding values from the current row.

=DeptSales[Sales Amount]*DeptSales[% Commission]

Multiples the corresponding values for each row for both columns.

The general rule to follow is this: If you’re using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.

Examples of using structured references

Here are some ways to use structured references.

This structured reference:

Which is cell range:

All the cells in the Sales Amount column.

The header of the % Commission column.

The total of the Region column. If there is no Totals row, then it returns null.

=DeptSales[[#All],[Sales Amount]:[% Commission]]

All the cells in Sales Amount and % Commission.

=DeptSales[[#Data],[% Commission]:[Commission Amount]]

Just the data of the % Commission and Commission Amount columns.

Just the headers of the columns between Region and Commission Amount.

=DeptSales[[#Totals],[Sales Amount]:[Commission Amount]]

The totals of the Sales Amount through Commission Amount columns. If there is no Totals row, then it returns null.

Just the header and the data of % Commission.

=DeptSales[[#This Row], [Commission Amount]]

The cell at the intersection of the current row and the Commission Amount column. If used in the same row as a header or total row, this will return a #VALUE! error.

If you type the longer form of this structured reference (#This Row) in a table with multiple rows of data, Excel automatically replaces it with the shorter form (@). They both work the same.

E5 (if the current row is 5)

Strategies for working with structured references

Consider the following when you work with structured references.

• Use Formula AutoComplete You may find that using Formula AutoComplete is very useful when you enter structured references and to ensure the use of correct syntax. For more information, see Use Formula AutoComplete.
• Decide whether to generate structured references for tables in semi-selections By default, when you create a formula, clicking a cell range within a table semi-selects the cells and automatically enters a structured reference instead of the cell range in the formula. This semi-selection behavior makes it much easier to enter a structured reference. You can turn this behavior on or off by selecting or clearing the Use table names in formulas check box in the File >Options >Formulas >Working with formulas dialog.
• Use workbooks with external links to Excel tables in other workbooks If a workbook contains an external link to an Excel table in another workbook, that linked source workbook must be open in Excel to avoid #REF! errors in the destination workbook that contains the links. If you open the destination workbook first and #REF! errors appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes.
• Convert a range to a table and a table to a range When you convert a table to a range, all cell references change to their equivalent absolute A1 style references. When you convert a range to a table, Excel doesn’t automatically change any cell references of this range to their equivalent structured references.
• Turn off column headers You can toggle table column headers on and off from the table Design tab >Header Row. If you turn off table column headers, structured references that use column names aren’t affected, and you can still use them in formulas. Structured references that refer directly to the table headers (e.g. =DeptSales[[#Headers],[%Commission]]) will result in #REF.
• Add or delete columns and rows to the table Because table data ranges often change, cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all the data cells in a table, and you then add a row of data, the cell reference automatically adjusts.
• Rename a table or column If you rename a column or table, Excel automatically changes the use of that table and column header in all structured references that are used in the workbook.
• Move, copy, and fill structured references All structured references remain the same when you copy or move a formula that uses a structured reference.

Note: Copying a structured reference and doing a fill of a structured reference are not the same thing. When you copy, all the structured references remain the same, while when you fill a formula, fully qualified structured references adjust the column specifiers like a series as summarized in the following table.

If the fill direction is:

And while filling, you press: