Don’t forget to share this post

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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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.
How to Make a Table in Google Sheets Open Sheets
How to Make a Table in Google Sheets - Open Sheets
  1. 2. Add column headers.
How to Make a Table in Google Sheets Add Headers
How to Make a Table in Google Sheets - Add Headers
  1. 3. Add row data.
How to Make a Table in Google Sheets Add Row Data
How to Make a Table in Google Sheets - 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.
How to Make a Table in Google Sheets Select Range of Cells
How to Make a Table in Google Sheets - Select Range of Cells
  1. 2. Go to Format > Alternating colors.
How to Make a Table in Google Sheets Format Alternating Colors
How to Make a Table in Google Sheets - Format > Alternating Colors
  1. 3. Google Sheets automatically recognizes the headers, marking the “Header” checkbox. If it didn’t, check the box yourself.
How to Make a Table in Google Sheets Header Checkbox
How to Make a Table in Google Sheets - Header Checkbox
  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.
How to Make a Table in Google Sheets Default Custom Styles
How to Make a Table in Google Sheets - Default & Custom Styles
  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 Table in Google Sheets Remove Alternating Colors
How to Make a Table in Google Sheets - Remove Alternating Colors

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.
How to Make a Table in Google Sheets Select Header
How to Make a Table in Google Sheets - Select Header
  1. 2. Go to Data > Create a filter.
How to Make a Table in Google Sheets Create a Filter
How to Make a Table in Google Sheets - Create a Filter
  1. 3. As you can see below, the filter icon now appears to the right of each header.
How to Make a Table in Google Sheets Filter Icons
How to Make a Table in Google Sheets - Filter Icons
  1. 4. Click the filter icon in any column to see the options for sorting and filtering.
How to Make a Table in Google Sheets Sort Filter Options
How to Make a Table in Google Sheets - Sort & Filter Options
How to Create and Use a Pivot Table in Google Sheets
How to Create and Use a Pivot Table in Google Sheets?

Pivot tables can help you summarize large data sets in spreadsheets. Here's how to create a pivot table in Google Sheets.

READ MORE

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.
How to Make a Table in Google Sheets Select Rows
How to Make a Table in Google Sheets - Select Rows
  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.
How to Make a Table in Google Sheets Group Rows
How to Make a Table in Google Sheets - Group Rows
  1. 3. Click the minus symbol in the top-left corner to collapse the rows.
How to Make a Table in Google Sheets Click Minus Symbol
How to Make a Table in Google Sheets - Click Minus Symbol
  1. 4. That’s it. To expand the table, simply click the plus symbol in the top-left corner.
How to Make a Table in Google Sheets Collapsed Rows
How to Make a Table in Google Sheets - Collapsed Rows

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.
How to Make a Table in Google Sheets Select Columns
How to Make a Table in Google Sheets - Select Columns
  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.
How to Make a Table in Google Sheets Group Columns
How to Make a Table in Google Sheets - Group Columns
  1. 3. Click the minus symbol above the first grouped column.
How to Make a Table in Google Sheets Click Minus Symbol 2
How to Make a Table in Google Sheets - Click Minus Symbol
  1. 4. That’s it. To expand the columns, click the plus symbol shown below.
How to Make a Table in Google Sheets Collapsed Columns
How to Make a Table in Google Sheets - Collapsed Columns
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
How to Make a Table in Google Sheets Select Table
How to Make a Table in Google Sheets - Select Table
  1. 2. Go to Data > Named ranges.
How to Make a Table in Google Sheets Data Named Ranges
How to Make a Table in Google Sheets - Data > Named Ranges
  1. 3. Type in the name you want to use for your table and click “Done”.
How to Make a Table in Google Sheets Name Your Table
How to Make a Table in Google Sheets - Name Your Table
  1. 4. The table now appears in the “Named ranges” sidebar.
How to Make a Table in Google Sheets Named Table
How to Make a Table in Google Sheets - Named Table
  1. 5. To name a column in your table, select the column and click “Add a range”.
How to Make a Table in Google Sheets Add a Range
How to Make a Table in Google Sheets - 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.
How to Make a Table in Google Sheets Name Column Range
How to Make a Table in Google Sheets - Name Column Range
  1. 7. In an empty cell, type in the SUM function.
How to Make a Table in Google Sheets Add Function
How to Make a Table in Google Sheets - Add 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.
How to Make a Table in Google Sheets Type Named Range
How to Make a Table in Google Sheets - Type Named Range
  1. 9. Close the parenthesis and press ‘Enter’.
How to Make a Table in Google Sheets Select Named Range
How to Make a Table in Google Sheets - Select Named Range
  1. 10. That’s it. You have the sum of all purchases.
How to Make a Table in Google Sheets Sum of Purchases
How to Make a Table in Google Sheets - Sum of Purchases

How to Automate Your Data on Top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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.

Layer Google Sheets Add On
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 14 2023, Updated Jan 15 2023