Calc Guide
Chapter 8
Using Pivot Tables
Copyright
This document is Copyright © 2019 by the LibreOffice Documentation Team. Contributors are listed
below. You may distribute it and/or modify it under the terms of either the GNU General Public
License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons
Attribution License (http://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.
Contributors
This book is adapted and updated from the LibreOffice 4.1 Calc Guide.
To this edition
Steve Fanning Kees Kriek Jean Hollis Weber
To previous editions
Barbara Duprey Martin J Fox Jean Hollis Weber
John A Smith Klaus-Jürgen Weghorn
Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
mailing list: [email protected]
Note
Everything you send to a mailing list, including your email address and any other
personal information that is written in the message, is publicly archived and cannot be
deleted.
Publication date and software version
Published December 2019. Based on LibreOffice 6.2.
Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows and Linux.
The table below gives some common substitutions for the instructions in this chapter. For a more
detailed list, see the application Help.
Windows or Linux macOS equivalent Effect
Tools > Options menu LibreOffice > Preferences Access setup options
Right-click Control + click or right-click
depending on computer setup
Open a context menu
Ctrl (Control) ⌘ (Command) Used with other keys
F5 Shift+⌘+F5 Open the Navigator
F11 ⌘+T Open the sidebar Styles panel
Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/
Contents
Copyright..............................................................................................................................2
Contributors................................................................................................................................. 2
To this edition..........................................................................................................................2
To previous editions................................................................................................................2
Feedback..................................................................................................................................... 2
Publication date and software version.........................................................................................2
Using LibreOffice on macOS........................................................................................................2
Introduction..........................................................................................................................5
Database preconditions............................................................................................................... 5
Data sources................................................................................................................................ 6
Calc spreadsheet....................................................................................................................6
Registered data source...........................................................................................................6
Using shortcuts............................................................................................................................6
Creating a pivot table................................................................................................................... 6
The Pivot Table Layout dialog............................................................................................7
Basic layout.................................................................................................................................8
More options................................................................................................................................ 9
More settings for the fields: Field options...................................................................................11
Options for data fields...........................................................................................................12
Options for row and column fields.........................................................................................14
Options for page fields..........................................................................................................18
Working with the results of the pivot table.....................................................................18
Changing the layout................................................................................................................... 18
Grouping rows or columns......................................................................................................... 19
Grouping of categories with scalar values.............................................................................19
Grouping of categories with date / time values......................................................................20
Grouping without automatic creation of intervals...................................................................21
Sorting the result........................................................................................................................ 23
Select sort order from drop-down menus on each column heading.......................................24
Sort manually by using drag and drop...................................................................................24
Sort automatically..................................................................................................................25
Drilling (showing details)............................................................................................................ 25
Filtering...................................................................................................................................... 26
Updating (refreshing) changed values.......................................................................................28
Cell formatting............................................................................................................................ 28
Deleting a pivot table................................................................................................................. 29
Using pivot table results elsewhere.................................................................................29
The problem............................................................................................................................... 29
The solution: Function GETPIVOTDATA().................................................................................30
Syntax................................................................................................................................... 30
First syntax variation.............................................................................................................30
Second syntax variation........................................................................................................30
Using pivot charts..............................................................................................................31
Introduction................................................................................................................................ 31
Creating a pivot chart.................................................................................................................33
Chapter 8 Pivot Tables | 3
Editing a pivot chart................................................................................................................... 34
Updating a pivot chart................................................................................................................ 34
Filtering a pivot chart..................................................................................................................34
Deleting a pivot chart.................................................................................................................35
4| Chapter 8 Pivot Tables
Introduction
Many requests for spreadsheet support are the result of using complicated formulas and solutions
to solve simple day-to-day problems. For more efficient and effective solutions, use the pivot table,
a tool for combining, comparing, and analyzing large amounts of data easily. Using pivot tables,
you can view different summaries of the source data, display the details of areas of interest, and
create reports, whether you are a beginner, an intermediate user, or an advanced user. In addition
you can create a pivot chart to view a graphical representation of the data in a pivot table.
Database preconditions
To work with a pivot table, you need a list of raw data, similar to a database table, consisting of
rows (data sets) and columns (data fields). The field names are in the first row above the list.
The data source could be an external file or database. For the simplest case, where data is
contained in a Calc spreadsheet, Calc offers sorting functions that do not require the pivot table.
For processing data in lists, Calc needs to know where in the spreadsheet the list is. The list can
be anywhere in the sheet, in any position. A spreadsheet can contain several unrelated lists.
Calc recognizes your lists automatically. It uses the following logic: Starting from the cell you have
selected (which must be within the list), Calc checks the surrounding cells in all four directions (left,
right, above, below). The border is recognized if the program discovers an empty row or column, or
if it hits the left or upper border of the spreadsheet. This means that the described functions can
only work correctly if there are no empty rows or columns in the list. Avoid empty lines (for example
for formatting). You can format the list by using cell formats.
Note
To make sure that Calc automatically recognizes a list correctly, check that there are
no empty rows or empty columns within the list.
If you select more than one cell before you start sorting, filtering, or creating the pivot table, then
the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells
you have selected.
Tip
For sorting, filtering, or using the pivot table, always select only one cell to allow Calc
to determine the scope of the list.
A relatively common source of errors is to inadvertently declare a list by mistake and then to sort
that list. If you select multiple cells—for example, a whole column—then the sorting mixes up the
data that should be together in one row.
In addition to these formal aspects, the logical structure of the list is also very important.
Note
Calc lists must have the normal form; that is, they must have a simple linear structure.
When entering the data, do not add outlines, groups, or summaries. Here are some mistakes
commonly made by inexperienced spreadsheet users:
• You made several unnecessary sheets; for example, a sheet for each group of articles. In
this case, analyses are then possible only within each group.
• In a Sales list, instead of only one column for the amount, you made a column for the
amounts for each employee. In this case, the system will have difficulty grouping data from
Introduction | 5
the various columns together. Thus, an analysis with the pivot table would no longer be
possible. All data must be entered into the same column for the pivot table to be able to
analyze it.
• You entered the amounts in chronological order. At the end of each month, you made a
sum total. In this case, sorting the list for different criteria is not possible because the pivot
table will treat the sum totals the same as any other value. Getting monthly results is one of
the very fast and easy features of the pivot table.
Data sources
The possible data sources for the pivot table are a Calc spreadsheet or an external data source
that is registered in LibreOffice.
Calc spreadsheet
Analyzing a list in a Calc spreadsheet is the simplest and most often used case. Lists might be
updated regularly or the data might be imported from a different application.
The list data might be entered directly into the spreadsheet or copied from another file or
application. You can also use a Web Page Query input filter to insert data from a HTML file, a CSV
file, a Calc spreadsheet, or a Microsoft Excel spreadsheet. See Chapter 10, Linking Calc Data, for
more information.
The behavior of Calc while inserting data from a different application depends on the format of the
data. If the data is in a common spreadsheet format, it is copied directly into Calc. However, if the
data is in plain text format, the Text Import dialog appears after you select the file containing the
data; see Chapter 1, Introducing Calc, for more more information about this dialog.
Registered data source
A registered data source is a connection to data held in a database outside of LibreOffice. When
using a registered data source, the data to be analyzed will not be saved in Calc; Calc always uses
the data from the original source. Calc is able to use many different data sources in addition to
databases that are created and maintained with LibreOffice Base. For more information, see
Chapter 10, Linking Calc Data.
Using shortcuts
If you use pivot tables often in Calc, you might find the frequent use of the built-in menu paths
inconvenient.
In some cases built-in keyboard shortcuts are already defined; see Appendix A, Keyboard
Shortcuts. An example is the F12 function key, which groups a selected data range. In some other
cases, the built-in toolbars already provide relevant icons. An example is the Insert Pivot Table
icon on the Standard toolbar.
In addition to using the built-in keyboard shortcuts and toolbar icons, you can also define your own.
See Chapter 14, Setting Up and Customizing Calc, for instructions.
Creating a pivot table
If you want Calc to automatically determine the full extent of the raw data list, then select a single
cell within this list. If you want to explicitly define the extent of the raw data list then select all
relevant cells.
Create the pivot table by selecting Data > Pivot Table > Create from the Menu bar, selecting
Insert > Pivot Table from the Menu bar, or clicking the Insert Pivot Table icon in the Standard
toolbar.
6 | Creating a pivot table
Calc displays the Select Source dialog (Figure 1), where you can choose between using the
selected data cells, a range of cells that has already been named, or a data source that has
already been registered with LibreOffice.
Note
See Chapter 13, Calc as a Simple Database, for more information about named
ranges. See Chapter 10, Linking Calc Data, for more information about linking to
registered data sources.
Figure 1: Select Source dialog
Click OK on the Select Source dialog to display the Pivot Table Layout dialog, which is described in
the next section.
The Pivot Table Layout dialog
The function of the pivot table is managed in two places: first, in the Pivot Table Layout dialog; and
second, through manipulations of the result in the spreadsheet. This section describes the Pivot
Table Layout dialog in detail.
Tip
To access the Pivot Table Layout dialog again after initial creation of the pivot table,
right-click in the results area of the pivot table and select the Edit Layout option from
the context menu. Alternatively left-click in the results area and select the Insert Pivot
Table icon in the Standard toolbar.
The Pivot Table Layout dialog | 7
Basic layout
In the Pivot Table Layout dialog (Figure 2) are four areas that show the layout of the resulting pivot
table:
• Page Fields
• Column Fields
• Row Fields
• Data Fields
Beside these four areas is another area labeled Available Fields that contains the names of the
fields in the source data list. To choose a layout, drag and drop the fields from the Available Fields
area to the other four areas.
The Data Fields area must contain at least one field. Advanced users can use more than one field
here. For the fields in the Data Fields area, an aggregate function is used. For example, if you
move the Sales Value field into the Data Fields area, it initially appears there as Sum – Sales
Value.
Figure 2: Pivot Table Layout dialog
8 | Basic layout
Row and column fields indicate from which groups the result will be sorted. Often more than one
field is used at a time to get partial sums for rows or columns. The order of the fields gives the
order of the sums from overall to specific.
For example, if you drag Region and Employee into the Row Fields area, the sum will be divided
into the regions. Within the regions will be the listing for the different employees (Figure 3).
Figure 3: Field order for analysis and resulting layout of pivot table
Fields that are placed into the Page Fields area appear at the top of the resulting pivot table as a
drop-down list. The summary in the result takes into account only that part of the base data that
you have selected. For example, if you include Employee in the Page Fields area, you can filter
the result shown for each employee.
To move a field from an area, just drag it to a new area. To remove a field from the Page Fields,
Column Fields, Row Fields, or Data Fields areas, drag it to the Available Fields area.
Tip
To rapidly move a selected field from one area of the Pivot Table Layout dialog to
another, press the letter on the keyboard that corresponds to the underlined letter in
the target area’s label.
More options
To expand the Pivot Table Layout dialog and show more options, click the plus signs adjacent to
the Options and Source and Destination labels (Figure 4).
Ignore empty rows
If the source data is not in the recommended form, this option tells the pivot table to ignore
empty rows.
The Pivot Table Layout dialog | 9
Figure 4: Expanded area of the Pivot Table Layout dialog
Identify categories
With this option selected, if the source data has missing entries in a list and does not meet
the recommended data structure (as in Figure 5 for example), the pivot table adds it to the
listed category above it. If this option is not chosen, then the pivot table inserts (empty)
(Figure 7).
Figure 5: Example of data with missing
entries in Column A
The option Identify categories ensures that in this example rows 3 and 4 are included for
Apples and that row 6 is included for Pears (Figure 6).
Figure 6: Pivot table result with Identify categories selected
Without category recognition, the pivot table shows an (empty) category (Figure 7).
Figure 7: Pivot table result without Identify categories selected
10 | More options
Logically, the behavior with category recognition is better. A list showing missing entries is
also less useful, because you cannot use functions such as sorting or filtering.
Total columns, Total rows
With these options you can decide if the pivot table shows an extra row with the sums of
each column, or if it adds on the very right a column with the sums of each row. In some
cases, an added total sum is meaningless, for example if the entries are accumulated or the
result of comparisons.
Add filter
Use this option to add or hide the cell labeled Filter above the pivot table results. This
conveniently provides additional filtering options within the pivot table. For more information,
see “Filtering” on page 26.
Enable drill to details
With this option enabled, if you double-click on a single data cell in the pivot table result,
including a cell produced from Total columns or Total rows, a new sheet opens giving a
detailed listing of the individual entry. If you double-click on a cell in either a row or column
field area, then the Show Detail dialog opens (Figure 38). If this function is disabled, the
double-click will keep its usual edit function within a spreadsheet. For more information, see
“Drilling (showing details)” on page 25.
Source
The Selection field in this area shows the sheet name and the range of cells containing the
raw data for the pivot table. If the source spreadsheet contains any named ranges, these can
be selected through the Named range option.
Destination
The controls in this area define where the result will be shown.
Selecting New sheet adds a new sheet to the spreadsheet file and places the results there.
The new sheet is named using the format Pivot Table_sheetname_X; where X is the
number of the table created, 1 for first, 2 for second and so on. For a sheet named Sales
List, the new sheet for the first pivot table produced would be named Pivot Table_Sales
List_1. Each new sheet is inserted next to the source sheet.
If the target spreadsheet contains any named ranges, these can be selected with the Named
range option.
The Selection field in this area shows the sheet name and the range of cells for the pivot
table’s results.
Tip
To display the pivot table on the same sheet as the raw data, check the Selection
option in the Destination area, click in the Selection field, click the Shrink button to the
right of the Selection field, click at an appropriate cell in an empty area of the sheet,
click the Expand button, and click OK on the Pivot Table Layout dialog.
More settings for the fields: Field options
The options discussed in the previous section are valid for the pivot table in general. You can also
change settings for any field that is currently included in the pivot table layout (that is, fields within
the Page, Column, Row, and Data Fields areas). Change a field’s settings by double-clicking that
field within the Page, Column, Row, or Data Fields areas of the Pivot Table Layout dialog. Double-
clicking a field within the Available Fields area has no effect. The options available for fields in the
Data Fields area differ from those for fields in the other three areas.
The Pivot Table Layout dialog | 11
Options for data fields
Double-click a field in the Data Fields area of the Pivot Table Layout dialog to access the Data
Field dialog shown in Figure 8.
In the Data Field dialog, you can select the function to be used to accumulate the values from the
data source. While you often use the Sum function, other functions (like standard deviation or a
counting function) are also available. For example, the counting function can be useful for non-
numerical data fields.
Select the Show items without data option to include empty columns and rows in the results
table.
Click the expansion symbol (plus sign or triangle) to expand the Displayed value section of the
dialog.
Figure 8: Expanded dialog for a data field
In the Displayed value section, you can choose other possibilities for analysis using the aggregate
function. Depending on the setting for Type, you may have to choose definitions for Base field and
Base item.
Figure 9: Example choices for Base
field and Base item
12 | More settings for the fields: Field options
The table below lists the possible types of displayed value and associated base field and base
item, together with a note on usage.
Type Base field Base item Analysis
Normal — — Simple use of the chosen
aggregate function (for example,
Sum).
Difference
from
Selection of a field
from the data
source of the pivot
table (for example,
Employee).
Selection of an
element from the
selected base field
(for example, Brigitte)
The result is the difference between
the result of the base field and the
base item (for example, sales
volume of the other employees
against the sales volume of Brigitte;
see Figure 10).
If previous item or next item is
specified as the Base item, the
reference value is the result for the
next visible member of the base
field, in the base field’s sort order.
% of Selection of a field
from the data
source of the pivot
table (for example,
Employee)
Selection of an
element from the
selected base field
(for example, Brigitte)
The result is a percentage ratio of
the value of the base field to the
base item (for example, sales result
of the other employees relative to
the sales result of Brigitte; see
Figure 11).
If previous item or next item is
specified as the Base item, the
reference value is the result for the
next visible member of the base
field, in the base field’s sort order.
%
difference
from
Selection of a field
from the data
source of the pivot
table (for example,
Employee)
Selection of an
element from the
selected base field
(for example, Brigitte)
From each result, its reference
value is subtracted, and the
difference is divided by the
reference value (for example, sales
of the other employees as relative
difference from the sales of Brigitte;
see Figure 12).
If previous item or next item is
specified as the Base item, the
reference value is the result for the
next visible member of the base
field, in the base field’s sort order.
Running
total in
Selection of a field
from the data
source of the pivot
table (for example,
Date)
— Each result is added to the sum of
the results for preceding items in
the base field, in the base field’s
sort order, and the total sum is
shown.
Results are always summed, even
if a different summary function was
used to get each result.
% of row — — The result is a percentage of the
value of the whole row (for
example, the row sum).
The Pivot Table Layout dialog | 13
Type Base field Base item Analysis
% of
column
— — The result is a percentage of the
total column value (for example, the
column sum).
% of total — — The result is a percentage of the
overall result (for example, the total
sum).
Index — — (Default result x total result) / (row
total x column total)
Figure 10: Original pivot table (top) and a Difference from example (bottom)
Figure 11: Example of a % of analysis
Figure 12: Example of % difference from analysis
Options for row and column fields
Double-click a field in the Row or Column Fields areas of the Pivot Table Layout dialog to access
the Data Field dialog shown in Figure 13.
In the Data Field dialog for a row or column field, you can choose to show subtotals for each
category. Subtotals are deactivated by default. Subtotals are useful only if the values in one row or
column field can be divided into subtotals for another (sub)field.
14 | More settings for the fields: Field options
Figure 16: Division of the regions for employees with subtotals (by region)
To calculate subtotals that can also be used for the data fields (see above), select the Automatic
option in the Subtotals section of the Data Field dialog.
You can choose the type of subtotal to use by selecting User-defined and then clicking the type of
subtotal you want to calculate from the list. Functions in this list are only available when User-
defined is selected.
Normally, the pivot table does not show a row or column for categories that have no entries in the
underlying database. By choosing the Show items without data option, you can force these to be
displayed.
For illustration purposes, the data was manipulated in such a way that the employee Brigitte has
no sales values for the Golf category.
Figure 17: Default setting
16 | More settings for the fields: Field options
Figure 18: Setting Show items without data
Click the Options button on the Data Field dialog to access the Data Field Options dialog (Figure
19). Use this dialog to specify additional options for fields in the Column and Row Fields areas of
the Pivot Table Layout dialog.
Figure 19: Data Field Options dialog
The following options are provided:
• Sort by. Select the data field that you want to sort columns or rows by. Ascending sorts
the values from the lowest value to the highest value. If the selected field is the field for
which the dialog was opened, the items are sorted by name. If a data field was selected,
the items are sorted by the resultant value of the selected data field. Similarly Descending
sorts the values descending from the highest value to the lowest value. Manual sorts
values alphabetically.
• Display Options. You can specify the display options for all row fields except for the last,
innermost row field. Select from the Layout drop-down list to select the layout mode for the
field in the list box. Select the Empty line after each item option to add an empty row after
the data for each item in the pivot table. Select or deselect the Repeat item labels option
as required.
The Pivot Table Layout dialog | 17
• Show Automatically. This feature displays the top or bottom nn items when you sort by a
specified field. Click the Show option to turn on the automatic show feature and enter the
maximum number of items that you want to show automatically. The From drop-down list
selects to show the top or bottom items in the specified sort order. The Using field drop-
down list selects the data field by which to sort the data.
• Hide Items. Use these options to select the items to hide from the calculations.
• Hierarchy. Select the hierarchy to use. The pivot table must be based on external source
data that contains data hierarchies.
Options for page fields
The Data Field dialog for fields in the Page Fields area is the same as for fields in the Row and
Column Fields areas, even though it appears to be useless to have the same settings as described
for the row and column fields. With the flexibility of the pivot table, you can switch the different
fields between pages, columns, or rows. The fields keep the settings that you made for them. The
page field has the same properties as a row or column field. These settings only take effect when
you use the field not as a page field but as a row or column field.
Working with the results of the pivot table
The Pivot Table Layout dialog is very flexible and a pivot table can be totally restructured with only
a few mouse clicks.
Changing the layout
The layout of the pivot table can be changed quickly and easily by using drag-and-drop. With the
Pivot Table Layout dialog open, fields can be dragged around from Row, Column, Page, and Data
Fields areas to any position you want to put them, and then dropped. Unused fields can also be
added, and fields removed in error can be replaced by dragging and dropping them into the
positions required.
Some manipulation can also be carried out in the results view of the pivot table. Within the results
of the pivot table, drag one of the page, column, or row fields to a different position. The cursor will
change shape from its starting shape (horizontal or vertical block on the arrow head) to the
opposite if moving to a different field, such as from row to column, where it can be dropped.
Figure 20: Drag a column field - note the cursor shape
Figure 21: Drag a row field - note the cursor shape
You can remove a column, row, or page field from the pivot table by clicking on it and dragging it
out of the table. The cursor changes to that shown in Figure 22. A field removed in error cannot be
recovered, without returning to the Pivot Table Layout dialog to replace it.
18 | Changing the layout
Figure 22: Field dragged out of the pivot table
Grouping rows or columns
For many analyses or summaries, the categories have to be grouped. You can merge the results in
classes. You can only carry out grouping on an ungrouped pivot table.
After selecting the correct cell area, select Data > Group and Outline > Group from the Menu bar,
or press F12 on the keyboard. The type of values to be grouped is what mainly determines how the
grouping function works. You need to distinguish between scalar values, or other values, such as
text, that you want grouped.
Note
Before you can group, you have to produce a pivot table with ungrouped data. The
time needed for creating a pivot table depends mostly on the number of columns and
rows and not on the size of the basic data. Through grouping you can produce the
pivot table with a small number of rows and columns. The pivot table can contain a lot
of categories, depending on your data source.
To remove grouping again, click inside the group, then choose Data > Group and Outline >
Ungroup.
Grouping of categories with scalar values
For grouping scalar values, select a single cell in the row or column of the category to be grouped.
Choose Data > Group and Outline > Group from the Menu bar or press F12 on the keyboard;
Calc displays the Grouping dialog shown in Figure 23.
You can define in which value range (Start / End) the grouping should take place. The default
setting is the whole range, from the smallest to the largest value. In the field Group by, you can
enter the class size, also known as the interval size.
Figure 23: Grouping dialog with scalar categories
Working with the results of the pivot table | 19
Figure 24 shows part of a pivot table created from a list containing speed measurements as a
function of time. This pivot table shows the count of km/h speed measurements in the raw data.
The pivot table in Figure 25 is based on the same raw data. However in this case the speed
measurements are grouped into intervals if 10 km/h.
Figure 24: Pivot table
without grouping
Figure 25: Pivot table
with grouping
Grouping of categories with date / time values
For grouping date / time values, select a single cell in the row or column of the category to be
grouped. Choose Data > Group and Outline > Group from the Menu bar or press F12 on the
keyboard; Calc displays the Grouping dialog shown in Figure 26.
Figure 26: Grouping dialog for date/time categories
You can define in which value range (Start / End) the grouping should take place. The default
setting is the whole range, from the smallest to the largest value. In the field Group by, you can
enter the class size, also known as the interval size. In this case you can select one of the
predefined time intervals (Seconds, Minutes, Hours, Days, Months, Quarters, or Years) as an
alternative to explicitly specifying a time interval in days.
20 | Grouping rows or columns
Figure 27 shows a pivot table configured to show the daily sales in the North region.
Figure 27: Pivot table without grouping
Figure 28 shows the same data but configured to show the monthly sales in the North region.
Figure 28: Pivot table with grouping
Grouping without automatic creation of intervals
It is not possible to create intervals for some categories (for example, those containing text fields).
However, for such fields it is possible to define which values are put together in one group.
For grouping of these categories, select in the results of the pivot table all the individual field values
that you want to put together in the group. With more than one cell selected, choose Data > Group
and Outline > Group from the Menu bar, or press F12 on the keyboard, to group the selected
cells.
Working with the results of the pivot table | 21
Tip
You can select several non-contiguous cells by pressing and holding the Control key
while clicking with the mouse.
Given the input data shown in Figure 29, create a pivot table with Department in the Row Fields
area and Sum - Sick days in the Data Fields area. The result should be as shown in Figure 30.
Figure 29: Database with text categories
Figure 30: Pivot table with text categories
In the results of the pivot table select Accounting, Purchasing, and Sales in the Department
column. Select Data > Group and Outline > Group from the Menu bar or press F12 on the
keyboard. The pivot table result updates to reflect the new group, as shown in Figure 31.
Figure 31: Summary of single categories in one group
In the updated pivot table result, select Assembly, Production, and Warehouse in the Department
column. Select Data > Group and Outline > Group from the Menu bar or press F12 on the
keyboard. The pivot table updates again to reflect the new group, as shown in Figure 32.
You can change the default names for the groups and the newly created group field by editing the
name in the input field (for example changing Group2 to Technical). The pivot table will remember
these settings, even if you change the layout later on.
22 | Grouping rows or columns
Figure 32: Grouping finished
To add partial sums for the groups, right-click the results of the pivot table and select the Edit
Layout option. Double-click the Department2 entry in the Row Fields area and select the
Automatic option on the Data Field dialog (Figure 13). Click the two OK buttons and the pivot
table is updated to include the partial sums for the groups, as shown in Figure 33.
Figure 33: Renamed group and partial results
If it is not enabled already, select the Enable drill to details option on the Pivot Table Layout
dialog. Double-click the Group 1 and Technical entries in the Department2 column to
collapse/expand the group entries (for example, Figure 34 shows both groups collapsed).
Figure 34: Reduced to the new groups
Note
A well-structured database makes manual sorting within the pivot table obsolete. In the
example shown, you could add another column with the name Department, that has
the correct entry for each person based on whether the employee’s department
belongs to the group Office or Technical. The mapping for this (1:n relationship) can be
done easily with the VLOOKUP() function from Calc.
Sorting the result
The results of a pivot table are by default sorted so that categories in columns and rows are
presented in ascending order. There are three ways to change the sorting order:
• Select a sort order from the drop-down menu on a column’s heading.
• Sort manually by using drag and drop.
• Select a sort order through the Data Field Options dialog for the appropriate row or column
field (Figure 19).
Working with the results of the pivot table | 23
Select sort order from drop-down menus on each column heading
The simplest way to sort entries is to click the arrow on the right side of the column heading for a
row or column field, and select one of the three sorting options (Figure 35):
• Sort Ascending
• Sort Descending
• Custom Sort
Figure 35: Column sorting and
filtering dialog
Selecting the Custom Sort option sorts according to one of the predefined custom sorts defined in
Tools > Options > LibreOffice Calc > Sort Lists. See Chapter 2, Entering, Editing, and
Formatting Data for more information about creating and using sort lists.
This dialog also provides facilities for simple filtering of the data in the pivot table. Check the
required individual boxes to select the data displayed in the results of the pivot table. Options are
provided to show all, show only the current item, or hide only the current item. Click OK to activate
the selected filtering. Once filtering has been carried out, the color of the arrow changes from black
to blue, and a small square of matching color is added to the bottom right of the arrow button
(Figure 36).
Figure 36: Arrow color change and indicator square on column heading
Sort manually by using drag and drop
You can change the order within the categories by moving the cells with the category values in the
results of the pivot table. The dragged cell is inserted above the cell on which you drop it.
24 | Sorting the result
Be aware that in Calc, a cell must be selected, it is not enough that the cursor is in the cell. The
background of a selected cell is marked with a different color. To mark a single cell do one of the
following:
• Click, then Shift+click the cell.
• Press the mouse button, drag a range across two cells, do not release the mouse button,
and then drag back to the first cell. Release the mouse button. You can now move the
individual cell by drag and drop.
To select several cells, mark one cell with no extra key pressed, then press the Shift or Ctrl key
while clicking in other cells.
Sort automatically
To sort automatically, right-click within the pivot table and choose Edit Layout. This will open the
Pivot Table Layout dialog (Figure 2). Double-click the row or column field you want to sort. In the
Data Field dialog which opens (Figure 13), click Options to display the Data Field Options dialog
(Figure 19).
For Sort by, choose either Ascending, Descending, or Manual. Ascending sorts the values from
the lowest value to the highest value. If the selected field is the field for which the dialog was
opened, the items are sorted by name. If a data field was selected, the items are sorted by the
resultant value of the selected data field. Similarly Descending sorts the values descending from
the highest value to the lowest value. Manual sorts values alphabetically.
Drilling (showing details)
Use Drill to show the related detailed data for a single, compressed value in the pivot table result.
This facility is available only if you selected the Enable drill to details option on the Pivot Table
Layout dialog.
To activate a drill, double-click on the cell or choose Data > Group and Outline > Show Details.
There are two possibilities:
• The active cell is a row or column field. In this case, drill means an additional breakdown
into the categories of another field. For example, double-click on the cell with the value
Golf. In this instance the values that are aggregated within Golf can be subdivided using
another field.
Figure 37: Before the drill down for Golf
The Show Detail dialog appears allowing you to select the field to use for further
subdivision. In this example, Employee.
Figure 38: Selecting the field for the subdivision
Working with the results of the pivot table | 25
Figure 39: After the drill down
To hide the details again, double-click on the cell Golf or choose Data > Group and
Outline > Hide Details.
The pivot table remembers your selection (in our example the field Employee) by adding
and hiding the selected field, so that for the next drill down for a field in the Category
column the dialog does not appear. To remove the selection Employee, open the Pivot
Table Layout dialog by right-clicking and choosing Edit Layout, then delete the unwanted
selection in the Row or Column Fields area.
• The active cell is a data field. In this case, drill down results in a listing of all data entries of
the data source that aggregate to this value.
In our example, if we double-click on the cell with the value $18,741 from Figure 37, we
would have a new list of all data sets that are included in this value. This list is displayed in
a new sheet.
Figure 40: New sheet after the drill down for a value in a data field
Filtering
To limit the pivot table analysis to a subset of the information that is contained in the data basis,
you can filter with the pivot table.
Note
An autofilter or default filter used on the sheet containing the raw data has no effect on
the pivot table analysis process. The pivot table always uses the complete list that was
selected when it was started.
To do this, click the Filter button at the top left above the results, or click in the results and select
Filter from the context menu.
Note
The Filter button is available only if you selected the Add filter option on the Pivot
Table Layout dialog.
26 | Filtering
Figure 41: Filter field in the upper left area of the pivot table
In the Filter dialog (Figure 42), you can define up to three filter options that are used in the same
way as Calc’s default filter.
Figure 42: Dialog for defining the filter
The data presented in a pivot table can also be filtered using the drop-downs on the right hand side
of column headings or by using page fields. Filtering through column headings is described in
“Select sort order from drop-down menus on each column heading” on page 24.
Even if they are not called a filter, page fields are another practical way to filter the results of the
pivot table. The advantage is that the filtering criteria used are clearly visible. Click the arrow on the
right side of the page field button to access the associated filtering dialog (Figure 43).
Figure 43: Page field filtering dialog
Working with the results of the pivot table | 27
The text adjacent to a page field button indicates the filtering status, that is “- all -” when nothing is
filtered, "- multiple -" when multiple but not all items are filtered, or the value when only that value is
not filtered.
Updating (refreshing) changed values
After you have created the pivot table, changes in the source data do not cause an automatic
update in the resulting table. You must update (refresh) the pivot table manually after changing any
of the underlying data values.
Changes in the source data could appear in two ways:
• The content of existing data sets has been changed.
For example, you might have changed a sales value. To update the pivot table, click in the
result area and choose Refresh from the context menu, or choose Data > Pivot Table >
Refresh from the Menu bar.
• You have added or deleted data sets in the original list.
In this case the change means that the pivot table has to use a different area of the
spreadsheet for its analysis. Fundamental changes to the data set collection means you
must redo the pivot table from the beginning.
Cell formatting
The cells in the results area of the pivot table are automatically formatted by Calc. You can change
this formatting using all the tools in Calc. However if you make any change in the design of the
pivot table using direct formatting, the formatting will return to that applied automatically by Calc
when the table is next refreshed.
On creating a pivot table, six standard cell styles are added to the list of styles in the document if
they are not included already. Each of these styles is applied to part of the pivot table. You can
customize these pivot table styles. The pivot table styles are:
• Pivot Table Category
• Pivot Table Corner
• Pivot Table Field
• Pivot Table Result
• Pivot Table Title
• Pivot Table Value
Tip
Use the pivot table styles to make sure that the format of your pivot table is not
unexpectedly changed during updates and that all pivot tables in your document have
the same appearance.
Note
Direct formatting of pivot table cells is not retained during updates.
For the number format in the data field, Calc uses the number format that is used in the
corresponding cell in the source list. In most cases, this is useful (for example, if the values are in
the currency format, then the corresponding cell in the result area is also formatted as currency).
However, if the result is a fraction or a percentage, the pivot table does not recognize that this
might be a problem; such results must either be without a unit or be displayed as a percentage.
Although you can correct the number format manually, the correction stays in effect only until the
next update.
28 | Cell formatting
Deleting a pivot table
To delete a pivot table, click in the result area and choose Delete from the context menu, or
choose Data > Pivot Table > Delete from the Menu bar.
Warning
If you delete a pivot table with an associated pivot chart, the pivot chart is also deleted.
Calc opens a dialog box to confirm the pivot chart deletion.
Using pivot table results elsewhere
The problem
Normally, you create a reference to a value by entering the address of the cell that contains the
value. For example, the formula =C6*2 creates a reference to cell C6 and returns the doubled
value. If this cell is located in the results area of the pivot table, it contains the result that was
calculated by referencing specific categories of the row and column fields. In Figure 44, the cell C6
contains the sum of the sales values of the employee Hans in the category Sailing. The formula in
the cell C12 uses this value.
Figure 44: Formula reference to a cell of the pivot table
If the underlying data or the layout of the pivot table changes, then you must take into account that
the sales value for Hans in the Sailing category might appear in a different cell. Your formula still
references the cell C6 and therefore uses a wrong value. The correct value is in a different
location. For example, in Figure 45, the location is now C7.
Figure 45: The value that you really want to use can be found
now in a different location
Using pivot table results elsewhere | 29
The solution: Function GETPIVOTDATA()
Use the function GETPIVOTDATA() to have a reference to a value inside the pivot table by using
the specific identifying categories for this value. This function can be used with formulas in Calc if
you want to reuse the results from the pivot table elsewhere in your spreadsheet.
Syntax
The syntax has two variations:
GETPIVOTDATA(target field, Pivot Table, [ Field name / Element, ... ])
GETPIVOTDATA(Pivot Table, specification)
First syntax variation
The target field specifies which data field of the pivot table is used within the function. If your pivot
table has only one data field, this entry is ignored, but you must enter it anyway.
If your pivot table has more than one data field, then you have to enter the field name from the
underlying data source (for example “Sales Value”) or the field name of the data field itself (for
example “Sum – Sales Value”).
The argument Pivot Table specifies the pivot table that you want to use. It is possible that your
document contains more than one pivot table. Enter here a cell reference that is inside the area of
your pivot table. It might be a good idea to always use the upper left corner cell of your pivot table,
so that you can be sure that the cell will always be within your pivot table, even if the layout
changes.
Example: GETPIVOTDATA("Sales Value",A1)
If you enter only the first two arguments, then the function returns the total result of the pivot table
(“Sum – Sales Value” entered as the field, will return a value of 408,765).
You can add more arguments as pairs with Field name and Element to retrieve specific partial
sums. In the example in Figure 46, where we want to get the partial sum of Hans for Sailing, the
formula in cell C12 would look like this:
=GETPIVOTDATA("Sales Value",A1,"Employee","Hans","Category","Sailing")
Figure 46: First syntax variation
Second syntax variation
The argument Pivot Table has to be given in the same way as for the first syntax variation.
For the specification, enter a list separated by spaces to specify the value you want from the pivot
table. This list must contain the name of the data field, if there is more than one data field;
otherwise it is not required. To select a specific partial result, add more entries in the form of Field
name[Element].
30 | The solution: Function GETPIVOTDATA()
In the example in Figure 47, where we want to get the partial sum of Hans for Sailing, the formula
in cell C12 would look like this:
=GETPIVOTDATA(A1,"Sales Value Employee[Hans] Category[Sailing]")
Figure 47: Second syntax variation
When working with data sets containing date information, you must take care if you use the date
information in the GETPIVOTDATA() function. The function will only recognize the date entry if it is
entered into the formula in exactly the same way that it appears in the data set from which the pivot
table is produced. In the example of Figure 48, an error is returned when the date format does not
match that of the data. Only with the correct format is the result returned.
Figure 48: Error produced if date information is not entered correctly
Using pivot charts
Introduction
A pivot table is a powerful tool to reorganize, manipulate and summarize data. A pivot chart
provides a visual representation of the information in a pivot table. You can create a pivot chart
from the output of a pivot table and, if the pivot table gets changed, so does the pivot chart.
Pivot charts are a special case of the more general Calc charts described in Chapter 3, Creating
Charts and Graphs. The main differences between pivot charts and other charts in Calc are as
follows:
Using pivot charts | 31
• A pivot chart tracks the changes in the data issued from a pivot table and Calc
automatically adjusts the data series and data range of the pivot chart accordingly.
• Pivot chart buttons are provided on pivot charts. These buttons are unique to pivot charts
and are not provided on Calc’s normal charts. An important purpose of the buttons is to
represent the layout of the underlying pivot table and they show the pivot table’s fields.
Buttons representing the page fields (if present) are provided at the top of the pivot chart.
Buttons representing the row fields are provided at the bottom of the pivot chart. Buttons
representing the column fields are stacked in the legend at the right of the pivot chart. You
can also use pivot chart buttons to filter the data displayed in the pivot chart.
Figure 49: Sample pivot chart and associated pivot table
32 | Introduction
Creating a pivot chart
To create a pivot chart, click inside the pivot table and select Insert > Chart on the Menu bar or
click the Insert Chart icon on the Standard toolbar.
Calc automatically detects the pivot table and opens the chart wizard. Through the chart wizard
you can select the chart type and chart elements for the pivot chart. The wizard is similar to the
corresponding wizard for normal charts but for pivot charts, the steps to define data range and data
series are disabled.
The first step in the wizard is to select the chart type and the same options are available as for a
normal chart (Figure 50).
Figure 50: Select the chart type through the chart wizard when creating a pivot chart
The second step is to select the chart elements and these are similar to those for normal charts
(Figure 51).
Figure 51: Enter chart elements through the chart wizard when creating a pivot chart
Click Finish to close the wizard and create the pivot chart.
Using pivot charts | 33
Editing a pivot chart
After you have created a pivot chart, you may find that you would like to move it, change its size or
improve its appearance. Calc provides tools for changing the chart type, chart elements, fonts,
colors, and many other options. The facilities provided for pivot charts are the same as those
available for normal charts; see Chapter 3, Creating Charts and Graphs.
Updating a pivot chart
If the source data of the pivot table changes, refresh the pivot table and the pivot chart is also
updated accordingly. To refresh the pivot table (and thus the pivot chart), click in any cell within the
pivot table and select Data > Pivot Table > Refresh from the Menu bar or select Refresh from the
context menu.
Filtering a pivot chart
Use filters to remove unwanted data from a pivot chart.
All filters applied to a pivot table affect any linked pivot chart equally, since the table and chart
show exactly the same data. The various pivot table filtering mechanisms are described in
“Filtering” on page 26.
Pivot chart buttons include a down arrow to indicate that there is an associated pop-up action. If
any filtering is applied, then this arrow turns from black to blue.
Click a page field button located at the top of a pivot chart to access a filtering dialog similar to that
shown in Figure 43. Change the filtering applied to the pivot table and pivot chart through this
dialog. The right-hand section of the button’s legend indicates the filtering status, that is “- all -”
when nothing is filtered, "- multiple -" when multiple but not all items are filtered, or the value when
only that value is not filtered.
Buttons located at the bottom and right of a pivot chart that include a downward pointing arrow
provide access to a sorting and filtering dialog similar to the shown in Figure 35. Change the
sorting and filtering applied to the pivot table and pivot chart through this dialog.
Figure 52: Filtering applied to page and row fields
34 | Filtering a pivot chart
Deleting a pivot chart
To delete a pivot chart, select the chart and press Del on the keyboard.
Note
When you delete a pivot chart, the associated pivot table is not affected.
Warning
If you delete a pivot table with an associated pivot chart, the pivot chart is also deleted.
Calc opens a dialog box to confirm the pivot chart deletion.
Using pivot charts | 35
