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.

Using Microsoft Excel to build a table

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

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

  1. Creating a filterable table
  2. Making it look pretty, and
  3. 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.

The raw data in Google Sheets we're going to use.
The raw data in Google Sheets to create a table

All you have to do is hit the filter button on the toolbar.

Create a filter button in Google Sheets (no data on page)
The Create a filter button

If you don't have the toolbar, go to the menu and from "Data" choose "Create a filter".

Create a filter in Google Sheets like a table
"Create a filter" button in Google Sheets

Your list is now filterable, like this.

Filtered table in Google Sheets
Table in Google Sheets with a filter.

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"))
How to query a Google Sheets table

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.