How to use complex structured references, table formulas, in Excel.

If you don't already understand how structured references work or what they do, please read our tutorial on that: using formulas with tables in Excel.

**Note**: the examples below will show you how to make the references but won't necessarily show you useful real world examples. This is in an effort to teach the main concepts, which are, themselves, quite tricky. If you want useful examples, simply wrap the ones in this tutorial in a SUM or AVERAGE function to see results.

All examples are based on this table, which is named **SalesData**:

(Download the attached file to follow along with these examples.)

Referencing Multiple Columns at Once

Referencing a Section of a Column

Reference a Section of the Table Across Multiple Columns

```
```=SalesData[Sales],SalesData[Goal]

You simply reference each column separately and put a comma between the references.

```
```=SalesData[[Goal]:[% of Goal]]

Two things here:

We reference two columns but separate them only with a colon **:**

Since we reference two columns next to each other, we must put another pair of brackets around them, which is why you see an extra one before Goal and an extra one at the end after % of Goal.

```
```=SalesData[[Sales]:[Goal]] SalesData[[Goal]:[% of Goal]]

This is the most complex method. Intersection is where two range references overlap. In this example we would end up referencing the Goal column because that is the only column that is listed in both table column references.

There are two column range references here: **SalesData[[Sales]:[Goal]]** and **SalesData[[Goal]:[% of Goal]]** and they are separated using a **single space** and nothing more; that single space is important and this won't work without it.

The syntax of the column range references is the same as the Contiguous Column example above it.

You can reference particular sections of a column using the item specifiers:

#All, #Data, #Headers, and #Totals (explained in the tutorial that is linked to at the top of this tutorial)

```
```=SalesData[[#Totals],[Sales]]

This references the Totals row in the Sales column of the SalesData table.

Note that there is a comma between the item specifier and the column name.

Note also that there is an extra set of brackets surrounding the reference for the table; an extra bracket before #Totals and an extra one after Sales.

Using this method you can reference any part of the column by changing Totals to any of the item specifiers, such as Headers or All.

This combines what was learned in the Referencing Multiple Columns at Once section and the Referencing a Section of a Column section.

```
```=SalesData[[#Totals],[Sales]:[Goal]]

There is the #Totals reference at the start and then a comma and then, instead of a single column reference, there is a multi-column reference.

If the two columns Sales and Goal were not touching, we would have to adjust the formula to look like this:

```
```=SalesData[[#Totals],[Sales]],SalesData[[#Totals],[Goal]]

Though this looks complex, it merely follows the syntax rules laid out in the example above for Reference Multiple Separate Columns combined with referencing the section of a column.

These formulas, structured reference formulas for tables, can be quite tricky and confusing. This tutorial assumes that you have a basic understanding of how they work and, if you don't, then you should visit the tutorial that is linked to at the top of this tutorial so you know what is going on.

You should play around with these formulas and practice them until you get the hang of the syntax. Nothing is really difficult, it's just that the syntax is different from the regular Excel syntax and that takes time to get used to.

Make sure to download the sample file for this tutorial so you can try everything out in Excel. To make the formulas actually work and return a value, wrap them in a SUM or AVERAGE function.

Similar Content on TeachExcel

Easier Table References in Excel

Tutorial: Some simple tips to make table references easier in Excel, even when you don't remember t...

Tutorial: Some simple tips to make table references easier in Excel, even when you don't remember t...

Convert Column Number to Letter Using a Formula in Excel

Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...

Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...

5 Tips for Evaluating Complex Formulas in Excel

Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...

Tutorial: 5 simple tips to evaluate any complex formula in Excel! These tips combine to give you th...

Regular Expression Search Formula in Excel - Regex

Tutorial: Part 1 - Regular Expression Match Formula in Excel (No VBA) Part 2 - Complete Excel Reg...

Tutorial: Part 1 - Regular Expression Match Formula in Excel (No VBA) Part 2 - Complete Excel Reg...

Hide Formulas in Excel

Tutorial: How to hide a formula in Excel so that a user cannot see it, select it, or change it - th...

Tutorial: How to hide a formula in Excel so that a user cannot see it, select it, or change it - th...

Remove Table Format in Excel

Tutorial: Convert a table back into a regular set of cells. This removes any automated table fea...

Tutorial: Convert a table back into a regular set of cells. This removes any automated table fea...