Using structured references in your formulas

Structured references make it much easier and more intuitive to work with table data when you are using formulas that reference a table– either portions of a table or the entire table. They are especially useful because table data ranges often change, and the cell references for structured references adjust automatically. This minimizes the need to rewrite formulas as rows and columns are added and deleted in a table, or when external data is refreshed

You may prefer this structured reference	To this cell reference
=SUM(DailyExercise[CardioTraining])	=SUM(C2:C7)

Components of a structured reference

To work with tables and structured references effectively, you need to understand how to create the syntax of structured references when you are creating formulas. The components of a structured reference are illustrated in the following example of a formula that calculates the average number of minutes in CardioTraining:

Provide a meaningful name to reference the actual table data, like DailyExercise (and excludes the header and totals row, if any).

#Data is a special item specifier that refers to a specific portion of the table (the data).

 CardioTraining is a column specifier, which is derived from the column header.

 The table specifier is made up of both the special item specifier and the column specifier enclosed in another set of brackets. 

 The table name, column, and special item specifier make up a structured reference.

Note: If you are using structured references within a table, you do not need to include the table name or the brackets around the table specifier. This is called an unqualified reference. For the example above, the unqualified reference is =AVERAGE([#Data],[CardioTraining]). It is a good habit to qualify your structured references, however, in case you decide to move a formula outside the table.

Special item specifiers

By using special item specifiers, you can refer to exactly the rows you want, without having to know the row numbers.

The special item specifier	Refers to
#All	The entire table, including the column headers, data, and totals.
#Data	Just the data rows.
#Headers	Just the header row.
#Totals	Just the total row. If none exists, then it returns null.
#This Row or @	Just the cells in the same row as the formula. This specifier cannot be combined with other special item specifiers.

Syntax rules for structured references

Follow these rules to make sure that your structured references are understood by Excel.

• You must enclose all table, column, and special item specifiers in brackets.

• Column headers, and therefore column specifiers, are always text strings, even if they include numbers. This means that you cannot use mathematical expressions with the data in these cells.

• If a column header contains certain special characters, its column specifier must be enclosed in an additional set of brackets. These characters are: space, 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 ($), karet (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/). The only exception to this is if the only special character included is a space character.

• The following characters have special meaning and require the use of a single quotation mark (') as an escape character: left bracket ([), right bracket (]), pound sign(#), and single quotation mark ('). For example, a column called Item# would have this column specifier: [Item'#].

• To improve readability of a structured reference, you are permitted to insert space characters into the reference in three places: immediately after the first left bracket ([), immediately before the last right bracket (]), and after commas.

Examples of structured references

These are examples of structured references for use within a formula – they will not work alone.

This structured reference	Refers to
DailyExercise[[#All],[StrengthTraining]]	All cells in the StrengthTraining column, including the header, data, and totals row.
DailyExercise[[#Headers],[CardioTraining]:[StrengthTraining]]	The headers between and including the CardioTraining and StrengthTraining columns.
DailyExercise[[#Totals],[StrengthTraining]]	The total of the StrengthTraining column. If there is no totals row, this will return #REF!
DailyExercise[[#Data],[CardioTraining]:[StrengthTraining]]	The data between and including the CardioTraining and StrengthTraining columns.
DailyExercise[[#Headers],[#Data],[StrengthTraining]]	The header and the data in the StrengthTraining column. If headers are turned off, this will return #REF!
DailyExercise[[#This Row],[CardioTraining]] or DailyExercise[@CardioTraining]	The cell at the intersection of the current row and the CardioTraining column. If used in the same row as a header or total row, this will return #VALUE! Note:  If you type the longer form of this structured reference, Excel replaces it with the shorter '@' form. They work the same.

See also

Enter a formula

Use names in formulas

Create or delete an Excel table