Pivot table in Google Spreadsheet

↑ Home

For convenience, let’s take ready-made arbitrary data from a small example.

Open the tab with an example table https://docs.google.com/spreadsheets/d/1UOBLxHVsCwYK78N9yBZBk9jkdJbvhmBIZpXt868hwfk/edit?usp=sharing and let's see what this data is all about.

Here we are a German supplier of a range of products from 6 categories. Our clients are 7 supermarkets and the table lists some orders from these seven companies in 2021 in the following cities: Hamburg, Dresden, Cologne, Munich, Frankfurt, Stuttgart.

One row in this table means one order and contains data such as the date of the order, its volume, the unit price of the ordered product, as well as the resulting revenue. The six cities mentioned are divided into four regions, and an indication of their regional affiliation is also present in the table.

But the problem is that in such a tabular form we cannot quickly analyze important facts and changes occurring in the business processes of our company. The simplest examples of important facts at a glance include the following questions:

Which product categories generate the largest share of revenue?
Which region is the most significant in terms of revenue?
Which customers are the most important for us, again based on the same revenue indicators?

These are just the first three questions that immediately come to mind.

In addition, a number of subsequent questions may arise from all these named questions:

firstly, for example, the same questions only on a certain day, month, year...;
or the same questions, but in relation to only one specific city;
In addition, the object of analysis can be not only revenue, but also the volume of goods sold.

And so on and so forth. There can be a huge number of facts of interest, and it is thanks to pivot tables that we will now learn how to conveniently and simply extract such facts from raw data in a form that is visual and understandable to us.

↑ Step 1

First of all, we select all the values from the table and the easiest way to do this is as follows:

click on one of the cells inside the table;
use the key combination CTRL+“A”;
In this way, Google Sheets all data adjacent to each other are completely selected for us;
go to the “Insert” tab;
select the “Pivot table” item:

and now we are asked to create a new pivot table.

We are quite happy that the table will be placed on a new sheet, so we check the checkbox in the desired line and click “Create”, as a result of which a new worksheet is created, on which such a field for the future pivot table is placed:

On the right side of the window there is Editor, with the help of which we will generate the table we need, into which we will summarize the statistics that interest us.

↑ Step 2

As an example, let's first add a division by city.

To do this in Editor, in section Rows, click on the “Add” button and select the field in the drop-down list of column headers of the source table City.

Immediately in the pivot table, the names of cities appeared in separate rows.

Have you got them too? Great!

And before we go any further, let's see what happens if we make a selection in the Columns section. Therefore, let’s undo the last action using the key combination CTRL + “Z”. Our pivot table is empty again and this time we will select the field City not in section Rows, and in the section Columns. Now we list cities not by rows, but by columns:

However, this is still just a listing of cities and we still do not see any meanings related to these cities.

So it's time to look at how pivot tables can be given a more complex structure and how they can be made more readable.

↑ Step 3

To begin with, for example, let's display the revenue for each city in the pivot table. To do this, simply select the field Revenue In chapter Values:

The default values will simply be inserted from the source table. If desired, they can be converted into the desired form, for example, percentages of the total amount, etc.

Now let's add to the section The values field Quantity. And at the same time, let’s change the column headings to more familiar ones:

Now the pivot table already displays two values for each city - revenue and quantity of goods sold. What kind of goods were sold and when?

↑ Step 4

Add to section Rows field Product, to the section Columns - field date:

Now you can immediately see where, what and when was sold and what revenue each product brought in in different cities.

Agree, systematized "food for thought"has become much larger and is easier to perceive and analyze than in our original example. Moreover, columns and rows can be swapped by simply dragging the corresponding fields with the cursor, as inside sections (Rows, Columns, The values), and between them. The selections of values in the pivot table will change accordingly.

↑ Step 5

We add and change different fields in different sections, achieving the desired result.

And when the desired picture begins to emerge in the structure of the pivot table, we simply leave the arrangement of the fields as is.

Hooray! Our pivot table is ready.

↑ Conclusion

Experiment with source table and see for yourself that with certain skills, compiling pivot tables is not difficult.

Spreadsheets

January 23, 2021 6