5 Easy Ways To Create A Pivot Table In Google Sheets (With Example)

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : May 21, 2021 | Review Date : May 22, 2021

Google Sheet, Spread Sheet

You know, back to 5 years, excel was top of data sheet management in data analysis industry. But after several years, professionals believe that open source and cloud base applications will be next generation and that they will be easy to manage with cross device and teams world wide. Then data management industry will drip by google sheets and doing amazing job with data manipulation.

Data is information, it will help to analyze your organization’s existing behavior and what kind of decision needs to get in feature. Data is the primary getaway to build relationships with stockholders and organizations. To take organization decisions, stockholders are using a large set of data. You already know that google sheets help to manipulate your unstructured data to Machine-readable (or structured) data.

About This Article
In this article, we cover basic usage of pivot in google sheets with practical examples and frequency issues you have to face with doing in real world situations.

What is the pivot table in google sheets?

Pivot table in google sheets, use for analysis and summarize large set structural data to take data driven decisions. Your pivot table can be used for sorting, grouping, graphing, filtering, and calculation based on given range.

This is the basic step to create a pivot table in google sheets.

Open a spreadsheet in Google Sheets.
Select the data range which you need to analyze data using pivot. Make sure to select the column with the header.
On the top of navigation click Data (middle of Insert and Data navigation link) > Select Pivot table.
Then open pivot table dialog box, Under Insert to, choose where to add your pivot table (create new sheet or insert into existing sheet).
Click the Create button.
Then you can manually add row or column or filter to manipulate data to take optimum summarize of given data,

Feature of pivot table editor

Click anywhere in the pivot table in google sheets to open pivot table edit & it will open right-side in spreadsheet. In the Pivot editor.

Pivot cell range: this is representing the datasheet cell range which you select for the pivot table. You can change the given range using this field.
Suggested: google sheet analysis your given data range and suggest some pre-configure combinations based on pivot table data. You can easily import that and customize.
Rows: this is the base of the pivot table. Summary of data will combinate based on this field. You can click the Add button and select which column needs to be Row in the given data range for the pivot table. This Row field will distinctly list if the column holds many duplicate values. You can order data and sort data by given argument. You can insert multiple rows in pivot table, and it can be change grouping order (draggable).
Column: this is a horizontal column that organizes aggregation of column value which you give using the Add button. You can order data and sort data by given argument.
Value: this field will be used to organize values based on Row and Column field choice. If you select one data column as value, you’re telling the Pivot Table to summarize that column data that is based on Row and Column in the pivot table.
Filter: this is the import field in pivot table. Even if you summarize data using pivot tables, you have to return a database based on a special situation. Filter can be used for search and filter data base on given data. The Interesting thing is this filter can be used based on value and based on condition. Base on condition will load all features which already exist in conditional formatting in google sheet.

Still don’t you have any idea about how to use conditional formatting In google sheets, we highly recommend reading Conditional Formatting in Google Sheets (6 Useful Examples) Article.

About example

Let’s take shoe sales inventory in a sportswear showroom. This store company was started in 2018, they sell 6 different types of sport shoes brand, also working 5 well experienced sales rep persons. their customers visit the showroom & buy via online websites too. Both of the above order types are handled by sales reps. that company managers are looking to analyze all selling and sales reps performance from 2018 and make some business decisions.

They need to summarize the following type of data.

Summarize counts of selling shoes base on sales reps & brand
Summarize selling performance based on each year & counts of selling shoes based on sales reps & brand
Filter 2019 online selling record which is sold by sales rep in each brand based on summarize in pivot table.

Download Saample Sheet

To Import the example datasheet into your spreadsheet,

Open a spreadsheet in Google Sheets.
On the top of navigation click File > Import (import datasheet)

Let’s check how doing following example with this sample shoe sales inventory sheets.

Let’s start creating a pivot table in google sheets based on the above example. It will be easy to understand how to use it in the real world.

Keep in mind that your datasheet doesn't hold. duplicate and empty cell. Also, when you pivot the table with date, make sure that it needs to be the same date format.

Step 01 (base on example sheet)

Create a pivot table.

Select all data cells (Ctrl + A) in the sales data sheet with row headers (A1:H1001).
On the top of navigation click Data > Select Pivot table.
Then open the pivot table dialog box. Check the new sheet radio button, insert to pivot table.

