Chapter 8 Using Pivot Tables

Calc Guide 7.2

Chapter 8

Using Pivot Tables

listed below. You may distribute it and/or modify it under the terms of either the GNU General

Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative

Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

Steve Fanning Felipe Viggiano

To previous editions

Barbara Duprey Martin J Fox Jean Hollis Weber

John A Smith Klaus-Jürgen Weghorn Kees Kriek

Steve Fanning Leo Moons Felipe Viggiano

Stefan Weigel Vasudev Narayanan

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 November 2021. Based on LibreOffice 7.2 Community.

Other versions of LibreOffice may differ in appearance and functionality.

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 document.

For a more detailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this

guide.

Windows or Linux macOS equivalent Effect

Tools > Options

menu selection

LibreOffice > Preferences Access setup options

Right-click Control+click and/or right-click

depending on computer setup

Open a context menu

Ctrl (Control) ⌘ (Command) Used with other keys

F11 ⌘+T Open the Styles deck in the

Sidebar

Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/

Contents
Copyright............................................................................................................................2
Contributors.............................................................................................................................. 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............................................................................................................................... 7
More options............................................................................................................................. 9
More settings for the fields: Field options................................................................................11
Options for data fields.........................................................................................................11
Options for row and column fields......................................................................................14
Options for filter fields.........................................................................................................18
Working with the results of the pivot table...................................................................18
Changing the layout................................................................................................................ 18
Grouping rows or columns of a pivot table..............................................................................19
Grouping of categories with scalar values..........................................................................20
Grouping of categories with date / time values...................................................................21
Grouping without automatic creation of intervals................................................................22
Sorting the result.....................................................................................................................24
Select sort order from drop-down menus on each column heading....................................25
Sort manually by using drag and drop................................................................................26
Sort automatically............................................................................................................... 26
Drilling (showing details).........................................................................................................26
Filtering................................................................................................................................... 27
Updating (refreshing) changed values.....................................................................................29
Cell formatting......................................................................................................................... 29
Deleting a pivot table............................................................................................................... 30
Using pivot table results elsewhere..............................................................................30
The problem............................................................................................................................ 30
The solution: Function GETPIVOTDATA()...............................................................................31
Syntax................................................................................................................................ 31
First syntax variation.......................................................................................................... 31
Second syntax variation.....................................................................................................32
Using pivot charts...........................................................................................................33
Introduction............................................................................................................................. 33
Creating a pivot chart..............................................................................................................34
Editing a pivot chart................................................................................................................. 35
Chapter 8 Using Pivot Tables | 3

Updating a pivot chart.............................................................................................................35

Filtering a pivot chart...............................................................................................................35

Deleting a pivot chart.............................................................................................................. 36

4 | Chapter 8 Using 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, analyzing, and summarizing 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, or

an advanced user. Besides, 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.

Tip

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 creating a pivot table, then Calc’s automatic list

recognition logic is not applied. Instead, Calc assumes that the pivot table is to be created using

exactly the cells that you selected.

Tip

Always select only one cell before initiating creation of a pivot table. This allows Calc

to automatically determine the full scope of your data 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 only possible within each group.

Introduction | 5

• 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 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 an HTML file, a

CSV file, a Calc spreadsheet, or a Microsoft Excel spreadsheet. See Chapter 10, Linking 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, Introduction, for 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 the spreadsheet;

Calc will always use the data from the original source. Calc can use many different data sources

in addition to databases that are created and maintained with LibreOffice Base. For more

information, see Chapter 10, Linking 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 or Edit

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, 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.

With the cell (or cells) selected, create the pivot table by selecting Insert > Pivot Table on the

Menu bar, selecting Data > Pivot Table > Insert or Edit on the Menu bar, or click the Insert or

Edit Pivot Table icon on the Standard toolbar.

6 | Chapter 8 Using Pivot Tables

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 Database, for more information about named ranges. See

Chapter 10, Linking 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 a pivot table,

left-click in any cell of the pivot table. Then select Insert > Pivot Table on the Menu

bar, or select Data > Pivot Table > Insert or Edit on the Menu bar, or click the

Insert or Edit Pivot Table icon on the Standard toolbar, or right-click in any cell of

the pivot table and select the Properties option in the context menu.

Basic layout

In the Pivot Table Layout dialog (Figure 2) there are four areas that show the layout of the

resulting pivot table:

• Filters

• Column Fields

• Row Fields

• Data Fields

