Pivot tables are designed to help you analyze data quickly and easily. A pivot table is a table of data that can be rearranged and manipulated to display different views of the information. For example, you can use a pivot table to display a list of a firm’s expenses by different sub-accounts, broken down by month. Or you can use it to perform a search for the lowest and highest value for a set of values. The pivot table can also be used to display data in various ways, such as a pie chart or a histogram.
All of us have used pivot tables at some point. While they are a handy way to organize data in tables, the way Google Sheets treat them is a little bit different from worksheets in Microsoft Excel. Here is a quick guide on how to use pivot tables with screenshots to help you understand this concept.
Working with a large Google or Microsoft Excel spreadsheet can sometimes be a challenge. To make matters worse, if the technical passage is not organized! Such conditions are not good if you are in a race against the clock and want to finish your work quickly. The pivot table in Google Sheets can help you in such cases. If your spreadsheet is growing by the day, a pivot table in Google Sheets or Excel can help you quickly manage and summarize your data to get the results you want. In short, a pivot table allows you to collect values from any cell in a spreadsheet and summarize them immediately. If your spreadsheet contains duplicate values, you can learn how to remove duplicate values from Google Sheets.
Create, edit, and use spreadsheets in Google Sheets
Suppose you are working on a complex data table. In this case, the summary table in Google Sheets will help you highlight small pieces of information and come to meaningful conclusions by performing different actions on them. Let’s look at the process of creating a pivot table in Google Sheets.
4 steps to creating a spreadsheet in Google Sheets
1. Select a range of cells in Google Sheets. 2. Click on the Data tab and select the Spreadsheet option. 3. Define the settings and click New. 4. Check the turntable. We can create a pivot table by following the four steps above. Let’s take a closer look at these steps with the help of pictures.
Creating a pivot table in Google Sheets
Let’s understand what a pivot table is in a Google Sheet report using the following sales table example. The table consists of four columns with the status, the product number, the quantity sold and the unit price. alt=Example of opening raw data data data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Example of opening raw data
1. Select cells
First, select the data field in the data sheet. However, to create a pivot table, you must ensure that the selected cells in the pivot table have the correct column headings. If you want to select the whole record, you can also click on the box in the upper left corner of the existing sheet. alt=Select cell data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887122_290_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Select cell
2. Create a worksheet
Click the Data tab on the menu bar and select Advanced Spreadsheet from the pop-up menu. alt=Create a worksheet data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887123_618_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Create a worksheet
3. Define the parameters and click Create.
A dialog box will then appear asking you to confirm the selection of the cell range. You can also choose whether the pivot table should be on the original data sheet or on a new sheet. After you make the settings, click the Create button. alt=Click New data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887124_974_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Click New
4. Display of data in summary table
The pivot table in Google Sheets will open automatically. You can also click on the PivotTable tab in the bottom tab bar. alt=Show example pivot table data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887125_204_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Show example pivot table
Editing a worksheet
When you open a pivot table in Google Sheets, the pivot table editor window appears in the right sidebar. Let’s familiarize ourselves with the composition of the sidebar. The Suggestions section at the top of the sidebar provides suggestions based on the information selected in the spreadsheet. You can click the Add button in this section to create a pivot table according to the settings defined by Google Sheets. alt=Use a suggested section data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887126_452_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Use a suggested section You can use the Summary Table Editor to add four fields next to the records: Rows, columns, values, filters. Rows: Adds all unique elements of a specific column in your dataset as row heads to the pivot table. These are always the first data points you see in the pivot table, highlighted in light gray on the left. Speaker: Adds selected data points (headings) in aggregate for each column in your table that are highlighted in dark gray at the top of the table. That is: Adds the current values of each header in your record to sort them in your summary table. Filter: Adds a filter to the table to display only the data points that meet certain criteria.
1. select rows and columns to create a pivot table
Suppose you need to calculate the total number of sales in each state (see example above). Click the Add button before the Rows section. The table shows the parameters we used. First, select the Status column in the Rows field. Since we need to know the number of cells, select the Turnover column under Summary Table Values. On this tab, under Summarize By, select SUM. alt=Select required parameters data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887127_341_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Select required parameters Here you can see what the result will look like after the operation. alt=Show result data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887128_513_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Show result
2. How to handle multiple values
In Google Sheets, you can work with multiple values in a single summary table. For example, let’s calculate the average income of each state. In the Values field, choose the Add button and select the Unit Price column. Then select the AVERAGE function under Summarize by. alt=Select average parameter data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887129_316_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Select average parameter Check the appearance of the table after adding the presets. alt=Show Results data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887130_129_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Show Results
3. How to manage multiple categories
Google Sheets pivot table also allows the user to get a view of the data for multiple categories in a dataset consisting of thousands of rows. Suppose you are asked to determine the total number of sales and the average sales of a Class 1 product manufactured in the state of California. To continue, add the Product Number tab to the Rows section. alt=Add String Product Number data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887131_193_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Add String Product Number The number of products and their sales and revenue are shown in the table below. alt=View results data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887132_818_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />View results Here we can quickly discover that California had 63 sales of product 1 with an average revenue of $15 per unit.
4. filtering data in pivot tables
But what if you want to display the data in more detail? For example, suppose that all states are only required to submit indicators for output 3. To do this, click on the Filter tab in the overview table and select the product number as an option. Then uncheck all other product numbers as shown in the figure below. alt=Filter product number data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887133_909_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Filter product number Now check the results. alt=Display result data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887134_14_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Display result You can also filter the data based on specific values or conditions. For example, suppose you want to show the number of products that have sold 50 or more times. First, select the Sales column from the Filter menu. alt=Sales tab data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887135_209_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Select the Sales tab Now click on the Status field and select the Filter by condition option. Then select More or Equal from the pop-up menu. alt=Select Required Condition data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887136_850_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Select Required Condition Now enter a condition value. Here we enter 50 according to the terms accepted above. alt=Enter these conditions and press OK. data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887137_172_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Enter these conditions and press OK. You can view the results as shown in the figure below. alt=View Results data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887138_46_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />View Results When reading the data, be careful not to be confused by the totals for each state; these numbers are not taken into account when filtering the data. If this is confusing, you can disable the Show Totals option under Rows. alt=Disable Show Totals option data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887139_926_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Disable Show Totals Option
5. How to make your own formula
Suppose you are not satisfied with the evaluation functions or you are using a formula that is not included in the function. In this case you can make it with the option calculated field. In the Values section, click Add and select the Calculate field. alt=Click the Calculatable field data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887140_203_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Click the Calculatable field Now enter the custom formula in the data field. alt=Enter a custom formula data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887140_744_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Enter a custom formula You will see that the calculated field is displayed in a separate column of the table. Similarly, you can add multiple columns to a pivot table with calculated fields, depending on your needs. alt=Calculated field data-ezsrc=http://server.digimetriq.com/wp-content/uploads/2021/06/1623887141_623_A-Quick-Guide-On-How-To-Use-Pivot-Table-In.png />Calculated field view To use a pivot table in Google Sheets to analyze data, you need to know how to perform different types of calculations on given data. Conditional formatting in Google Sheets also helps users organize pivot tables and display only the values they need.
Copy and paste pivot tables in Google Sheets
When you have created a pivot table with all the necessary formats and values, you can save them individually using the Copy/Paste function.
1. Select cells
You can select the cells used in the pivot table or the entire Google Sheets worksheet by clicking on the box in the upper left corner.
2. Using a shortcut key for the copy and paste function
If you are a Windows user, press Ctrl + C and paste the pivot table into another worksheet by pressing Ctrl + V. Make sure you have enough cells to put the data in the worksheet. If you are a Mac user, use Cmd + C to copy the distribution items to the clipboard, and Cmd + V to paste the values into another sheet. Also note that pivot tables cannot be created in the Google Sheets application; you need to work in the desktop versions for that.
Supplement
Working with complex data sheets can be a real challenge if you don’t know what features are needed to help you reduce your workload. Google Sheets PivotTable is one of those tools that allows you to easily summarize the data that you have randomly entered into a spreadsheet. Pivot tables offer many advantages, but only those related to the aggregation of data. There are not many options for performing complex calculations, which may require you to perform them manually. Moreover, creating this task is not easy for all users; you need to practice pivot tables daily to fully understand how to use them in different scenarios.
Frequently Asked Questions
How do you use pivot tables in Google Sheets?
Pivot tables are a powerful tool in the datastore, and can be used to help you extract some useful information from your big data. Here’s how to make a pivot table in Google Sheets. This post will focus on how to get the most out of Pivot Table in Google Sheets. I will guide you through a series of examples showing you how to create a Pivot Table, perform some basic calculations, explore data using the Pivot Table and put a Pivot Table to use.
What is the best way to use a pivot table?
Pivot tables are a great tool for analyzing your data and making quick decisions, but not all pivot tables are created equally. Today we ask you to use the Pivot Table feature in Google Sheets to create a pivot table that summarises the data from your dashboard. In this post I will go over the basic uses for a pivot table and what it can do for you. A pivot table is a spreadsheet tool that allows you to select a group of values and sort them by any attribute you choose, and in turn, displays the results in rows and columns that you can manipulate as you like. In this guide we will go over some of the basic uses of this tool, but feel free to experiment with the drag-and-drop features of pivot tables to get a feel for how the tool can easily be applied to your own data. We will also go over how to save pivot table data for future use, and how to share pivot table files with other users.
How do I navigate pivot table?
Pivot tables are an easy tool to use when summarizing large amounts of data to gain insight from your data. One of the best known examples is the Pivot Table in the Google Spreadsheet, which can be used to produce charts of data. Pivot tables are easy and handy, but often not too intuitive. If you are a newbie to pivot tables, it is a good idea to read up on their basic usage. If you have a question, have a suggestion, or want to send us feedback, feel free to use the form below or drop us a mail. Welcome to our Pivot Table Guide. In this guide, we will be going over everything you need to know about pivot tables, like how to create and modify them, as well as how to use them in formulas. We will also be going over some of the common uses for pivot tables as well as some of the items that you can do with them.
Related Tags:
Feedback,google sheets pivot table formattinggoogle sheets advanced pivot tablewhat is a pivot table in google sheetsgoogle sheets pivot table group by monthgoogle sheets pivot table countcalculated field in pivot table google sheets,People also search for,Privacy settings,How Search works,7:03Google Sheets Pivot Table – advanced features (calculated field …Radu PopaYouTube – Aug 19, 2020,7:03,Google Sheets Pivot Table – advanced features (calculated field …,Radu PopaYouTube – Aug 19, 2020,Radu Popa,YouTube – Aug 19, 2020,25:31Google Sheets Pivot Tables – Calculated Fields, Custom Formulas …Learn Google SpreadsheetsYouTube – Oct 7, 2017,25:31,Google Sheets Pivot Tables – Calculated Fields, Custom Formulas …,Learn Google SpreadsheetsYouTube – Oct 7, 2017,Learn Google Spreadsheets,YouTube – Oct 7, 2017,1:05:25Pivot Tables in Google Sheets – Recorded LiveProlific OaktreeYouTube – Feb 14, 2020,1:05:25,Pivot Tables in Google Sheets – Recorded Live,Prolific OaktreeYouTube – Feb 14, 2020,Prolific Oaktree,YouTube – Feb 14, 2020,See more,1:23Google Sheets: Pivot Table COUNTUNIQUE to count each unique …DoIT Training at Stony Brook UniversityYouTube – Nov 1, 2019,1:23,Google Sheets: Pivot Table COUNTUNIQUE to count each unique …,DoIT Training at Stony Brook UniversityYouTube – Nov 1, 2019,DoIT Training at Stony Brook University,YouTube – Nov 1, 2019,16:06Google Sheets Pivot Tables – SUM, AVERAGE, MEDIAN, COUNT …Learn Google SpreadsheetsYouTube – Oct 2, 2017,16:06,Google Sheets Pivot Tables – SUM, AVERAGE, MEDIAN, COUNT …,Learn Google SpreadsheetsYouTube – Oct 2, 2017,YouTube – Oct 2, 2017,15:58Pivot Table – Text Value Field Instead of Counts – Google SheetsLearn Google SpreadsheetsYouTube – Aug 29, 2019,15:58,Pivot Table – Text Value Field Instead of Counts – Google Sheets,Learn Google SpreadsheetsYouTube – Aug 29, 2019,YouTube – Aug 29, 2019,google sheets pivot table formatting,what is a pivot table in google sheets,google sheets pivot table multiple sheets,google sheets pivot table group by month,google sheets pivot table from another sheet,pivot tables in google sheets 2020
