Structured reference in Excel tables

Easily created

To add structured references to your formula, you simply select the table cells you want to refer to. The knowledge of a special syntax is not required.

Resilient and automatically updated

When you rename a column, references are automatically updated with the new name, and a formula does not break. Moreover, as you add new rows to the table, they are immediately included in the existing references, and the formulas calculate the full set of data.

So, whatever manipulations you do with your Excel tables, you don't have to worry about updating the structured references.

Can be used inside and outside a table

Structured references can be used in formulas both inside and outside an Excel table, which makes locating tables in large workbooks easier.

Formula auto-fill (calculated columns)

To perform the same calculation in each table row, it is enough to enter a formula in just one cell. All other cells in that column are filled automatically.

Range operator (colon)

As with normal range references, you use a colon (:) to refer to two or more adjacent columns in a table.

For example, the below formula adds up the numbers in all the columns between South and East.

=SUM(Regions[[South]:[East]])

Union operator (comma)

To refer to non-adjacent columns, separate the column specifiers with commas.

For instance, here's how you can sum the data rows in the South and West columns.

=SUM(Regions[South], Regions[West])

Intersection operator (space)

It is used to refer to a cell at the intersection of a specific row and column.

For example, to return a value at the intersection of the Total row and West column, use this reference:

=Regions[#Totals] Regions[[#All],[West]]

Please notice that the [#All] specifier is required in this case because the column specifier does not include the total row. Without it, the formula would return #NULL!.

1. Enclose specifiers in brackets

All column and special item specifiers must be enclosed in [square brackets].

A specifier that contains other specifiers should be wrapped in outer brackets. For example, Regions[[South]:[East]].

2. Separate inner specifiers with commas

If a specifier contains two or more inner specifiers, those inner specifiers need to be separated with commas.

For example, to return the header of the South column, you type a comma in between [#Headers] and [South] and enclose this whole construction in an additional set of brackets:

=Regions[[#Headers],[South]]

3. Do not use quotation marks around column headers

In table references, column headers don't require quotes whether they are text, numbers or dates.

4. Use a single quotation mark for some special characters in column headers

In structured references, some characters such as left and right brackets, pound sign (#) and single quotation mark (') have special meaning. If any of the above characters is included in a column header, a single quotation mark needs to be used before that character in a column specifier.

For example, for the column header "Item #", the specifier is [Item '#].

5. Use spaces to make structured references more readable

To improve the readability of your table references, you can insert spaces in-between specifiers. Normally, it is considered a good practice to use spaces after commas. For example:

=AVERAGE(Regions[South], Regions[West], Regions[North])

Relative column reference (default)

table[column]

Absolute column reference

table[[column]:[column]]

To make an absolute reference for the current row, prefix the column identifier by the @ symbol:

table[@[column]:[column]]

To see how relative and absolute table references work in practice, please consider the following example.

Supposing you want to add up the sales numbers for a specific product for 3 months. For this, we enter the target product name in some cell (F2 in our case) and use the SUMIF function to get the total of Jan sales:

=SUMIF(Sales[Item], $F$2, Sales[Jan])

The problem is that when we drag the formula to the right to calculate the totals for the other two months, the [Item] reference changes, and the formula breaks:

To fix this, make the [Item] reference absolute, but keep [Jan] relative:

=SUMIF(Sales[[Item]:[Item]], $F$2, Sales[Jan])

Now, you can drag the modified formula to other columns and it works perfectly:

Absolute range reference (default)

table[[column1]:[column2]]

Relative range reference

table[column1]:table[column2]

To refer to the current row inside the table, use the @ symbol:

[@column1]:[@column2]

For example, the below formula with an absolute structured reference adds up the numbers in the current row of the Jan and Feb columns. When copied to another column, it will still sum Jan and Feb.

=SUM(Sales[@[Jan]:[Feb]])

In case you want the reference to change based on a relative position of the column where the formula is copied, make it relative:

=SUM(Sales[@Jan]:Sales[@Feb])

Please notice the formula transformation in column F (the table name is omitted because the formula is inside the table):

That's how you make table references in Excel. To have a closer look at the examples discussed in this tutorial, feel free to download our sample workbook to Excel Structured Reference. I thank you for reading and hope to see you on our blog next week.