When you create a Google Sheets table by inserting custom tables or converting a range to a table, Google Sheets assigns a name to the table and each column header. This enables the use of structured table references in Google Sheets formulas.

Let’s consider a column in a structured table.

Assuming the table name is ‘Table1’ and the field label is ‘Amount’, here are the possible structured table references to the Amount:

Here are the possible structured table references to reference the table as a whole:

• Table1 – Refers to the whole table without the header and footer.
• Table1[#ALL] – Refers to the whole table, including the header and footer.
• =Table1[#Headers] – Refers to the table header only.
• =Table1[#TOTALS] – Refers to the table footer only.

Using Reference Operators

You may need to combine table references to specify table ranges in functions such as FILTER, SORT, SORTN, QUERY, etc., in Google Sheets. You can use the following reference operators to combine table references effectively:

Assume the data is in columns A to D, with the following field labels: Category, Subcategory, Item, Amount.

1. Colon (:) range operator: The colon operator is used to create a continuous range across adjacent columns.
   • =Table1[Category]:Table1[Item] – Refers to all cells in adjacent columns excluding the header and footer.
   • Table1[[#ALL],[Category]:[Item]] – Refers to all cells in adjacent columns, including the header and footer.
2. Comma (,) union operator: The comma operator combines multiple columns into a single set.
   • ={Table1[Category], Table1[Amount]} – Combines the “Category” and “Amount” columns. (Note: In some locales, you may need to use a backslash (\) instead of a comma. For example: ={Table1[Category] \ Table1[Amount]}). Refer to How to Change a Non-Regional Google Sheets Formula for details.)
   • ={Table1[[#ALL],[Category]], Table1[[#ALL],[Amount]]} – Combines the “Category” and “Amount” columns along with headers and footers.

Let’s see how to use structured table references to refer to a table or parts of a table in formulas in Google Sheets.

Creating a Table in Google Sheets

I have sample data in the range A1:D42 where A1:D1 contains the field labels Category, Subcategory, Item, and Amount, respectively.

You can copy my sample sheet by clicking the button below to follow the examples step by step.

Sample Sheet

Select the range A1:D43, ensuring it includes an empty row at the bottom.

1. Click on Format > Convert to Table.
2. Navigate to any cell within the table and click Format > Alternating Colors. Under “Styles,” check the Footer option and click Done. This will designate the last row as the footer row for adding totals.

This will create a table with the default table name, which is “Table1”. If you create another table, then the table name will be “Table2”.

Although you can rename the table by overwriting its name, we will use the default table name, which will be reflected in the structured table references in the formulas below.

Examples of Using Structured Table References in Google Sheets

Basic Examples

To sum the values in D2:D42 using explicit cell references, such as =SUM(D2:D42), you can use table and column names like =SUM(Table1[Amount]). In our table, enter this formula in cell D43 (this will help me explain how to extract this value using structured table references later).

To count the number of items that fall in the “Home Decor” subcategory, use the below COUNTIF:

=COUNTIF(Table1[Subcategory], "Home Decor")

Here, Table1 represents the table name, and [Subcategory] is the column specifier.

This adheres to the syntax COUNTIF(range, criterion) where the range is Table1[Subcategory] and the criterion is "Home Decor".

To sum the amount of the items that fall in the “Home Decor” subcategory, use SUMIF as follows:

=SUMIF(Table1[Subcategory], "Home Decor", Table1[Amount])

This follows the syntax SUMIF(range, criterion, [sum_range]) where the range is Table1[Subcategory], the criterion is "Home Decor", and the sum_range is Table1[Amount].

This way, we can use structured table references in Google Sheets formulas.

Examples of Reference Operators in Structured Tables

Let’s use the FILTER function to filter adjacent as well as distant columns using structured table references in Google Sheets.

Filter the range A2:D42 where D2:D42 is greater than 200:

In the following formula, we will replace A2:D42 and D2:D42 with corresponding structured table references.

=FILTER(Table1[Category]:Table1[Amount], Table1[Amount]>200)

The following FILTER formula filters the combined range A2:A42 and D2:D42, i.e., {A2:A42, D2:D42}, if D2:D42 is greater than 200.

=FILTER({Table1[Category], Table1[Amount]}, Table1[Amount]>200)

Filter the Entire Table Using Structured Table References

You can specify an entire table as follows:

Table1[#ALL]

This includes column headers and data. However, using this reference directly in certain formulas, such as FILTER, may cause issues.

For example, the following formula will return a “mismatched range error” since the number of rows in Table1[#ALL] and Table1[Amount] differ:

=FILTER(Table1[#ALL], Table1[Amount]>200)

Table1[#ALL] refers to A1:D42, whereas Table1[Amount] refers to D2:D42.

What you should do is use just the table name, i.e., Table1, which refers to the data in the table, i.e., A2:D42.

=FILTER(Table1, Table1[Amount]>200)

If you prefer Table1[#ALL], you need to adjust Table1[Amount] as follows:

=FILTER(Table1[#ALL], Table1[[#ALL],[Amount]]>200)

Escaping Square Brackets in Field Labels

When using structured table references in Google Sheets formulas, you should pay attention to the following:

If any of the field labels contain the special characters left bracket ([) or right bracket (]), escape them as follows:

If the field label is Item [USD], it should be referred to as Table1[Item '[USD']].

Resources

• Converting a Range to a Table and Vice Versa in Google Sheets
• Customizing Alternating Colors of a Table in Google Sheets
• Get the First or Last Row/Column in a New Google Sheets Table
• How to Add a Total Row to a Google Sheets Data Table
• 3-D Referencing Structured Data Tables in Google Sheets