Structured reference syntax examples

In this video, we'll look at the syntax used in a variety of structured references.

Structured references are used to refer to Excel tables in formulas. The syntax for structured references allows you to precisely target different parts of the table.

Let's walk through some examples.

All these formulas refer to Table1 at the left. Since the formulas are outside the table, they all include the table name.

The formulas in column I are actually entered as text. In each case, I'll temporarily convert to a real formula so you can see exactly what range is being targeted.

The most basic syntax is the table name only. This refers to the table data.

The table name with the #Data specifier also refers to all of the table data. The #Data specifier is seldom used this way since it's not necessary.

To refer to all headers, use the #Headers specifier.

To target the entire table, including data, headers, and total row, use the #All specifier.

To refer to a specific header, you'll need a double set of square brackets. Inside the outer brackets, use the #Headers specifier and the column name, each in brackets separated by a comma.

To target a specific column, use the table name, with the column name in square brackets.

To refer to the entire column, including headers, and total row, use the #All specifier.

To reference multiple columns, use a double set of brackets. Wrap each column name in brackets, and separate with a colon.

The @ symbol inside a table means "this row".

You can combine this with a column name to reference a particular cell in the current row.

To reference the Total row, use the #Totals specifier. To reference a specific total, use a double set of brackets, and the column name, just like the headers row.

Finally, let me mention a few things you should keep in mind with respect to structured references.

First, structured references always evaluate to a range of cells, usually with multiple values. If I use F9 to evaluate the price column, I'll see an array that contains all of the prices.

Second, it's often easy to point and click and let Excel complete the structured reference. For example, if I type an equal sign, and click a cell in the header row, Excel will enter the required syntax.

Alternately, as you start typing a table name in a formula, Excel will match the name. 

You can use the arrow keys to select table and column names, and the tab key to accept a value.