قالب وردپرس درنا توس
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?

Pivot tables allow you to analyze large amounts of data and narrow down large data sets to see the relationships between data points. Google Sheets uses pivot tables to aggregate your data to help understand all the information in your spreadsheet.

What are pivot tables?

Pivot tables are useful for analyzing huge amounts of data. If a normal spreadsheet uses only two axes (columns and rows), pivot tables help understand the information in your spreadsheet by summarizing the selected columns and rows of data. For example, a pivot table could be used to analyze the sales made by departments of a company for a given month in which all information is randomly entered into a record.

  Sample record in worksheets

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

  Sample Pivot Table

RELATED: The Best Google Sheets Add-Ons

Creating a Pivot Table

Start Chrome and open a spreadsheet in Google Sheets.

Next, select one of the cells you want to use in your pivot table. If you want to use everything in your dataset, you can click anywhere in the spreadsheet, you do not have to select each cell first.

 Select all cells to display in your pivot table.

Note: Each selected column must be headered to create a pivot table with these data points.

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


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

 If your pivot table does not open automatically, click

How to Edit a Pivot Table

You can add rows in the pivot table sheet. Columns, values, and filters to display your data. Sometimes Sheets offers suggestions based on the information you choose. Click on a suggestion or click on "Add" next to one of the other options.

 Choose between proposed pivot tables or create your own custom pivot table.

If You Click Any Option From the proposals, Sheets will automatically create your pivot table with the option you selected from the specified list.

If you prefer to 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. This means what they mean:

  • Rows: Adds all unique elements of a specific column from your dataset to your pivot table as row headers. They are always the first data points you see in light gray on the left side of your pivot table.
  • Columns: Adds selected data points (headers) in aggregated form for each column in your table that appears dark gray
  • Values: Adds the actual values ​​of each record to your record to sort in your pivot table.
  • Filter: Adds a filter to your spreadsheet to display only data points that meet certain criteria.

Next to "Rows," click Add and add the rows you want to show in your pivot table. In this example, we add division and subdivision.


. Next to Values ​​As, click Add and enter the values ​​you want to sort. We will use the sum of units sold and the average price per unit.

 Continue to

To change the sort order for each unit, click the drop-down menu under the "Merge to" heading. You can choose from Sum, Count, Average, Min, Max and other values.

 Click the drop-down menu with any value to select the values ​​you want.

After adding all rows, columns, values, and so on, you will see an easy-to-read pivot Table showing which division sold the most units and sold the average cost of all units.

 All information is presented in a well-organized manner, and totals of individual departments are listed below

CONNECTED: Importing an Excel document into Google Sheets [19659011] If you prefer to create your own formula, click "Add" next to the heading "Headings" and then "Calculated field". [19659011]   To add a custom summary, click

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

 Enter a custom formula for your data in the appropriate field a

If you want to add a filter to your spreadsheet, click Add next to the Filter heading.


Filter for your spreadsheet, select the values ​​that you want to display in your spreadsheet, or deselect them, and then click OK Apply filter.

 Select how you want to filter the data, and then click

. That's all there is to it. While this is just an introduction to using pivot tables, there are seemingly endless possibilities to use this feature, which few people know about.

Source link