Table in Google Sheets

What Is Table in Google Sheets?

Google Sheets has a vast variety of features, and one of the most useful ones is its ability to create tables. A table is an easy and efficient way to organize data, which can be useful for analysis. They are structured ranges consisting of rows and columns with a column header. Let us look at a sample table below.

Creating a table involves organizing the data you have at hand in a structured manner that is easy to understand. For example, below, we have a table containing the number of items sold at a supermarket over a week. Here, the first row contains the column headers.

You can further modify this table by formatting it, adding formulas(we have added them in Column D), and enabling filters.

How to Create a Table in Google Sheets?

In this section, we will learn how to create a table in Google Sheets.

The first step is to go to Google Sheets and add headers based on the data you have at hand. Here, we are entering the details of some students in a table.

Add the required headers, as shown below.

Step 1: The next step is to add the row data. Add the details as shown below in an organized manner.

Step 2: As you can see, we have filled up some columns and left a few blanks. We must use formulas in those. To find the total of Leo, enter the following formula in cell E2.

Step 3: To fill in all the values, drag the formula all the way up to cell E7.

Step 4: The next column will contain an expression. Here, we find the average of each student and grade accordingly.

Write the following expression in cell F2.

=IF(E2/3 > 80, “A”, IF(E2/3 > 50,”B”,”C”). Press Enter.

Here, we check if the average is greater than 80. If so, they are awarded A. Otherwise, we check if it is greater than 50. In this case, we award B; otherwise, it is Grade C.

Step 5: Now, drag the formula all the way down to cell F7 to get the students’ grades.

That’s it! Your table is ready for analysis and is structured for you to study at a glance. You can further apply filters, formatting etc., based on your requirements.

 In the next section, we will learn how to format the table to make it more readable, filtered, collapsible, and searchable.

Examples

Once you’ve created a table in Google Sheets, you can work wonders on them. Let us look at some interesting examples below.

Example #1 – Apply Alternating Row Colors

In Google Sheets, there is a table that shows the consumption of items during teatime in other European countries. Here, we are required to apply colors in alternating rows.

Step 1: Select the entire table. Also include the headers.

Step 2: Go to the Format option. Choose “Alternating Colors.”

Step 3: Usually, the headers are automatically recognized, as shown in the image. Else, check the box for headers.

Step 4: Choose one of Google Sheets’ default styles that you prefer. You can also customize it by manually selecting the color for the header and the alternating colors for the rows. Press Done.

The colors are applied to the table for alternating rows.

Example #2 – Using Filters and Sort for Enhanced Usability

Using filters helps you view specific rows in your table for analysis. It is straightforward to apply filters to Google Sheets. However, remember that you can apply filters to one table per sheet only. If you must filter other tables, they should be on separate Google sheets. Now, let us use filters on the table used in Example 1.

Step 1: Select your table’s headers, as shown below.

Step 2: Go to Data – Create a filter.

.Step 3: When you select the option, you can see the filter icon. It appears to the right of each header.

Step 4: Click the filter icon in any column. It is to see the options for sorting and filtering.

Step 5: Now, to apply sorting, let us sort the countries from A to Z. Select the option and look at the table. You can observe that the countries have been sorted from A to Z.

Example #3 – Conditional Formatting for Data Insights

Conditional formatting provides visual cues to help you make sense of your data. It also helps you identify trends and patterns in your data, making for easier data analysis. Let us apply conditional formatting to the above table based on certain conditions.

Step 1: Go to Format -Conditional Formatting.

Step 2: In the ‘Conditional format rules” pane on the right, you can choose from a host of format rules. Before that, select the range for which you want to apply the conditional formatting.

Step 3: We choose to use a different color and bold format for values greater than 50.

Step 4: Click on Done and check how the conditional formatting has been applied to the table.

Example #4 – Collapsible Table in Google Sheets

Not all tables would have few rows, as in the example above! Usually, we are left dealing with large datasets in Google Sheets. In such cases, you can collapse some rows or columns to make your table look more user-friendly.

For this, you group rows or columns to make your table collapsible.

The following are the steps to achieve the same.

Step 1: Select all rows except the headers.

Step 2: Right-click near the row number, and in the menu, click “View more row actions.” Now, you can use the option “Group rows.”

Step 3: Now, you will see a minus symbol in the top-left corner, which you can use to collapse the rows.

Your collapsible table is ready! To expand it again, click on the ‘+’.

Example #5 – Searchable Table in Google Sheets

Sometimes we frequently refer to a particular table for data analysis or calculations. In such cases, using a named range is an easy option for searching for data in that particular table.

Below are the steps for creating a searchable table using the named ranges property.

Step 1: Select the range of cells in your entire table.

Step 2: Now, go to Data -Named ranges.

Step 3: The “Named ranges” pane appears on the right. Here, type the name you want for your table and click “Done.”

Step 4: The table appears in the “Named ranges” sidebar. Similarly, you can also name a particular column.

To do this, you select the column and click “Add a range.”

Step 5: Now, select a name for the column. We have selected, as shown in the image below, after specifying the range.

Now, column F has a name, which can be used to reference it from other cells. Let’s demonstrate this with an example: Let’s find the total consumption of biscuits in all European countries.

Step 6: In an empty cell, type in the SUM function.

Step 7: Start typing the column name. A suggestion box appears as shown below. Select the name of your desired column.

Step 8: Now, select this column name and press Enter. You get the sum of the biscuit consumption across Europe. Thus, a named range for either a group of cells or table makes it easy to use it for calculations.

Important Points To Note

1. Compared to Excel, Google Sheets don’t have the advanced default functionality, but they can be added to Google Sheets with some manual intervention to get a Google Sheets table like Excel.
2. The table can be designed to suit your data and conditions in terms of layout and appearance.
3. You can easily apply features such as sorting, filtering, grouping rows and columns, and named ranges to search your tables.
4. You can record macros on your tables and automate tasks.
5. Google tables in sheets can be seamlessly integrated with other Google Workspace tools like Google Docs.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Table in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Table in Google Sheets. Here we learn how to use Table in Google Sheets with examples and points to remember. You can learn more from the following articles.

ACOS Function in Google Sheets

FLOOR.MATH function in Google Sheets

PV in Google Sheets