Besides these four areas is another area labeled Available Fields containing 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 Pivot Table Layout dialog | 7

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

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).

Fields that are placed into the Filters 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 Filters 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 Filters,

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 Alt+letter on the keyboard that corresponds to the underlined letter

in the target area’s label.

8 | Chapter 8 Using Pivot Tables

Figure 3: Field order for analysis and resulting layout of pivot table

Note

By default, Calc inserts a Data field into the Column Fields area. The Data field can

be moved between the Column Fields and Row Fields areas as required. Depending

on its position within the list of fields in its area, the Data field may lead to a button

labeled Data appearing in the results of the pivot table, affecting the layout of the

results. If you do not wish to use this facility, simply place the Data field at the bottom

of the list of fields in its area.

More options

To expand the Pivot Table Layout dialog and show more options, click the expansion symbol

(plus or triangle sign) 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.

Figure 4: Expanded area of the Pivot Table Layout dialog

The Pivot Table Layout dialog | 9

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 5: Example of data with missing

entries (blank/empty values) 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

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 27.

Note

The filtering provided through the Add filter option is independent of the filtering

provided by including fields in the Filters area of the Pivot Table Layout dialog.

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

10 | Chapter 8 Using Pivot Tables

detailed listing of the individual entry. If you double-click on a cell in either a row or column

field area, the Show Detail dialog opens (Figure 39). 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 26.

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 the 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 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. Change a

field’s settings by double-clicking that field within the Filters, Column Fields, Row Fields, 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.

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.

The Pivot Table Layout dialog | 11

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

the Base field and Base item.

Figure 9: Example choices for Base

field and Base item

Table 1 lists the possible types of displayed value and associated base field and base item,

together with notes on usage.

Table 1: Description of Displayed value options on the Data Field dialog

Type Base field Base item Analysis

Normal — —

Simple use of the chosen aggregate

function (for example, Sum).

12 | Chapter 8 Using Pivot Tables

Type Base field Base item Analysis

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 a 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

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).

% 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)

The Pivot Table Layout dialog | 13

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

Figure 13: Data Field dialog for a row or

column field

14 | Chapter 8 Using Pivot Tables

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.

Some examples are shown in Figures 14, 15, and 16.

Figure 14: No subdivision with only one row or column field

Figure 15: Division of the regions for employees (two row fields) without subtotals

The Pivot Table Layout dialog | 15

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 | Chapter 8 Using Pivot Tables

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

The Pivot Table Layout dialog | 17

row after the data for each item in the pivot table. Select or deselect the Repeat item

labels option as required.

• 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 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, when the field has multiple hierarchies. The pivot

table must be based on external source data that contains data hierarchies. For the

majority of users, Calc does not provide multiple hierarchies for a single field and so this

option is normally grayed. If you use a pivot table data source extension, that extension

could define multiple hierarchies for some fields and then the option could become

available. See the documentation supplied with that extension for more details.

Options for filter fields

The Data Field dialog for fields in the Filters 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 filters, columns, or rows. The fields keep the settings that you gave them.

The filter field has the same properties as a row or column field. These settings only take effect

when you use the field not as a filter 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 Fields, Column Fields,

Filters, 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 filters, 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

18 | Chapter 8 Using Pivot Tables

Figure 21: Result of dragging column field

(see Figure 20)

Figure 22: Drag a row field - note the cursor

shape

You can remove a column, row, or filter field from the pivot table by clicking on it and dragging it

out of the table. The cursor changes to that shown in Figure 23. A field removed cannot be

recovered, without returning to the Pivot Table Layout dialog to replace it.

Figure 23: Field dragged out of the pivot table

Grouping rows or columns of a pivot table

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 on 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.

Working with the results of the pivot table | 19

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, or press Ctrl+F12.

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 on the Menu bar or press F12 on the

keyboard; Calc displays the Grouping dialog shown in Figure 24.

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 24: Grouping dialog with scalar

categories

In the results of the pivot table select Accounting, Purchasing, and Sales in the Department

column. Select Data > Group and Outline > Group on the Menu bar or press F12 on the

keyboard. The pivot table result updates to reflect the new group, as shown in Figure 32.

Figure 32: 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 on the Menu bar or press F12 on the

keyboard. The pivot table updates again to reflect the new group, as shown in Figure 33.

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.

Working with the results of the pivot table | 23

Figure 33: Grouping finished

To add partial sums for the groups, right-click the results of the pivot table and select the

Properties 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 34.

Figure 34: 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 35 shows both groups collapsed).

