Google Sheets energy ideas: Learn how to use pivot tables

Among the best methods to grasp Google Sheets — the highly effective spreadsheet app that you simply use in an internet browser — is to learn to construct and use pivot tables.

To explain it very typically, a pivot desk takes information from a gaggle of cells in your spreadsheet and presents it in additional understandable and attention-grabbing methods.

Pivot tables are primarily used to compile quantity information in cells, however they’ll additionally manipulate textual content information. They usually’re most useful after they’re used to extract cell information from a spreadsheet that has a number of columns or rows.

Table of Contents

Why use pivot tables?

One of the best ways to clarify what a pivot desk does, and why you’ll wish to create one, is to point out examples. Under is a spreadsheet of “uncooked” cell information that hasn’t been organized. Notice the 2 columns that listing the product names and the quantities of every product that had been bought. Gross sales for a similar product names had been entered a number of occasions, so it’s inconceivable to inform at a look the entire gross sales for every product sort.

google sheets pivot tables 01 raw spreadsheet — The uncooked information on this spreadsheet is tough to decipher. (Click on picture to enlarge it.)

Here’s a pivot desk that was created from this spreadsheet. It’s been designed to summarize the entire quantities of every sort of product bought:

google sheets pivot tables 02 amount sold pivot table — This pivot desk provides up the quantities of every product bought, displaying general totals.

The desk compiles all the info from the PRODUCT NAME column and all the info from the AMOUNT SOLD column, putting them neatly collectively for a centered view of what number of of every product bought — information that’s very laborious to see within the unique spreadsheet.

In our second pivot desk instance, beneath, we see the common of complete gross sales of merchandise which were credited to every salesperson. Once more, the pivot desk pulls out info that’s inconceivable to shortly glean from the unique spreadsheet.

google sheets pivot tables 03 avg sales pivot table — This pivot desk computes the common sale worth for every salesperson.

Making ready your spreadsheet for pivot tables

With the intention to make helpful pivot tables in Google Sheets, it’s finest to arrange your spreadsheet for them. The spreadsheet ought to have the next splendid elements in its formatting:

It accommodates “uncooked” information — that’s, the spreadsheet lacks charts, graphics, tables, or different miscellaneous parts.
There are not any empty columns or rows between columns/rows that include information inside their cells.
The columns or rows that you choose for the pivot desk to compile information from have clearly worded headers. Though you possibly can manually choose particular columns or rows, it’s finest if the pivot desk is allowed to extract all of the cell information that seems on a spreadsheet. Clearly worded headers make that simpler.

Mainly, the easier and extra simple your spreadsheet is, the higher.

To prep your spreadsheet in order that it may be used to generate pivot tables simply, first make a replica of the spreadsheet file — or make a reproduction of the sheet with the cell information that you simply wish to manipulate in a pivot desk.

To make a replica of your spreadsheet: Open the spreadsheet in Google Sheets. On the higher left, choose File > Make a replica. On the panel that opens, enter a brand new identify for the copy and click on Make a replica. A brand new browser tab will open with the brand new copy of your spreadsheet.

To duplicate a sheet in your spreadsheet: Open the spreadsheet in Google Sheets. Alongside the underside of your spreadsheet, click on the down arrow on the tab of the sheet that you simply wish to duplicate. From the menu that opens, choose Duplicate. A replica of this sheet will then be added to your spreadsheet, set to the correct of the unique sheet.

google sheets pivot tables 04 duplicate sheet — Creating a reproduction sheet in a spreadsheet.

To provide the brand new sheet copy a singular identify, click on the down arrow on its tab. From the menu that opens, choose Rename and kind in a brand new identify.

Then you possibly can edit the copy of your spreadsheet or sheet with out altering the unique. Delete all charts, graphics, tables, and different miscellaneous parts. Delete empty columns and rows. And examine and replace the names of the headers of the columns and rows.

Utilizing the Discover software to create a pivot desk

When your spreadsheet is open in Google Sheets, the Discover icon on the decrease proper might seem as inexperienced. Which means that Google’s Discover software has a instructed pivot desk for you. Clicking this icon will open the Discover sidebar alongside the correct aspect.

