How to Create a Google Sheets Pivot Table

Pivot tables in Google Sheets are a dynamic tool that can help you get essential data insights while also summarizing and analyzing information. By grouping and summarizing data, pivot tables provide a clear overview of patterns, trends, and relationships within your data. By the end of this tutorial, you’ll know how to make a pivot table in Google Sheets and how to determine if it’s right for your needs.

Steps to create a pivot table

When creating a pivot table in Google Sheets, you will follow these steps:

1. Open your Google Sheet.

2. Highlight your data.

3. Go to Insert > Pivot table.

4. Create your pivot table.

5. Drag and drop the categories to include in your pivot table.

6. Format your pivot table.

Now, let’s go further into each step.

1. Open Google Sheets.

Open your Google Sheets in your web browser. 

2. Select your data.

Next, check that your data is organized in columns and rows with headers in the first row. Each column should have a clear heading that describes the data it contains. 

Select the range of cells that contains your data. Ensure you’ve chosen all the columns that you want to include in your analysis. It's important to select the entire data range, including headers.

3. Access the ‘Pivot table’ option in the ‘Insert’ menu.

Once you have selected the data range selected, click on the Insert menu at the top, and choose Insert > Pivot table. 

4. Create your pivot table.

Once you click Insert > Pivot table, an editor will open. You can select ‘New Sheet’ or ‘Existing Sheet’ as the placement for your pivot table. You can also edit the data range included. In this example, we will opt to add our pivot table to the new sheet.

5. Configure your pivot table.

In the pivot table editor, you'll see several sections: Rows, Columns, Values, and Filter. Drag and drop the headers from your data on the right into the respective sections based on how you want to analyze and summarize your data.

• Rows: Choose the column headers you want to group and display as rows in the pivot table.

• Columns: Select the column headers you want to group and display as columns in the pivot table.

• Values: Choose the column headers containing the data intended for summarizing. You can select different aggregation functions, such as sum, count, average, or custom formulas.

• Filter: Add filters to narrow down the data included in the pivot table based on specific criteria.

6. Customize your pivot table.

Customize your pivot table to enhance its readability and insights. You can experiment with different configurations, such as rearranging row and column headers or changing the aggregation functions you use in the Value section. 

Limitations and alternatives

While pivot tables in Google Sheets are powerful tools for data analysis and summarization, it's important to be aware of their limitations. Consider the following factors:

• Limited to a single sheet: Google Sheets limits pivot tables to analyze data within a single sheet. If your data is spread across multiple sheets, you may need to consolidate it into a single sheet before creating a pivot table.

• Lack of advanced calculations: If you require statistics beyond what Google Sheets or Excel can provide, consider using dedicated statistical software or scripting languages like R or Java.

• Limited customization: If you require more advanced customization or visualizations, consider using data visualization tools like Looker Studio or other specialized software.

Learn more on Coursera

You can enhance your data analytics skills with top-rated courses and Professional Certificates on Coursera. With the Google Data Analytics Professional Certificate, you can build job-ready skills to enter an entry-level position in less than six months. This program covers topics like data visualization, exploration, and more. Upon completion, gain exclusive access to career resources like resume review, interview prep, and career support.