Pivot table
From Wikipedia, the free encyclopedia
A pivot table is a powerful data summarization tool in Microsoft Excel, OpenOffice.org Calc, and other electronic spreadsheet programs. Among other functions, it can automatically sort, count, and total data stored in a spreadsheet and create a second table displaying the summarized data. Pivot tables are useful to quickly create crosstabs. 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 name PivotTable is a trademark of Microsoft Corporation.
Contents |
[edit] History
According to Bill Jelen and Mike Alexander, authors of the best selling book Pivot Table Data Crunching, the concept that led to today's pivot table came from Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas. Salas realized that spreadsheets have patterns of data. By designing a tool that could recognize these patterns, one could quickly build advanced data models.
In a 2004 survey at MrExcel.com, fewer than 42% of Excel users make use of the powerful features in pivot tables.
In OpenOffice.org Calc, the DataPilot provides similar functionality with drag-and-drop column fields inside the pivot table.
A pivot table can be graphically represented in a pivot chart.
[edit] Explanation of a pivot table
For typical data entry and storage, data is usually flat. Flat means that it consists of only columns and rows, such as in the below example:
While there is a lot of information stored in such data, it is very difficult to gather the information you want out of it. A pivot table can help you quickly summarize the flat data, giving it depth, and get the information you want. 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's 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 row is Region, the column is Ship Date, and the data we would like to see is Units. These fields were dragged onto the pivot table from a list of available fields. Pivot tables also 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.
[edit] 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.
[edit] Other programs
Although pivot tables are supported by Microsoft Excel and OpenOffice.org Calc, some users may find their implementation of it is lacking due to the generic design of these applications. Some companies such as numberGo, Quantrix, and some other spreadsheet software packages have filled this gap to simplify the use of pivot tables.
[edit] References
- Alexander, Michael and Bill Jelen: Pivot Table Data Crunching (QUE, ISBN 0-7897-3435-4) June 2005
- Call for Help (TV Show), Episode 236, originally aired 8/19/2005
- U.S. Patent & Trademark Office [1]
[edit] See also
[edit] External links
[edit] Online tutorials
- A series of tutorials by John F. Lacher LLC
- Introduction to pivot tables by Microsoft Business Solutions
- Summary Tables
[edit] Books on pivot tables
- Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
- A Complete Guide to PivotTables: A Visual Approach (ISBN 1-59059-432-0) (in-depth review at slashdot.org)
