If you’re like most people, you use Google Sheets to store your data. And if you’re like most people, you probably use pivot tables to analyze your data. But what are pivot tables, and how can you use them to your advantage? Pivot tables are a great way to analyze your data. They let you see how different factors (like sales or income) affect each other. You can also use pivot tables to see how different groups of people (like employees or customers) behave. To get started with pivot tables, open Google Sheets and create a new sheet. Then click the “Pivot Tables” button on the toolbar (or press Ctrl+T). In the “Pivot Table” window that opens, click the “Create” button (or press Ctrl+N). In the “Create Pivot Table” window that opens, give your table a name (like “Sales”). Then select the data that you want to include in your table. To include all of the data in your sheet, select all of it by clicking on the blue arrow next to the word “Data.” To exclude some data from your table, select it by clicking on the red arrow next to the word “Data.” For example, if you want to see how sales vary by month but not by day of the week, select Monday through Friday by clicking on their checkboxes. Then click OK (or press Enter). Now it’s time to start analyzing your data! To start with, click on one of the column headers in your table and choose one of its options from the drop-down menu that appears. For example, if you want to see how sales vary by product category, choose “Product Category.” The values in each column will change according to which option you choose. For example, if you choose “Product Category,” then each column will show how much revenue each product category has generated for Google Sheets over time ..
Pivot tables let you analyze large amounts of data and narrow down large data sets to see the relationships between data points. Google Sheets uses pivot tables to summarize your data, making it easier to understand all the information contained in your spreadsheet.
What Are Pivot Tables?
Pivot tables are handy for analyzing massive amounts of data. Where a regular spreadsheet uses only two axes—columns and rows—pivot tables help us make sense of the information in your spreadsheet by summarizing any selected columns and rows of data. For example, a pivot table could be used to analyze sales brought in by divisions of a company for a specific month, where all the information is randomly entered into a dataset.
Creating a pivot table from the information in the picture above displays a neatly formatted table with information from selected columns, sorted by division.
RELATED: The Best Google Sheets Add-Ons
How to Create a Pivot Table
Fire up Chrome and open a spreadsheet in Google Sheets.
Next, select any of the cells you want to use in your pivot table. If you’re going to use everything in your dataset, you can click anywhere on the spreadsheet, you don’t have to select every cell first.
Note: Each column selected must have a header associated with it to create a pivot table with those data points.
On the menu bar at the top of the page, click “Data,” then click “Pivot Table.”
If the new table doesn’t open automatically, click “Pivot Table,” located at the bottom of your spreadsheet.
How to Edit a Pivot Table
From the pivot table sheet, the side panel lets you add rows, columns, values, and filters for viewing your data. Sometimes, Sheets offers up suggestions based on the information you chose. Click a suggestion or click “Add,” located next to any of the other options below.
When you click on any of the suggestions, Sheets automatically builds your pivot table using the option you selected from the list given.
If you’d rather customize a pivot table to for your own needs, click any of the “Add” buttons next to the four options below. Each option has a different purpose, here’s what they mean:
Rows: Adds all unique items of a specific column from your dataset to your pivot table as row headings. They are always the first data points you see in your pivot table in light grey on the left. Columns: Adds selected data points (headers) in aggregated form for each column in your table, indicated in the dark grey along the top of your table. Values: Adds the actual values of each heading from your dataset to sort on your pivot table. Filter: Adds a filter to your table to show only data points meeting specific criteria.
Click on “Add” next to Rows and add in the rows you want to display in your pivot table. For this example, we’ll be adding division and subdivision.
Next, click “Add” next to Values As and insert the values you want to sort information. We’ll be using the sum of the number of units sold and the average price per unit.
To change the sorting of each unit, click the drop-down menu, located under the heading “Summarise by.” You can choose from the sum, count, average, min, max, among others listed below.
After adding all rows, columns, values, etc. what we’re left with is an easy to read pivot table that outlines which division sold the most units and the average cost of all units sold.
RELATED: How to Import an Excel Document into Google Sheets
If you’d prefer to make your own formula, click “Add” next to the Values as heading, then click “Calculated Field.”
From the new value field, enter a formula that best summarises the data in your pivot table.
If you want to add a filter to your table, click “Add,” located next to the Filters heading.
When adding a filter to your table, select—or deselect—the values you want to show on your table, then click “OK” to apply the filter.
That’s all there is to it. Although this is just an introduction to using pivot tables, there is a seemingly endless amount of possibilities for utilizing this feature that not many people know much about.