Scroll right down to the PIVOT TABLE part of the sidebar to see a thumbnail of a pivot desk that was generated by Google Sheets. Clicking MORE (if this phrase seems; it won’t) will present you extra pivot tables created by Google Sheets.

google sheets pivot tables 05 explore pane — Google’s Discover software might counsel pivot tables on your sheet. (Click on picture to enlarge it.)

Transfer the pointer over the thumbnail of a pivot desk. Two icons will seem over the higher proper of the thumbnail:

google sheets pivot tables 05b explore pane pivot table — Click on the grid icon to insert the pivot desk onto your sheet or the magnifying glass icon to get a more in-depth look.

The grid icon: This can place the instructed pivot desk in your spreadsheet. You’ll be able to choose to have the pivot desk set onto the present sheet you’re viewing, or onto a brand new sheet.

In most cases, it’s finest to put a brand new pivot desk onto a brand new sheet — whether or not it’s a pivot desk instructed by this Discover software, or one that you simply create from scratch (which this information will delve into beneath). Setting a pivot desk onto a sheet that already has cell information on it may well make the entire sheet seem complicated and tough to handle. It would mess up the operations of the pivot desk, too.

The magnifying glass icon: This can open a bigger picture of the pivot desk thumbnail over your spreadsheet. This offers you a greater preview of the instructed pivot desk.

Making a pivot desk from scratch

In the event you don’t see a pivot desk you wish to create within the Discover sidebar, it’s simple to create your individual. You’ll be able to both choose the columns or rows within the spreadsheet that you really want the pivot desk to extract information from (together with their headers), or you possibly can strive skipping this step, particularly should you’ve edited the spreadsheet in response to the instructed formatting described earlier.

Then on the menu bar over your spreadsheet, choose Insert > Pivot desk. A “Create pivot desk” pane seems. In the event you chosen sure cells within the sheet to extract information from, that choice will seem within the “Information vary” area. In the event you didn’t choose any information, that area will present the complete spreadsheet information set.

google sheets pivot tables 06 create pivot table pane — Creating a brand new pivot desk. (Click on picture to enlarge it.)

As talked about above, it’s higher to insert your pivot desk to a brand new sheet, so depart “New sheet” chosen, then click on Create.

You’ll be taken to a brand new sheet with the “Pivot desk editor” sidebar open alongside the correct aspect. The editor has two columns. The best column has a search field alongside its high. Under this, it lists the headings of the columns or rows that you simply chosen for this pivot desk to extract cell information from — or, should you didn’t choose any columns or rows, it lists all of the headers within the sheet. This search column is useful for locating a particular header in your spreadsheet that you simply wish to embody in your pivot desk, particularly in case your spreadsheet is extraordinarily giant.

google sheets pivot tables 07 pivot table editor — The pivot desk editor helps you construct pivot tables utilizing labeled columns and rows out of your uncooked information sheet. (Click on picture to enlarge it.)

Within the left column of the pivot desk editor, you’ll doubtless see a couple of pivot desk solutions that Google Sheets has parsed out of your information set. These will doubtless be the identical solutions that appeared within the Discover sidebar earlier. In our product gross sales instance, Sheets suggests “Common of COST EACH for every SELLER,” “Sum of AMOUNT SOLD for every PRODUCT NAME,” and “Common of TOTAL SALES for every SELLER.” Click on any of them to see that full pivot desk.

Under that you simply’ll discover 4 sorts of parts you possibly can add to the pivot desk manually:

Rows and Columns

If you click on the Add button to the correct of the “Rows” or “Columns” gadgets, a menu will open itemizing the headings of the columns and rows within the information vary for this pivot desk. The cell information underneath the heading you choose can be added to your pivot desk as rows or columns.

Considerably confusingly, should you add a Rows component, a column can be created for the heading you choose, and its cell information will seem in rows beneath. In the event you add a Columns component, a row can be created for the heading you choose, with its cell information showing as columns to its proper.

