Pivot tables inGoogle Sheets summarize smaller sets of data from a more extensive table and group sums, averages, or other statistical measurements in a specific and meaningful way.- You can create pivot tables in
Google Sheets manually or automatically and sort your spreadsheet's data by rows, columns, values, and filters. - Pivot tables also let you group data manually or automatically to give you a more specific and customized window into your more extensive data set.
Pivot tables in Google Sheets are a way to get a more focused snapshot of your larger database.
They can be handy for their versatility - pivot tables group and filter information according to variables that you pick - and be as simple or complex as you want.
More than just counting, pivot tables can total up amounts, calculate averages, and even help you find errors in your dataset.
Here's how to perform some of the most basic functions of a pivot table.
How to create a pivot table in Google Sheets manually
Making a pivot table manually allows you to define which categories go in the rows and columns, as well as which values you'd like represented or totaled and what filters you'd like to apply to your data.
- Open an existing spreadsheet in Google Sheets.
- Go to Data in the top menu and then select Pivot table.
- Determine your data range. Google sheets will automatically take into account your whole spreadsheet, but you can also specify which cells you want it to cover.
- Select the New sheet option to set the pivot table on a separate sheet. Or choose Existing sheet to put the pivot table on a page you've already created. Then click Create.
- Click Add by the option Rows to select specific column data and define what appears in your rows. You can add multiple column categories to the Rows section.
- Modify how data is presented in row categories by changing the Order or Sort by setting. If you don't want any changes, keep the "Order" set to ascending and the "Sort by" set to the column title.
- You can repeat this process for the Columns section and determine which column data from your original seat is viewable in the pivot table.
- Next, set the value of each category by clicking Add next to Values.
- In the Summarize by dropdown, set how you want the pivot table to calculate your values. You can choose to sum your data, average, or find the minimum or maximum.
- Under the Show as section, you can switch from Default to a percentage of a row, column, or grand total.
- You can use the Filter section to get an even closer look at your data. Just set your filter only to show a particular set of column data within the parameters of your pivot table.
Quick tip: A pivot table works best when style and spelling are uniform in the original spreadsheet. Otherwise, you'll get multiple entries featuring different versions of the same variable in your pivot table.
How to create a pivot table in Google Sheets based on a suggestion
Google Sheets creates suggestions for pivot tables representing the information you're likely to want to be broken down. Making a pivot table based on a suggestion allows you to skip the process of manually defining rows and columns. This saves time, but only if the suggestion for the pivot table you want pops up.
- Open an existing spreadsheet in Google Sheets
- Select Data from the top menu, then Pivot table from the dropdown.
- Create your pivot table using your entire or selected data range in a new or existing sheet.
- In the left side column that appears, look underneath Suggested for a small list of potential pivot table ideas.
- Select the one you want and begin to adjust your pivot table based on row, columns, value, and filter data.
How to manually group data in a pivot table
You can create a pivot table as before, then highlight and compare certain data areas based on groups. Here's how to group pivot table categories manually.
- On the pivot table you arranged manually, click Command (on a Mac) or Control (on a PC) + whichever cell columns apply to your first group.
- Right-click your selection and then choose Create pivot group.
- You'll see your categories as before, but with minus symbols to allow a dropdown function. Now the separate categories will be viewable in one shared column featuring both their category titles. Double click a cell marked with the minus symbol to rename it if you want.
- Repeat this process for other groups of data and view them in a collapsible format with totals for the group and the sub-categories.
How to automatically group data in a pivot table
If you'd like to break your data into groups based on years or want to group it by date, you can do this automatically. This tool can come in handy for any long list of data assigned number values already, but only cells with numerical values (not including dates or dollar values) can be grouped automatically.
- In the original spreadsheet, assign each category a number that corresponds with it. (For something like months, try 12, 1, or 2, for example). You can do this by entering a number in the first row and hovering to get a + symbol, then dragging it down to the end of the section to fill the value for that set of entries.
- Label this column.
- Create a new pivot table by going to Data, Pivot table, and picking New sheet.
- In Rows, click Add and then click the category you want to see.
- In the Values section, choose Add before selecting an option, like Amount.
- To group your 1 and 2 categories and put 12 in another, select all three and right-click.
- When a new option called Create pivot group rule appears, click it.
- Now define the range, so it only includes what category data you want to appear in the group.
- Set your Interval size and then click Okay. Now, you'll have your selected categories in one group and anything in another group.
More ways to edit and work with pivot tables
Here are a few more ways to adjust and get the most out of the pivot table you've created using other facets of the feature in Google Sheets.
- Add data: On the right side where you set up the pivot table, you can always add to Rows, Columns, or Values to expand the pivot table with another variable.
- Change row or column names: To change row or column names, double click inside the label cell and rename it.
- Change sort order: To see your data in a different order, click the dropdown menu within a variable and sort differently or see a separate order (descending or ascending alphabetically, by amount, etc.)
- Change the data range: The data range signifies which cells are being used from the original spreadsheet to make the pivot table. Change the range by clicking the grid symbol at the top of the pivot table editor and editing it.
- Delete data: Any variable you add to your pivot table can be removed using the X feature at the top right of the table.
- Hide data with filters: Filtering allows you to remove specific data from the pivot table. Under Filters, click Add and choose which category you'd like to filter within. Then, under Status, you can use the dropdown menus to uncheck certain conditions or values. For example, you could select to filter by Account number in the budget and deselect a particular bank account number, which would prevent those entries from being counted in the pivot table.
- Move data: Make rows columns and vice versa by simply dragging their boxes to the section you'd like them in - for example, you might see that there are more columns than rows and switch them to make the pivot table more vertically readable.
- Repeat row labels: Not every entry within a group will have a row label. In the box for that group, check Repeat row labels to apply the group name to every row.
- Show totals: Sum totals for each category or group will typically be visible, and you can check or uncheck Show totals within a variable's box to change this.
- Show values as percentages: Change values to percentages by, underneath Values, clicking the dropdown menu under Show as, which will let you convert values into percentages of rows, columns, or totals.
- Create custom formulas: If you want to create a column that does a certain calculation for each row, click Add underneath Values and click Calculated field, which will add a column with no values. Define the formula (let's say you want to subtract 100 from each row value) in the text field and customize how the values are shown in the dropdown menus. Under Summarize by change Sum to Custom.
- Show details of data: Double click any value to show its source data (for example, anything within the "Freelance" category of the budget). This will create a new sheet with a list of the data used to arrive at the value.