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 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.
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.
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.
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.
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.
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.
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.
Employee / Products Table
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.
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.