For example, to get the sum of all Apples sales, you want the sum of all the data in column B. You can now query your data using =query(). Now on the right toolbar, give the table a name. Select the area and choose Data –> Named ranges. To do it the query() way, first, give your table a name. My second favourite way is to give individual columns names. Here is my favourite way to simulate Microsoft Excel tables in Google Sheets: using query(). 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. The good news is that you can name ranges in Google Sheets and access them by name. They must have patented this design, because otherwise I can’t imagine why Google Sheets hasn’t implemented the same thing. This is the hardest part, and the part where Microsoft really made it easy to make tables. Formatted Google Sheets table Naming your Google Sheets table and accessing it via the name You can use one of the preset themes, or you can define your own (I defined my own below). Your table will then look nice and pretty! Choosing Alternating Colors in Google Sheets Then go to the Format menu and choose Alternating colors. This part is easy - it’s easy to add stripes to your Google Sheets table to make it look like an Excel table.Ĭhoose the area of your table.
This is a disadvantage compared to tables in Microsoft Excel.
One important note is that you can only have one filtered table per sheet. Your Google Sheets table is now filterable, like this. If you don’t have the toolbar, go to the menu and from “Data” choose “Create a filter”. Hit the filter button on the sidebar to make this table filterable To make this filterable, all you have to do is hit the filter button on the toolbar. Here’s the basic, unfiltered table, before any filtering is present. The first part of creating tables in Google Sheets is making a range you can filter. Naming your Google Sheets table and accessing it via the nameĬreating tables in Google Sheets you can filter.Creating tables in Google Sheets you can filter.