You can work with large ranges of data in Google Sheets easier by turning them into Tables. Tables come with built-in abilities to help you access the data inside.
When you use a table in Google Sheets, the table and column headers have names. Notice the table name Table1 and column headers such as Campaign and Status in the Google Sheets table below. You can use these names to create Table References.
💡Table references are also called structured references. This term differentiates them from explicit cell references such as A1 or B1:E5.
Contents
Syntax
Table references appear as you type formulas, making it easier to use them.
Even through Sheets writes them for you, it would be best if you understood how to build them independently, so we’ll cover their syntax and explain how they work.
Table Column Reference
The table column reference syntax is:
=Table Name[Column Name]
This type of reference returns the contents of the Status column in a table, excluding the header. Using this syntax to refer to the table in the image above would be: =Table1[Status] which would return Previous, Current, Future, Current.
Table Reference
The syntax to return the contents of a table without the headers is simple:
=Table Name
Using this syntax, you can use the reference =Table1 to refer to A2:E5 which would be an array with all the data excluding the header row.
Entire Table Reference
To return an entire table, including the headers, add the #All modifier to the end as such:
=Table Name[#All]
Interestingly, there are far more modifiers for Excel tables. Google Sheets has chosen to keep it simple.
Usage
This syntax seems straightforward. However, these references don’t work like traditional cell references. If you use a table reference alone, it produces an error, as shown below.
The reason for this error is simple. Table references return more than one value, and Google Sheets doesn’t know what to do with those values. In this case, the table reference of Table1[Cost] refers to every value in the Cost column, which are $23,000, $1,000, $3,400, and $14,000.
Returning One Column
To return all the values inside the table reference, combine the reference with the ARRAYFORMULA function like this =ARRAYFORMULA(Table1[Cost]). This function tells Google Sheets to write every value from the array.
Now that you’ve seen how a table column reference works, let’s examine the other types of table references.
Returning The Entire Table
You can return the entire table with or without headers with these formulas:
Without headers
=ARRAYFORMULA(Table1[Cost])
With headers
=ARRAYFORMULA(Table1[#All])
Using Table References in Formulas
The key to using table references in formulas is knowing they return an array of values. This enables concise, easy-to-read formulas without cell ranges. You can use these formulas inside or outside of the table. First, let’s look at how you can use them inside a table.
Table References Inside A Table
Let’s add a column to calculate the Conversion Rate for each campaign. The conversion rate is Impressions divided by Conversions.
Formula used: =ARRAYFORMULA(Table1[Conversions]/Table1[Impressions])
The formula fills all the rows in the column with the Conversions divided by Impressions. Next, we will look at using table references in formulas outside of a table.
Table References Outside a Table
These references shine when refering to table data from outside of the table. They are easier and faster to use than traditional references. They also expand and contract when the table is updated.
Let’s say we want to calculate the overall conversion rates. To do this with regular cell references, you would need a formula like =SUM(E2:E5)/SUM(D2:D5). However, this formula would miss any rows added to the bottom of the table, and it would be difficult to see what data it refers to. Let’s use a table reference to make this formula better.
Formula Used =SUM(Table1[Conversions])/SUM(Table1[Impressions])
Video Tutorial
Conclusion
Table references empower you to work more efficiently in Google Sheets by using names instead of cell references. Accelerate your workflow by using them in your next spreadsheet.