Figure 35: 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.

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 in 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).

24 | Chapter 8 Using Pivot Tables

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 36):

• Sort Ascending

• Sort Descending

• Custom Sort

Figure 36: 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

to white on a blue background, and a small white square is added to the bottom right of the arrow

button (Figure 37).

Figure 37: Arrow color change and indicator square on column heading

Working with the results of the pivot table | 25

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 before the cell on which you drop it.

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 Properties. 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. 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. Ascending sorts the values

from the lowest value to the highest value. Similarly Descending sorts the values descending

from the highest value to the lowest value. Manual sorts values alphabetically.

Drilling (showing details)

Use drilling 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 38: 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.

26 | Chapter 8 Using Pivot Tables

Figure 39: Selecting the field for the

subdivision

Figure 40: 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 and 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 38, 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 41: 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 the pivot table results.

Working with the results of the pivot table | 27

Note

An AutoFilter or Standard 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 right-click in the results and

select Filter in the context menu.

Note

The Filter button is available only if the Add filter option on the Pivot Table Layout

dialog is selected.

Figure 42: Filter button in the upper left area of the pivot table

In the Filter dialog (Figure 43), you can define up to three filter options that are used in the same

way as Calc’s Standard Filter. The controls in the Options section of this dialog are similar to the

equivalent controls on Calc’s Standard Filter dialog – see Chapter 2, Entering, Editing, and

Formatting Data for more information.

Figure 43: 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 filter fields. Filtering through column headings is described in

“Select sort order from drop-down menus on each column heading” on page 25.

Filter fields (that is, fields that you placed in the Filters area of the Pivot Table Layout dialog) 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 filter field button to access

the associated filtering dialog (Figure 44).

28 | Chapter 8 Using Pivot Tables

Figure 44: Filter field filtering dialog

The text adjacent to a filter 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, left-click

in the results area and choose Data > Pivot Table > Refresh on the Menu bar, or right-

click in the results area and choose Refresh in the context menu.

• 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. If a simple addition to the list was made (for example, a new

sale from a different employee was inserted), it is possible to update the pivot table by

changing the range in the Selection field in the Source area of the Pivot Table Layout

dialog. In the case of more complex changes to the data set collection, 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

Working with the results of the pivot table | 29

• 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.

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 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.

Deleting a pivot table

To delete a pivot table, left-click in any cell of the pivot table and select Data > Pivot Table >

Delete on the Menu bar, or right-click in any cell of the pivot table and select Delete in the

context menu.

Caution

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 45, cell C6

contains the sum of the sales values of the employee Hans in the category Sailing. The formula

in cell C12 uses this value.

Figure 45: Formula reference to a cell of the pivot table

30 | Chapter 8 Using Pivot Tables

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 46, the location is now C7.

Figure 46: The value that you really want to use can be found

now in a different location

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 1; Item 1][; ...

[Field 126; Item 126]])

=GETPIVOTDATA(Pivot Table; Constraints)

The square brackets in the first variation surround optional arguments.

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. Your document may

contain 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 names and Elements to retrieve specific partial

sums. 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:

Using pivot table results elsewhere | 31

=GETPIVOTDATA("Sales

Value",A1,"Employee","Hans","Category","Sailing")

Figure 47: 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 Constraints, 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].

In the example in Figure 48, 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 48: 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 49, an error is returned when the date format

does not match that of the data. Only with the correct format is the result returned.

32 | Chapter 8 Using Pivot Tables

Figure 49: Error produced if date information is not entered correctly

Using pivot charts

Introduction

Figure 50: Sample pivot chart and associated pivot table

Using pivot charts | 33

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:

• 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 filter 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.

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 51).

Figure 51: 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 52).

34 | Chapter 8 Using Pivot Tables

Figure 52: Enter chart elements through the Chart Wizard when creating a pivot chart

Click Finish to close the wizard and create the pivot chart.

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 on the Menu bar or select Refresh in

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 27.

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. Pivot chart buttons become

operational when you double-click on the chart and a gray frame appears around the chart. This

action puts the pivot chart into edit mode.

Click a filter field button located at the top of a pivot chart to access a filtering dialog similar to

that shown in Figure 44. 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 one shown in Figure 36. Change the

sorting and filtering applied to the pivot table and pivot chart through this dialog.

Using pivot charts | 35

Figure 53: Filtering applied to filter and row fields

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.

Caution

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 table deletion.

36 | Chapter 8 Using Pivot Tables