قالب وردپرس درنا توس
Home / Tips and Tricks / What are pivot tables in Google Sheets, and how do I use them

What are pivot tables in Google Sheets, and how do I use them

With pivot tables you can analyze large amounts of data and narrow down large amounts of data to see the relationships between data points. Google Sheets uses pivot tables to summarize your data, making it easier to understand all the information in the spreadsheet.

What are pivot tables?

Pivot tables are useful for analyzing massive amounts of data. If a regular spreadsheet uses only two axis columns and rows pivot tables, we help understand the information in the spreadsheet by summarizing all selected columns and rows of data. For example, a pivot table can be used to analyze sales submitted by divisions of a company for a given month, where all information is randomly entered into a dataset.

  Test data sheet in Ark

Creating a pivot table from The information in the above image shows a neatly formatted table with information from selected columns, sorted by division.

  Sample pivot table

RELATED: The best additions to Google Sheets

Create a pivot Table

Turn on Chrome and open a spreadsheet in Google Sheets.

Then select one of the cells you want to use in the pivot table. If you are going to use everything in your dataset, you can click anywhere on the spreadsheet, you don't have to choose each cell first.

 Select all cells you want displayed in your pivot table

Note! Each column selected must have a header associated with it to create a pivot table with these data points.

On the menu bar at the top of the page, click "Data" and then click "Pivot Table."


in the menu bar. If the new table does not open automatically, click "Pivot Table" at the bottom of the worksheet.

 If your pivot table does not open automatically, click

To edit a pivot table

From the pivot table, the sidebar can add rows, columns, values, and filters to display your data. Sometimes Sheets offers suggestions based on the information you chose. Click on a suggestion or click on "Add" next to any of the other options below.

 Choose from recommended pivot tables or create your own pivot table.

When you click on any of the suggestions, Ark automatically builds your pivot table with the option you selected from the list.

If you would rather customize a pivot table to your own needs, click one of the "Add" buttons next to the four options below. Each option has a different purpose, here is what they mean:

  • Rows: Adds all unique objects in a particular column from the dataset to your turntable as row headers. They are always the first data points you see in your turntable in light gray on the left.
  • Columns: Adds selected data points (headers) in aggregated form for each column in your table, set in dark gray at the top of the table.
  • Values: Adds the actual values ​​of each heading from your dataset to sort by your pivot table.
  • Filter: Adds a filter to your table to show only data points that meet specific criteria.

Click "Add" next to Rows and add the rows you want to display in your pivot table. For this example, we add division and subdivision.


Next, click "Add" next to Values ​​as and insert the values ​​that you want to sort the information. We use the sum of the number of units sold and the average unit price.

 Next to

To change the sorting of each device, click on the drop-down menu, which is located under the heading "Merge by." You can choose between the sum, count, average, min, max, among others listed below.

 Click on the drop-down menu with any value to choose how you want them to appear in the table.

After adding all rows, columns, values, etc., it is an easily readable pivot table describing which part that sold most units and the average cost of all units

 All information is displayed carefully, with each division's totals listed below

RELATED: How to import an Excel document into Google Sheets

If you prefer to create your own formula , click "Add" next to Values ​​as the heading and then click "Calculated field". [19659011]   To add a custom summary, click

In the new value field, enter a formula that best summarizes data in your pivot table.

 In the box, enter a custom formula for your data

To add a filter to your table, click "Add", next to the heading Heading.

 Click on

When you add a filter to your table, select or deselect the values ​​you want to display on your table, and then click "OK" to apply the filter.

 Choose how you want to filter the data and then click

It's all there is. Although this is just an introduction to pivot tables, there is a seemingly endless amount of opportunity to utilize this feature that not many people know much about.

Source link