Pivot table

A pivot table is a data summarization tool found in data visualization programs such as spreadsheets (e.g. Microsoft Excel, OpenOffice.org Calc, Lotus 1-2-3). Pivot Tables were created in 1979 by Paul Spinks. Among other functions, they can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. Pivot tables are also useful for quickly creating cross tabs. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name. The term pivot table is a generic phrase used by multiple vendors. However, the specific form PivotTable is a trademark of the Microsoft Corporation.^[1]

History

In their book Pivot Table Data Crunching, authors Bill Jelen and Mike Alexander call Pito Salas the “father of pivot tables”.

While working on a concept for a new program which would eventually become Lotus Improv, Salas realized that spreadsheets have patterns of data. By designing a tool that could help the user recognize these patterns, one could quickly build advanced data models. With Improv, users could define and store sets of categories, then change views by dragging category names with the mouse. This core functionality would become the basis for pivot tables today.

Improv was released in 1991 on the NeXT platform. A few months after the release of Improv, Brio Technology published a standalone Mac implementation called DataPivot. Borland purchased the DataPivot technology in 1992 and implemented it their own spreadsheet application Quattro Pro.

One year later, at the time when the Windows version of Improv appeared, Microsoft Excel 5 was already on the market with a new functionality called a “PivotTable”. This functionality was further improved in later Excel versions:

• Excel 97 included a new and improved PivotTable Wizard, the ability to create calculated fields, and new pivot cache objects that allow developers to code against pivot tables.
• Excel 2000 introduced “Pivot Charts” to graphically represent pivot table data.

Explanation of a pivot table

For typical data entry and storage, data is usually flat, meaning that it consists of only columns and rows, as in the following example:

While there is a lot of information stored in such data, it can be difficult to get summarized information. A pivot table can help quickly summarize the flat data, giving it depth, and get the desired information. The usage of a pivot table is extremely broad and depends on the situation. The first question to ask is, "what am I looking for?" In the example here, let us ask "How many Units did we sell in each Region for every Ship Date?":

A pivot table usually consists of row, column, and data (or fact) fields. In this case, the column is Ship Date, the row is Region, and the data we would like to see is Units. These fields will allow several kinds of aggregations including: sum, average, standard deviation, count, etc. In this case, we wanted to see the total number of units shipped, so we used a sum aggregation.

How a pivot table works

Using the example above, it will find all distinct records for Region. In this case, they are: North, South, East, West. Furthermore, it will find all distinct records for Ship Date. Based on the aggregation type, sum, it will summarize the fact, and display them in a multidimensional chart. In the example above, the first data point is 66. This number was obtained by finding all records where both Region was East and Ship Date was 1/31/2005, and adding the Units of that collection of records together to get a final result.

Application support

Pivot tables are now considered an integral part of a spreadsheet application. In addition to Microsoft Excel, competing software such as OpenOffice.org Calc provide similar functionality; the OpenOffice.org implementation is called DataPilot. Other companies such as numberGo and Quantrix provide similar implementations.

P

Google Docs allow the creation of basic pivot tables, via an application gadget, but the functionality is still very limited.

See also

• Comparison of office suites
• Data drilling
• OLAP cube
• Group by (SQL)

References

Further reading

• A Complete Guide to PivotTables: A Visual Approach (ISBN 1-59059-432-0) (in-depth review at slashdot.org)
• Excel 2007 PivotTables and PivotCharts: Visual blueprint (ISBN 978-0470132319)
• Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
• Beginning Pivot Tables in Excel 2007 (ISBN 1-59059-890-3)

External links

• PivotTable video for Excel 2007, including compatibility mode
• A series of tutorials by John F. Lacher LLC
• Introduction to pivot tables by Microsoft Business Solutions
• Video Tutorials by DataPig Technologies
• Pivot Table FAQs by Contextures