DataPilot functions in detail

From Apache OpenOffice Wiki
< Documentation‎ | OOo3 User Guides‎ | Calc Guide
Revision as of 12:15, 9 October 2023 by DiGro (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This part describes the use and options of the DataPilot in detail.

The database (preconditions)

The basis for work with the DataPilot 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 DataPilot.

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 contain several unrelated tables.

Calc recognizes your lists automatically. It uses the following logic:

Starting from the cell you’ve selected (which must be within your 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.

Documentation caution.png 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 DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected.

Documentation caution.png For sorting, filtering or using the DataPilot, always select only one cell.

A relatively big source for errors is that you might declare a list by mistake and you sort your 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 very important when using the DataPilot.

Documentation caution.png 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. This becomes clear when we think about what we could have done wrong in our Sales list example in the section Sales volume overview. Here are some mistakes commonly made by inexperienced spreadsheet users:

  1. You could have made several sheets, for example, a sheet for each group of articles. Analyses are then possible only within each group. Analyses for several groups would be a lot of work.
  2. In the Sales list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
  3. You could have entered the amounts in chronological order. At the end of each month you could have made a sum total. In this case, sorting the list for different criteria is not possible because the DataPilot will treat the sum totals the same as any other figure. Getting monthly results is one of the very fast and easy features of the DataPilot.


You start the DataPilot with Data → DataPilot → Start. If the list to be analyzed is in a spreadsheet table, select one cell within this list. Calc recognizes and selects the list automatically for use with the DataPilot.

After the Start of the DataPilot
Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA).
Personal tools