The Rows and Columns parts don’t carry out calculations after they’re added to a pivot desk, however they do take away duplicates.

In our gross sales instance, going to Rows, clicking Add subsequent to it, and deciding on PRODUCT NAME from the pop-up menu provides a PRODUCT NAME column with the product names showing as rows beneath. There’s only one row for every product identify.

google sheets pivot tables 08 product name rows added — The pivot desk now has a PRODUCT NAME column with a row for every product. (Click on picture to enlarge it.)

You’ll additionally see settings packing containers for the weather you added seem underneath “Rows” or “Columns” within the pivot desk editor. There are a couple of methods you possibly can alter these parts:

Order: Clicking this dropdown menu lets you choose Ascending or Descending. For instance, if the cell information underneath the heading for this row or column consists of numbers, then their values can be ordered as rising (ascending) or reducing (descending). If the cells include textual content, then they are going to be listed in alphabetical order (ascending) or in reverse alphabetical order (descending).

Type by: If there are two or extra row or column parts in your pivot desk, you possibly can choose which one takes priority for the Order perform, and the opposite rows or columns will comply with it. At this level in our instance we solely have one column, so we’ll circle again to this perform later.

Present totals: If the cell information underneath this heading consists of numbers and also you examine this field, then a row or column can be added to the pivot desk that lists the entire of those numbers.

To delete a rows or columns component, click on the X on the higher proper of its settings field.

Values

To carry out calculations on the info in a pivot desk, you’ll want so as to add a Values component. If you click on the Add button to the correct of the “Values” part, a menu will open itemizing the headings of the columns and rows within the information vary for this pivot desk. Typically you’ll wish to choose a heading for a column or row with numerical values.

When you choose a heading, a column with this heading can be added to your pivot desk. The cell information underneath that heading can be calculated with a system that you choose.

If you add a values column to a pivot desk, a settings field for it can seem within the “Values” part of the pivot desk editor, with these capabilities:

Summarize by: Clicking this dropdown menu opens a menu of a number of calculation sorts. Those you’ll most likely use most are SUM and AVERAGE. If the cell information underneath the heading you’ve chosen consists of numbers, then SUM will add up these numbers. AVERAGE will calculate the common of those numbers.

When you choose a heading with numerical information beneath as you’re including a price column, Sheets chooses the SUM perform by default. In our gross sales figures instance, going to Values, clicking its Add button, and deciding on AMOUNT SOLD provides a “SUM of AMOUNT SOLD” column with the gross sales numbers for every product added up and proven subsequent to every product identify.

google sheets pivot tables 09 amount sold value column — The pivot desk now has a “SUM of AMOUNT SOLD” worth column. (Click on picture to enlarge it.)

google sheets pivot tables 09b show as percentages — You’ll be able to alter the values in a price column to show as percentages as a substitute of numerals.

Present as: By default, Sheets exhibits the calculations in a price column as numerals. This dropdown menu enables you to as a substitute show the values calculation as a proportion — representing a proportion of the row or column that the determine is in, or a proportion of a grand complete.

To delete a price column, click on the X on the higher proper of its settings field.

Now that we have now two columns displayed in our instance pivot desk, let’s take a fast detour again to the settings field for the Rows component we added. The gadgets within the “PRODUCT NAME” column are listed in ascending alphabetical order. But when we now set the “Type by” area to SUM of AMOUNT SOLD, then the numbers underneath the “SUM of AMOUNT SOLD” column can be listed in ascending order. The order of the gadgets within the “PRODUCT NAME” column will shift to appropriately correspond with their respective gross sales numbers.

google sheets pivot tables 10 sort by value column — Altering the “Type by” dropdown to “SUM of AMOUNT SOLD” rearranges the rows so as of the numbers within the second column. (Click on picture to enlarge it.)

Filters

Filters allow you to exclude sure gadgets or values within the unique information set out of your pivot desk with a view to current a cleaner, clearer set of knowledge. For a whole information to how filters work and learn how to profit from them, see “Google Sheets energy ideas: Learn how to use filters and slicers.” However this is a fast overview of learn how to use them in pivot tables.

