Pito Salas, the father of pivot tables, noted that spreadsheets have patterns of data and rightly concluded that a tool that could help users recognize these patterns would be tremendously useful. His efforts from 1986 onwards culminated in 1991 with the release of Lotus Improv, which has been the model for today’s pivot tables.

A key insight was that data can be summarized in multiple ways. Pivot tables summarize data in two dimensions simultaneously. (Some would say in multiple dimensions.)

A single pivot report can simultaneously be used as a summary or a detailed report, simply by adding and removing fields or by moving fields between row and column areas – all with a few mouse clicks, effortlessly. Pivot tables have revolutionized data analysis not only in what they do, but also because they are so easy to use. With a few clicks and drags (and occasionally a few keystrokes) anyone can:

• Analyze data by dragging and dropping fields on to the report
• Sort and filter and group data.

Our Pivot Reports add a little icing on the cake. Users can:

• Format and group data for suitable representation.
• Save custom layouts of a report for reuse with fresh data extracts or later review.
• Print and export to various file formats

A Pivot Report has four areas into which fields can be placed:                                                                                                                    

Row Area

This area displays row fields on the left side of the report. Fields in the row area are used to categorize data. They are typically nouns, often names. For example, an Employee field would contain names, and a location field might contain cities or sales territories.

Each value in a row-area field appears in a row once. Pivot tables usually have at least one row field although it is possible to have a pivot table that has no row fields.

Column Area

Column Fields are also used to categorize data. They are often used to show time periods – months, quarters and years. One way to think of them is a way to split a row value across a second differentiator.

Row and column areas are interchangeable. There are no rules on what should be in a row versus a column. Indeed, much depends on personal preference, the data being analyzed and the eventual “result”. In practice, Row fields tend to have more variety (more values - employees, part numbers, customers), while column fields have more uniformity (fewer values - a handful of years, or a half-dozen aging buckets in an Accounts Receivable report)

Data Area

The data area contains the field(s) being summarized. Typically this is numeric data and contains a summary value of the field being measured – say, Sales Revenue or Expenses, quantity sold, number of employees – the values represent the summary (sum, count, etc) of the data field at the intersection of the row and column values.

Example: Suppose you have a pivot-report with:

• Products in the row-area
• Months in the column-area
• Sales in the data-area (summed)

Then a particular data-area value would represent, say, the Sales Total (data) of Blue Widgets (row-area) in January (column-area).

Filter Area

Fields in this area are not displayed as part of the report results. However, they may be used to restrict or define a subset of the data that is presented.

Example: Suppose your data-set contains an OrderStatus field, and you don’t need to see it in the results - but you do want to restrict the data to only "shipped" orders. In this case you’d place the OrderStatus field in the FilterArea.

You can apply filters to any, some, or all fields simultaneously.

The filter-area is a convenient place to park fields. Even if you don’t filter them, it’s handy to have them available. You can drag filter-area fields into and out of the reports results at any time.

Hidden Area

There is also a Hidden Area: you can hide fields that you don’t want to see or use at all - they won’t appear in any of the above areas. If you change your mind, you can easily bring them back.