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.

Tables in Google Sheets — Microsoft Excel Table
A Microsoft Excel table

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

  1. Look nice, and
  2. 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.

Tables in Google Sheets — Base data range
Unfiltered table in Google Sheets

To make this filterable, all you have to do is hit the filter button on the toolbar.

Toolbar filter button in Google Sheets table
Hit the filter button on the sidebar to make this table filterable

If you don’t have the toolbar, go to the menu and from “Data” choose “Create a filter”.

Create a Filter button in Google sheets
Create a filter

Your Google Sheets table is now filterable, like this.

Filtered table in google sheets
Filtered table in Google Sheets

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.

Choosing alternating colours to format a Google Sheets table
Choosing Alternating Colors in Google Sheets

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).

Formatted Table in Google Sheets with alternating colours
Formatted Google Sheets table

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.

Creating named ranges in Google Sheets
Give your table a name as a Named Range

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.

Giving a name to one column in a Google Sheets table
Named column in Google Sheets table

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.

Similar Posts

Leave a Reply

Your email address will not be published.