So, you want to make tables in Google Sheets. Here’s how you do it — three ways you can get the same functionality.
Microsoft Excel has long (for over ten years) had a type of data called a “table”. Tables in Excel are cool because you can name them and then write really easy-to-parse formulas with them.
In this Microsoft Excel table, if I named it MyTable, instead of doing a formula like =sum(c4:b12), I could write a human formula like =sum(MyTable[Oranges]). See how easy that is to read??
Aside from that, Microsoft Excel tables
- Look nice, and
- Are easy to sort and filter and do things like remove duplicates.
But Google Sheets often presents similar functionality a little differently. So how do you make tables in Google Sheets? It depends on what you want to achieve.
There are three parts to making tables in Google Sheets:
Creating tables in Google Sheets you can filter
The first part of creating tables in Google Sheets is making a range you can filter.
Here’s the basic, unfiltered table, before any filtering is present.
To make this filterable, all you have to do is hit the filter button on the toolbar.
If you don’t have the toolbar, go to the menu and from “Data” choose “Create a filter”.
Your Google Sheets table is now filterable, like this.
One important note is that you can only have one filtered table per sheet. This is a disadvantage compared to tables in Microsoft Excel.
Making your Google Sheets table pretty
This part is easy — it’s easy to add stripes to your Google Sheets table to make it look like an Excel table.
Choose the area of your table. Then go to the Format menu and choose Alternating colors.
Your table will then look nice and pretty!
You can use one of the preset themes, or you can define your own (I defined my own below).
Naming your Google Sheets table and accessing it via the name
This is the hardest part, and the part where Microsoft really made it easy to make tables. They must have patented this design, because otherwise I can’t imagine why Google Sheets hasn’t implemented the same thing.
The good news is that you can name ranges in Google Sheets and access them by name.
The bad news is that even if you apply every “hack” in the book, the formulas will never look as clean as they do in Microsoft Excel.
Here is my favourite way to simulate Microsoft Excel tables in Google Sheets: using query().
My second favourite way is to give individual columns names.
To do it the query() way, first, give your table a name. Select the area and choose Data –> Named ranges.
Now on the right toolbar, give the table a name.
You can now query your data using =query(). For example, to get the sum of all Apples sales, you want the sum of all the data in column B.
=sum(query(MyTable,"select B"))If this is your first time seeing query() in Google Sheets, that’s a whole other topic!
Another easier way that you might also find useful is to give names to individual columns. That way, if your columns move around, your name still applies to just that column.
In that example, I gave the Oranges column the name MyTable_Oranges.
This means that to get the sum of all Oranges sales, I use the formula:
=sum(MyTable_Oranges)A tip to make both of these work very well: Make the whole sheet the table — trim off all other rows and columns.
