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.

If you switch from Excel to Google Sheets, you may encounter issues when using structured table references in Google Sheets formulas.

Let’s consider a column in a structured table.

In Excel, you can use structured table references to refer to the header of a column, the column range (data) including the header, and the data in the column range. However, in Google Sheets, you can only refer to the data in the column range.

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

So, in Google Sheets formulas, referring to the entire table as the range and a specific column as the criterion range will cause issues.

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

In my earlier tutorial, I already shared how to convert a range to a table in Google Sheets.

1. Select the range A1:D42
2. Click on Format > Convert to Table.

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

To sum the values in D2:D42 using explicit cell references like =SUM(D2:D42), you can use table and column names like =SUM(Table1[Amount]).

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.

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:

1. Colon (:) range operator: =Table1[Category]:Table1[Item] – Refers to all cells in adjacent columns, which is equivalent to the cell range A2:C42 as per our sample data. In standalone use, you can use the structured table reference as it is, whereas you should use explicit cell references like ={A2:C42}.
2. Comma (,) union operator: ={Table1[Category], Table1[Item]} – To combine two or more columns, use the comma separator (in some locales, you might want to use the backslash like ={Table1[Category] \ Table1[Item]}). Please refer to How to Change a Non-Regional Google Sheets Formula.

Examples

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: {""; Table1[Amount]}

Conclusion

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']].

Inserting tables is a new feature in Google Sheets, and Google may improve the functionality over time based on user feedback. I’ll try to update this tutorial accordingly.

Related: Customizing Alternating Colors of a Table in Google Sheets