The Database

Apache OpenOffice 4.1 Calc Guide

Chapter 8: Using Pivot Tables

< Previous Section

Next Section >

• Introduction
• Examples with Step-by-Step Descriptions
  • Example 1: Sales Volume Overview
    • Practical Problems and Questions
    • Solution
    • Advantages
  • Example 2: Timekeeping
    • Practical Problems and Questions
    • Solution
    • Differences and Advantages
  • Example 3: Frequency Distribution
    • Solution with an Array Formula
    • Solution with a Pivot Table
• Functions in Detail
  • The Database
  • Start
  • Data Sources
    • Calc Spreadsheet
    • Registered Data Source
  • The Pivot Table Dialog
    • Basic Layout
    • More Options
    • More Settings for the Fields – Field Options
    • Data Fields
    • Row and Column Fields
    • Page Fields
  • Working with the Results of the Pivot Table
    • Start the Dialog
    • Change Layout by Using Drag and Drop
    • Grouping Rows or Columns
    • Grouping of Categories with Scalar Values
    • Grouping of Categories with Date or Time Values
    • Grouping without the Automatic Creation of Intervals
    • Sorting the Result
    • Drilling (Showing Details)
    • Filtering
    • Updating or Refreshing Changed Values
    • Cell Formatting
  • Multiple Data Fields
  • Shortcuts
• Function GETPIVOTDATA
  • Difficulty
  • Syntax
    • First Syntax Variation
    • Second Syntax Variation

< Previous Page

Next Page >

The Database

The first thing we need to have on hand for a Pivot Table is a set of raw data, similar to a database table. Such a set should comprise rows (AKA records) and columns (AKA fields). Moreover, field names must be in the first row of any such list.

Almost anything can serve as a data source – for instance, one can use an external file. In many cases, it is most convenient to use the raw data in the file that will contain the Pivot Table. For processing lists of data, Calc and its Pivot Tables need to know where the list is in the spreadsheet. That can be anywhere in the sheet, in any position; a spreadsheet can even contain several unrelated lists.

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

• You must begin your effort by selecting a cell that is within the range of the items in your list.
• Starting from that cell, Calc checks the surrounding cells in all four directions (left, right, above, below). A border is recognized if the program discovers an empty row or column or if it hits the spreadsheet's first or last row or column.
• This means that the Pivot Table's functions can only work correctly if there are no empty rows or columns in your list. Avoid these; if you want to distinguish regions of the data, format your list by using cell formats.

Again, be careful - no empty rows or empty columns are allowed within lists.

If you select more than one single cell before creating the Pivot Table, the automatic list recognition is disabled. Calc assumes that the list exactly matches the cells you have selected.

When using the Pivot Table, always select only one cell or carefully select the entire data set. Failing to do so may result in empty rows or columns.

In addition to these formal aspects, logical structure is very important when using Pivot Tables. When entering data, don't add outlines, groups, or summaries. Here are some mistakes commonly made by inexperienced spreadsheet users:

1. You 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 created a column for each employee's amounts. The amounts had to be entered manually into the appropriate column, making an analysis with a Pivot Table impossible. In contrast, one benefit of a Pivot Table is that you can get results for each employee if you've entered data with the appropriate layout.
3. You entered the amounts in chronological order. At the end of each month you made a sum total. In this case, processing the list for different criteria is not possible because the Pivot Table will treat the sum totals the same as any other figure. Getting monthly results is one of the very fast and easy features of Pivot Tables.