How to Make a Table in Google Sheets

Tables are a fundamental component of any spreadsheet, allowing you to structure and relate data. While the basic concept of a table is common to all, the specifics vary depending on the spreadsheet software you choose. If you're using Excel tables, you may be missing some of the built-in functionality from Google Sheets’ tables. However, this functionality isn’t missing; it just works differently.

In this post, you will learn how to create a simple table in Google Sheets. Once you have created a basic table, you will learn how to format it so that it’s clearly identifiable as a table. You will also learn how to add more functionality to this table by making it filtered, collapsible, and searchable.

How to Create a Table in Google Sheets?

First, let’s see how to create a simple table in Google Sheets.

1. 1. Go to Google Sheets.

1. 2. Add column headers.

1. 3. Add row data.

That’s it. You have a simple Google Sheets table. In the next section, you’ll learn how to format this table to make it prettier, filtered, collapsible, and searchable.

How to Format Your Table in Google Sheets?

Now that you have a simple table, you can format it to change its appearance and add functionality.

How to Apply Alternating Row Colors?

1. 1. Select the range of cells containing your table, including the headers.

1. 2. Go to Format > Alternating colors.

1. 3. Google Sheets automatically recognizes the headers, marking the “Header” checkbox. If it didn’t, check the box yourself.

1. 4. You can choose one of Google Sheets’ default styles or create a custom one by manually selecting the color for the header and the alternating colors for the rows.

1. 5. Once you’re happy with the appearance of your table, click “Done”. To remove the alternating colors, click the button marked in red, as shown below.

How to Make a Filtered Table in Google Sheets?

It’s important to note that Google Sheets only allows one filtered table per sheet. If you’re using multiple filtered tables in one Google Sheets file, make sure each one is in a separate sheet or tab. Follow the steps below to add filters to each column, so you can filter and sort your data with a couple of clicks.

1. 1. Select any of your table’s headers, as shown below.

1. 2. Go to Data > Create a filter.

1. 3. As you can see below, the filter icon now appears to the right of each header.

1. 4. Click the filter icon in any column to see the options for sorting and filtering.

How to Make a Collapsible Table in Google Sheets?

If you’re dealing with a large table, you may want to be able to collapse the rows or some of the columns. While you can do this by hiding rows or columns, it can get tricky and lead to mistakes. Instead, you can group rows or columns to make your table collapsible.

Group Table Rows

Follow the steps below to group the table’s rows.

1. 1. Select all rows except the headers.

1. 2. Right-click in the row number area to get the menu and click “View more row actions”. Select the option to group rows.

1. 3. Click the minus symbol in the top-left corner to collapse the rows.

1. 4. That’s it. To expand the table, simply click the plus symbol in the top-left corner.

Group Table Columns

Follow the steps below to group table columns so you can collapse and expand them as needed.

1. 1. Select the columns you want to be able to collapse.

1. 2. Right-click in the area with the column letters to get the menu, and click “View more column actions”. Select the option to group columns.

1. 3. Click the minus symbol above the first grouped column.

1. 4. That’s it. To expand the columns, click the plus symbol shown below.

How to Make a Searchable Table in Google Sheets?

If you frequently refer to data in your tables in other tables or formulas, named ranges will save you a lot of time. You can name the entire range, as well as individual columns. This allows you to search for and manipulate data by referring to the range name or the column name.

Below, you have step-by-step instructions on how to name your whole table, as well as specific columns within that table. Finally, you have examples of how to use named ranges within Google Sheets functions.

1. 1. Select the range of cells containing your table.

1. 2. Go to Data > Named ranges.

1. 3. Type in the name you want to use for your table and click “Done”.

1. 4. The table now appears in the “Named ranges” sidebar.

1. 5. To name a column in your table, select the column and click “Add a range”.

1. 6. Now that the column has a name, I can reference it from other cells. For example, to find the total sum of purchases.

1. 7. In an empty cell, type in the SUM function.

1. 8. After the parenthesis, start typing the name of the column. You will see a suggestion box like the one below. Click or hit the Tab key to select the column.

1. 9. Close the parenthesis and press ‘Enter’.

1. 10. That’s it. You have the sum of all purchases.

Conclusion

While Google Sheets don’t have the default functionality of Excel tables, you can add it with a few simple modifications. In terms of layout and appearance, you can choose from the selection offered by Google Sheets or design them yourself. You can also transform your table by adding more advanced features, such as sorting and filtering, grouping rows and columns, and using named ranges to search your tables more easily.

You now know how to create a simple table in Google Sheets. You also know how to make this table look prettier with distinct headers and alternating row colors. Finally, you know how to make your tables more advanced by using filters to sort and filter your data by column, grouping to make rows or columns collapsible, and naming ranges to make your table searchable. Check out our article on how to create a pivot table in Google Sheets.