How to Create Pivot Tables in Calc of OpenOffice - LibreOffice

One of these useful features is the pivot table function. You can find it in all spreadsheet applications. The present guide shows how to create a pivot table in the Calc application of LibreOffice, which is the Ubuntu Linux version of Calc in OpenOffice.org. The method is exactly the same in the two applications, and very similar to Microsoft's Excel.

Pivot tables can help you to summarize data and identify trends. Let's say we have data on our friends' travels to Europe. They visited different countries and on different purposes. Some of them were on holiday, some of them had business there and others studied.

Looking at the spreadsheet, you cannot see how many of them visited each country and what was their purpose. Using a pivot table, you will be able to look at the data from all angles.

1. In order to create a pivot table, just click in the upper left corner of the data. You don't have to select the data, Calc will recognize it.

2. Then choose Data > Pivot Table > Create.

3. Calc will ask if you want to base your pivot table on the current selection or you want to use another data source. Now choose to use the current selection and click OK. The DataPilot window will pop up.

4. Drag the fields where you want them. If you want to see how many people traveled to each country, create a new row for each country by dragging “Country” to Row Fields and “Name” to Data fields. The default method here is adding values, which is certainly not possible in case of names, they need to be counted. To do that, click “Sum Name” and modify sum it to count. Select count, click OK and click OK on the DataPilot window as well. Now we have our pivot table, there is a row for every country, and the names of people who visited that country are counted.

5. You can repeat the process and drag “Purpose” to the Row Fields and “Name” to Data Fields.

6. In case you only need the pivot table sorted by purpose, you can edit the table by right-clicking on it and selecting edit. The DataPilot window will appear again, you can now drag “Country” out and drop the “Purpose” button in the row field area.

7. If you need a table analyzing countries broken down by purposes, you can drag both into the row field area.

8. You can change layout and analyze purposes broken down by countries, just drag “Purpose” in front of “Country”.

9. You can create a spectacular table with a row for each country and a column for each purpose. Right-click on the table and select Edit. In the DataPilot window, drag “Purpose” in the column field area.

10. If you want to change layout and see the purposes in the rows and the countries in the columns, just drag and drop them with your mouse.

11. If you click the Filter button, you will be able to filter things out, for example, select only two travel purposes. Click on the drop-down sign next to purposes and use the check-boxes to select the purposes you want to keep. Click OK.
    
    

    You have many options even with a small underlying table. The more data you have, the more layouts you can create. It is worth to try a few of them to see which meets your requirements.