select a new sheet to get a clear visualization of your pivot table summary. You can also create a pivot table in the existing data sheet. It will generate an existing data sheet.

Step 02 (base on example sheet)

Insert rows into the pivot table in google sheets.

To take sales count based on sales raps, we need to group all selling databases on sales reps. For that, I need to initialize the sales rep as a row in the pivot table.

Click Add button in Rows section & select Sales rep option (actually, its row header in data sheet) .

You can organize as ascending or descending in the sales rep. Show total (Grand total in pivot table) represents the total count (sum) of a given column value.

Step 03 (base on example sheet)

Insert column into pivot table in google sheets.

Then need or organize brand names into columns. As mentioned on the example sheet, there are 6 types of brand & all brand orders handled by sales reps.

Click Add button in Column section & select Brand option (actually, its row header in data sheet).

Same as rows you can organize ascending or descending brand columns. In Last column hold Grand total in total brand selling count (or sum) in each row (sales rep).

Step 04 (base on example sheet)

Insert & Sort by value Pivot table in google sheets.

Now you need to get a sales count in each brand and need a group based on sales rep. We need to get each row count based on the sales rep.

Click Add button in Value section & select Id field option (actually, its row header in data sheet).
Then select the COUNT option in summary by dropdown to get the count of each row.

Now you can get Sales rep sales performance in each brand. Also, you can get the total selling of each brand and each sales rep.

Step 05 (base on example sheet)

Group by Month / Year in Pivot Table in Google Sheets.

Next, we need to perform sales rep selling performance groups each year. Group mean it should be a row,

Click Add button in rows section & select Sale date field option in data field dropdown.
Then sales count will appear on the pivot table in each Calendar data which is given data range.
Next select any cell value ref in Sale data in the pivot table and right click. Then Select Create pivot date group > select Year in date grouping dropdown.

Awesome now sales rep, sold orders will group by each year. Please note that your date in the data sheet needs to be the same date format and doesn’t contain any empty cell.

Filter Pivot Table in Google Sheets.

Filter is used for returning the rows in a range that meet specified criteria. Make sure that your filtered result can only show subset of given data, To create Filter, Click Add button in Filter section & select field which need to filter pivot table date

Feature of Filter in Pivot Table

Filter by values :
This is a searchable multi select dropdown that organizes by selected filter value. for example If you select the date column as filter it will drop all date cell values and distinct automatically.
Filter by condition :
Instead of a value base filter, you can select predefined conditions or write your own argument for the return subset of given data. Conditional formatting is a predefined set of arguments that are used for running IF functional base statements.

Still don’t you have any idea about how to use conditional formatting in google sheets, we highly recommend reading Conditional Formatting in Google Sheets (6 Useful Examples) Article.

Let’s continue with an example.

Step 06 (base on example sheet)

Filter multiple conditions in the pivot table in google sheets.

The pivot table needs to filter 2019 online selling records which are sold by sales reps in each brand. To filter this argument, we have to combine 2 data values in given data.

Sale date
Sale Type

The first step to do, Filter 2019 selling date related inventory data.

Click Add button in Filter section & select Sale date field option (actually, its row header in data sheet).
Click Status dropdown in filter dialog box.
Then under Filter By Value Toggle, click clear and clean the already selected value (by default it checks all values in filter range).
Next type 2019 in search text field and filter 2019-year related dates. Then click select all links. It selects (checked) all 2019 sale dates which you are given range.
Then click the ok button and your pivot table data will filter only 2019 Sale date.

The second step to do, show only Online sold shoes in 2019 filtered in the pivot table.

Click Add button in Filter section & select Sale Type field option.
Click Status dropdown.
Instead of Filter By Value Toggle, click Filter By Condition Toggle, then select Is Equal to in conditional dropdown.
Insert online value in value or formula text field.
Then click the button .

That’s it. I hope this article will help to get a basic idea of pivot table usage of google sheets and how to summarize given data.

Conclusion

Google sheet continuously grows every day with new features. Pivot table is one of smart features to summarize data to take organization decisions. amazing job with data manipulation. When you are working with pivot table you may need to remove duplicate data and date and curranty need to be same format.