Connect with us


How to create a pivot table in Google Sheets

If you’re stuck and wondering how to create a pivot table in Google Sheets, no worries, we have you covered. Learn how to create on in just minutes with this simple guide.

Image: YouTube / Lexnet

Tabular data surrounds us everywhere. We just used not to notice it. If you think about it, the most convenient way to transfer saved notes, lists of recipes, reports on winning lottery tickets, currency and securities rates, train schedules and other everyday data to electronic form is to save all the information in the form of spreadsheets. This is not only practical, but also a scientific way, so students learn to build spreadsheets from the very beginning of the education, and in the most challenging cases they turn to custom services.

What is the Purpose of a Pivot Table and How to Create It in Google Spreadsheets Easily?

Any duplicate data is unwittingly organized into a structure. In this form it is much easier to work with them: to search for certain values, summarize and analyze data, sort, and build charts. Therefore, if for the creation of texts of any volume the correct and most effective way is to turn to the Documents, then for the rest of the diversity of the digital world it is reasonable to use tables Google. In everyday life, spreadsheets can be used to maintain various lists of goals, kilometers and the consumption of gasoline of a personal car, the cost of gifts during the holidays and the income of the family budget. In the business world, a great number of Google spreadsheet applications have long been invented: it is a tackle for data analysis, and the accounting system of everything and everything, as well as an indispensable assistant in creating all sorts of graphs.

An unconditional leader in the implementation of all possible functionality of summary spreadsheets is Microsoft Excel. Only such giants of the BI industry as QlikView and Tableau can compete with the given tool, but due to the fact that these platforms are quite expensive, the leadership went to Microsoft Excel and Google Sheets, a comparison of which proves that they are rather individual despite the fact that they are created on the common basis. One of the most useful functions is a pivot table that can be easily created with Google drive tools. The summary spreadsheets are based on OLAP technology, described in 1993 by a well-known database researcher and author of the relational data model Edgar Codd. This is the most convenient and quick tackles for converting an array of data into an informative report.

In fact, a consolidated spreadsheet is a data processing tackle for grouping and summarizing of information. Any summary spreadsheet is based on a specific database. An array consists of fields and records in the form of a database. Each row (record) in the database is information about the individual case, object or state of the object under study, and each pile (field) is a parameter, property or attribute of all objects under investigation. All fields of the database are divided into two types: dimensions and measures. The dimension is the name or property of the object, in the context of which we can analyze various quantitative indicators. Each dimension contains a number of elements. The measure is the quantitative indicators that we compare the elements of the dimensions.

Google Sheets Pivot Table Format

In order to create a pivot table in Google Sheets, you need to follow a simple algorithm. The algorithm includes three actions: preparation of the source spreadsheet, creation of a summary spreadsheet, report setup.

1. Preparing the source spreadsheet

pivot table google sheets

A consolidated table will only be useful if your input data is properly structured. Before you start, you need to check the following parameters.

Each pile in the account spreadsheet should contain one type. For example, if you keep a record of your purchases, it should be a detached column for the date, a detached pile for the product and a detached column for the total shipment.

A detached row with column headers should be above the spreadsheet data.

Try not to use the merged cells in the account spreadsheet.

If you want to see totals for years or months, you need to append columns to the spreadsheet for their automatic calculation. On order to reckon the year, use the function YEAR (). To reckon the month, use the MONTH () function.

2. A pivot table creation

insert pivot table google sheets

To create a summary table, select the entire source table, including the headers. In the “Data” menu, select the “Pivot table report” item. After that, a detached sheet will be created, on which you need to customize the type and content of the report.

3. The configuration of the report

google sheets pivot table

Navigate to the created sheet with the summary table. Initially, it will be empty, but on the right side of the page, there appears the “Report Editor” menu. In this menu, you can append fields to “Rows”, “Columns”, “Values” and “Filter”. Use this menu to append the “Year” column to the summary table. Then append the “Month” column to the summary table. Then use the “Report Editor” to append a row to the summary table.  Then use the “Report Editor” to append the “Product” line to the summary table. Now it remains to append the “Amount” field to the values.

In values, you can set the type of the calculated total. This can be the total amount of your purchases, the minimum, the maximum purchase amount, the average cost and other options. If necessary, you can output several types of totals in one summary table.

Unlike Excel, consolidated tables in Google are updated automatically. But it makes sense to check regularly whether the pivot table refers to the entire range of source data. If you need to make changes in series, you can switch to manual mode to save the changes. In this case, the changes will be saved only after clicking on the “Update Table” button.

In addition to the simplest functions available to almost all modern spreadsheets, Google Spreadsheets has a set of unique features. In skilled hands, they turn into a serious analytical and statistical tackle with the ability to automatically collect, process and publish data, as well as the option of group work in real time. Moreover, the functionality of modern gadgets allows you to make the functionality of this tackle almost unlimited.


Like us on Facebook!


More in How-To