If you click on the Add button to the correct of the “Filters” part, a menu will open itemizing the headings of the columns and rows within the information vary for this pivot desk. Select a heading for one of many parts (Rows, Columns, or Values) that you simply’ve already added to the pivot desk. When you choose a heading from this menu, a filter settings field for the heading can be added to the pivot desk editor. To exhibit this, we’ll add a filter for the PRODUCT NAME heading.

Every filter you add has a single “Standing” perform in its settings field. Click on the dropdown menu that claims Displaying all gadgets to open a panel that permits you to filter the info underneath the heading that you simply chosen.

google sheets pivot tables 11 filter by values — Within the PRODUCT NAME filter field, click on *Displaying all gadgets* to see the filtering choices.

There are two methods to filter: by values or by situation. Filtering by values enables you to select particular numbers or phrases that you simply wish to embody or exclude from the info set. You should use the search field on the panel that pops up from the filter’s settings field to seek for numbers or phrases that seem within the cells underneath the heading that you simply chosen. If you’d like a quantity or textual content merchandise to seem within the pivot desk, depart it checked. If you wish to disguise it, uncheck it. Then click on OK.

In the event you uncheck a quantity or textual content merchandise, the row for the cell that accommodates the quantity or textual content you unchecked can be faraway from the pivot desk. Don’t fear — this row hasn’t been deleted. This filter you created has hidden this row, displaying your pivot desk with out it. Within the instance beneath, we’ve unchecked Thingmabobs to exclude these gross sales figures from the pivot desk.

google sheets pivot tables 12 thingmabobs filtered out — The Thingmabobs row has been filtered out of the pivot desk.

Filtering by situation enables you to show solely the info that meets sure standards, equivalent to containing sure phrases or having a price above or beneath a sure quantity. Let’s say you wish to exclude any gross sales of lower than 11 gadgets from the totals in your pivot desk. To do that, you’d filter the AMOUNT SOLD information to point out solely cells that include numbers better than 10.

Within the Filters part, click on the Add button and select the AMOUNT SOLD header from the pop-up menu. Within the AMOUNT SOLD filter field, click on Displaying all gadgets. On the pane that seems, click on Filter by situation, then click on the field with None inside it. From the lengthy menu listing of filter variables that opens, scroll down and choose Larger than. Contained in the entry field beneath “Larger than,” sort 10. Scroll to the underside of the panel and click on OK.

google sheets pivot tables 13 filter by condition — This filter will solely embody numbers better than 10 when calculating the AMOUNT SOLD worth column.

Now your pivot desk exhibits calculations utilizing solely these cells from the info vary in your unique spreadsheet that include numbers better than 10. The cells in your unique spreadsheet that include numbers lower than 10 will not be used within the calculation for the pivot desk.

google sheets pivot tables 14 conditional filter lower totals — The pivot desk now exhibits decrease numbers within the “SUM of AMOUNT SOLD” column, since all values of 10 or much less are excluded from the calculations.

To delete a filter, click on the X on the higher proper of its settings field.

Managing your pivot tables

Edit a pivot desk: Each time you must edit a pivot desk’s settings, merely hover over the pivot desk and click on the Edit button that seems slightly below it. The sidebar for the pivot desk editor will open alongside the correct aspect. To shut the sidebar, click on the X on its higher proper.

google sheets pivot tables 15 edit button — Hover over a pivot desk and click on the *Edit* button to open the pivot desk editor at any time.

Hold pivot tables on separate sheets: Keep in mind, it’s often finest to set every pivot desk on a separate sheet to cut back muddle. This could additionally assist make your general spreadsheet extra versatile: You’ll be able to create a number of pivot tables — every set by itself sheet compiling the uncooked cell information out of your predominant sheet in another way from the opposite pivot tables. You’ll be able to then swap amongst these completely different pivot tables by clicking by means of their tabs.

google sheets pivot tables 16 sheet tabs — Hold pivot tables on separate sheets in your spreadsheet. (Click on picture to enlarge it.)