Ade Malsasa Akbar contact
Senior author, Open Source enthusiast.
Saturday, October 8, 2022 at 22:44

This tutorial will show you how to create Pivot Table using LibreOffice Calc -- the complete, free spreadsheet program for everyone. We will learn with examples and pictures by using a simple sales table to create sales report with multiple pivot tables we want. Now let's exercise!

Subscribe to UbuntuBuzz Telegram Channel to get article updates.

What is pivot table?

Pivot table is basically moving a table into another table in different form with filters.

 

What we will create?

Our goal this time is to make summaries of a data table in Picture 1. It is an imaginary sales data with date, employee, product category, shop, and sales columns. Basically, we will turn the data from Picture 1 below into Picture 2. Finally, you might notice that the employee names involved here are the same as the previous at our Calc Tutorials. As an acknowledgement, this table is adapted from Ellak Course's Example File mentioned at references section (that one with Brigitte, Elen, Fritz and friends).

 Picture 1. Data source

 

 Picture 2. Pivot tables


How To Use the Filters

A pivot table is typically equipped with one filter or more depicted as a drop down button at each column head. See the difference below.

Picture 3. Pivot tables with no filter selected

 Picture 4. Pivot tables with filters selected (notice the color and signs)

To show pivot table like the above, you change the filters by selecting items you want to be displayed. Picture 5 shows the employee filter selections of the three sample tables shown above.

Picture 5. Filters


Tips: aside from filtering, filters are also equipped with sorting capabilities. For example, for the date-based pivot table, you can enable filter > Custom sort > select January, February, March ... > data sorted according to month order. You can also select order ascending or descending if you want. See picture 5. 


Step 1. Prepare Data Source

In order to make Pivot Table, you need a data source first. Write Table 1 with Calc and then save it as pivot-table-exercise.ods. This table is called data source.


 Table 1. Data Source


Step 2. Create a basic Pivot Table

1. Select a cell in the source table.  Do not select multiple cell as Calc is able to read your table thoroughly.

2. Go to Insert > Pivot Table > Pivot Table Layout dialog will open.

3. Drag and drop "employee" from right to left under Row fields. See picture 6.

4. Drag and drop "sales" from right to left under Data Fields. See picture 6.

5. Click OK. 

6. Pivot Table created in a new sheet called Pivot Something.

 Picture 6. Pivot Table dialog

 

Step 3. Pivot Table Created

Pivot table with one criteria will be created in a new sheet.

 
Table 2. A basic pivot table

Explanation: this is the very basic of pivot table, it sums up between employees and their sales respectively and display them without duplicated data. For example, we can say that Cinta's sales is 3125, Shinta's 4749 while Silvie's 3836. This way, computer helps us to calculate them precisely and accordingly without us doing redundant job repeatedly. Using the same method, you can create four pivot tables based on our example:

Employee Sales Table

This table sums up employees and their respective sales. This helps us to examine that Abi is the lowest while Shinta and Widodo are the highest, in sales.

Table 3. Employee sales


Products Category Sales Table

This table sums up products (category) and their respective sales.  This helps us to see quickly that water is the highest while corn and salt are the lowest in sales.

Table 4. Product sales

Shop Sales Table

This table sums up shop branches and their respective sales. This helps us to quickly see that shop 1 is the lowest selling, shop 3 is the highest selling.

Table 5. Shop sales

Date Sales Table

This table sums up month (date) and their respective sales. This helps us to see quickly that January is the lowest month while April is the highest month in sales.

Table 6. Date sales

Pivot Table with Two Criteria or More

1. Repeat step 1 to 3 above with one more criteria into Row Fields.

2. Alternatively, you may instead put one criteria into Row Fields, and put another one into Column Fields.

3. A new pivot table created.

Table 7. Pivot table with two criteria

Table 8. Same pivot table but with Products Category as Column Fields

Explanation: pivot table above displays a summary of sales according to employees and category. From that, for example, we can say that Abi sells only salt, Budi sells only water, Cinta sells corn and sugar, while Silvie sells corn, sugar and water. We can also say, that Cinta's corn's 1174, Shinta's corn's 992 while Silvie's corn's 2149. Using the same method, you can create many combination of pivot tables as shown below:

Employee / Month Sales Table

This table sums up a comparison between employees and months (date) of sales.  This helps us to see in two ways, at the same time, total results of employee and month. For example, employee Silvie's total is 3836 while month April's total is 7089.

  Table 9. Sales by employee / month

Employee / Shop Sales Table

This table sums up a map between employees and shops in sales. This helps us observe two things at the same time, for example, total results of employee and shop. Further, let's say that employee Cinta's total is 3125 while branch Shop 2's total is 7958.

Table 10. Sales by employee / shop

Employee / Products Table

This table sums up a map of employees and their products (category) sales. This helps us to observe in two ways at the same time between employee and product total results. For example, we can say that employee Silvie's total is 3836 while product category Sugar's total is 6598.

Table 11. Sales by employee / product

Employee / Criteria Table, Inverted Position

You can also switch between row and column position of any field item. For example, this is the same table as Employee / Month above, but by putting Employee to Column Field and Date to Row Field in the Pivot Table dialog. As a result, here employees became columns and month became rows, especially now they can be filtered more easily. Compare the difference.


Table 12. Sales by date / employee

Create Multiple Pivot Table in one Sheet

1. Create a new sheet.

2. Repeat step 1-2 to create a pivot table. 

3. On the Pivot Table dialog, open the plus or triangle sign under Source and Destination.

4. Select "Selection" under Destination.

5. Click the button "Shrink" , now the dialog will shrink. 

6. With mouse cursor, select the new sheet > select an empty cell in an empty area > Shrink dialog will show the cell address for example $Sheet1.$G$1.

7.  Click the button "Shrink" once again, now the full dialog will show.

8. Click OK. 

9. Pivot table will be created in the selected sheet. 

10. Repeat step 1-9 by selecting same sheet as destination.

 

Picture 7. Pivot tables in one sheet

Explanation: this is useful to create report. You can, by this example, show as much helpful summaries as possible, from the simples one, like employee sales, to the complex ones, like employee / shop sales. As a result, you can further print out the sheet into papers, or simply export it as PDF, if you need to.


Afterwords

This should gives you a basic of basics of pivot table in LibreOffice Calc. Fortunately, not only that, you should have an understanding of the power and greatness of spreadsheet too. Pivot table helps you redisplay and sum up your data both ways intelligently and quickly. Next time, we will learn further about pivot table in a separate article. Happy learning and see you.


References 

Pivot Table Documentation -- by LibreOffice Official Book 

Pivot Table Example File (ODS) -- by LibreOffice Official Help

Pivot Table Help -- by LibreOffice Official Help

Pivot Table -- by Ellak LibreOffice Course from Greece 



This article is licensed under CC BY-SA 3.0.