Using structured references with Excel tables

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.

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.

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:

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.

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.

  : 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.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Related Topics

Overview of Excel tables
Video: Create and format an Excel table
Total the data in an Excel table
Format an Excel table
Resize a table by adding or removing rows and columns
Filter data in a range or table
Convert a table to a range
Excel table compatibility issues
Export an Excel table to SharePoint
Overviews of formulas in Excel