Introduction
Many requests for spreadsheet support are the result of using complicated formulas and solutions
to solve simple day-to-day problems. 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 or an intermediate or advanced user.
Database preconditions
The first thing needed to work with the Pivot Table is 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, the program needs to know where in the spreadsheet the table is. The
table can be anywhere in the sheet, in any position. A spreadsheet can contains several unrelated
tables.
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 4 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 your list. Avoid empty lines (for example for formatting). You can format your list by
using cell formats.
Rule
No empty rows or empty columns are allowed within lists.
If you select more than one single cell before you start sorting, filtering, or calling the Pivot Table,
then the automatic list recognition is switched off. Calc assumes that the list matches exactly the
cells you have selected.
Rule
For sorting, filtering, or using the Pivot Table, always select only one cell.
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 your table is also very important.
Rule
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:
1) 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.
2) 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
Introduction 4