Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A pivot table report in Google sheets is a dynamic tool you can use to interpret the data in your spreadsheet without ever having to enter a formula. Pivot table reports give you a quick way of analyzing a large amount of spread sheet data. Let's look at how to create and use a pivot table report in a Google Sheet spreadsheet. I'll show you how it works in this solar products sales spreadsheet. You'll notice that I've organized my data into columns. It's best to organize your data into columns for pivot tables because column headers will become the fields that you add into your pivot table report.
Once you've opened your spreadsheet, you don't even have to pick a range of cells to add to your pivot table. Google Sheets will automatically detect a range of cells for your pivot table. You can always edit the data range at any point. Start by clicking the data menu and selecting pivot table report. A new sheet named Pivot Table 1 will open in your spreadsheet document. With a report editor tool available on the right hand side of the screen. The sheet that is displayed contains an empty pivot table report for your spreadsheet. You can rename the tab if you like.
I'll rename mine sales pivot table. In the report editor, select the fields that you'd like to have up here in the rows, columns, values or filter categories of your pivot table report. I'd like the date to appear in the rows, and the item type to appear in the columns. I'd also like the amount to appear for the values. You're pivot table report will automatically add the values of each row and column into a grand total. Grand Total summaries will appear at the right, and to the bottom of your pivot table report. Now I can easily see how many chargers, lightbulbs and panels were sold on each of the dates in March, and I can also see the total of all items sold on each date, under Grand Total here.
Furthermore, I can see the total amount of chargers, light bulbs and panels sold in the month of March, along the bottom, as well as the grand total of all items sold, in the bottom right corner. If I want to adjust how data is arranged in the table, I can drag fields to a different category. I'll change this table so that the date is in the columns and the item type is listed in the rows by clicking and dragging their fields in the Report Editor. I can remove a field from my report by clicking the x in the top right of the field.
I'll remove the Amounts field and replace it with the Price field, then I'll change the price to summarize the average price here. Now I can see the average price for each item and the average price for an item sold by date. We can also add a filter to the pivot table report, to view that report editor again, I'll click anywhere in the pivot table. I'll scroll down to add a filter, and I'll click add field. I'll filter on date here, and I'll select only those dates in the first week of March 2014, and click okay.
Now we only see information about those items sold in the first week of March 2014. And there's a couple things that are important to understand about pivot tables. First, notice that you can't edit the cell values by manually typing new values or by changing formulas in the pivot table report, It's not editable. Also you can create multiple pivot table reports from the same set of data. Simply go back to the data source, and create a new pivot table report.
It will create a new pivot table report on a separate sheet, and it won't overwrite previously created pivot tables. Pivot tables created in Microsoft Excel can be uploaded and converted to Google Sheets. And pivot table reports created in Google Sheets can be downloaded and converted to Microsoft Excel format. If you'd like to analyze and interpret your spreadsheet data without creating a bunch of formulas, give pivot tables a try. Pivot table reports can often get you the information you need without taking up a lot of time.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 75228 Viewers
80 Video lessons · 130060 Viewers
52 Video lessons · 64202 Viewers
59 Video lessons · 50004 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.