So, you want to make a "table" in Google Sheets. Here's how you do it!
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)2, I could do
=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.
So how do you make a table in Google Sheets?
There are three parts to making tables in Google Sheets:
- Creating a filterable table
- Making it look pretty, and
- Giving it a name so you can manipulate it easily.
Creating a table in Google Sheets you can filter
The first part of creating a table in Google Sheets is making a range you can filter.
Here's the basic, unfiltered table.
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 list 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
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.
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
This means that to get the sum of all Oranges sales, I use the formula:
A tip to make both of these work very well: Make the whole sheet the table — trim off all other rows and columns.