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). 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. [ [http://msdn.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspx Microsoft Trademarks] ]

History

The concept that led to today's pivot table came from Lotus Development Corporation's innovative spreadsheet program Lotus Improv. Improv was envisioned in 1986 by Pito Salas, though it was not released until 1991. Salas realized that spreadsheets have patterns of data, and that designing a tool that could help the user recognize these patterns would make data analysis much more efficient. The grouping and aggregation feature Improv provided to fill this need is recognized as the original pivot table. [Alexander, Michael and Bill Jelen: "Pivot Table Data Crunching" (QUE, ISBN 0-7897-3435-4)]

A few months after Improv was first released on the NeXTStep platform, Brio Technology released a standalone Mac implementation called DataPivot. [Wylie, Margie: "Data handling takes new turn with Brio manipulation utility", in "MacWEEK", May 21, 1991. Summary accessed via [http://www.accessmylibrary.com/coms2/summary_0286-9233900_ITM accessmylibrary.com] on September 24, 2007.] Brio appears to have been the first company to use the term "pivot" to describe the concept. The DataPivot technology was purchased by Borland, who implemented it in their Quattro Pro spreadsheet; this was released in 1992, narrowly beating Lotus Improv for Windows to be the first spreadsheet to provide this functionality in the critical Windows market.

Today, many applications provide pivot tables or equivalent functionality, but the best-known modern implementation of the concept is in the dominant spreadsheet application, Microsoft Excel. Even though, Excel's pivot tables are not as widely used as they might be; in a 2004 survey at Jelen's MrExcel.com, fewer than 42% of Excel users reported that they made full use of pivot tables. [cite web|title=MrExcel quick poll|url=http://www.mrexcel.com/quickpoll.php?vo=2 MrExcel.com]

A pivot table can be graphically represented in a pivot chart.

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 following 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 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 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.

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.

Pivot functionality is also provided in other data visualization tools, including business intelligence packages such as Hyperion Performance Suite (the direct descendant of Brio's original implementation).

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

ee also

* Comparison of office suites
* Data drilling
* OLAP cube

References

Further reading

* "A Complete Guide to PivotTables: A Visual Approach" (ISBN 1-59059-432-0) ( [http://books.slashdot.org/article.pl?sid=04/11/19/2012256 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

* [http://www.numbergo.com/Support/Tutorial/PivotTableTutorial.aspx A pivot table tutorial by numberGo]
* [http://lacher.com/toc/tutpiv.htm A series of tutorials by John F. Lacher LLC]
* [http://www.microsoft.com/BusinessSolutions/excel_pivot_tables_collins.mspx Introduction to pivot tables by Microsoft Business Solutions]
* [http://j-walk.com/ss/excel/usertips/tip068.htm Summary Tables]
* [http://www.datapigtechnologies.com/ExcelMain.htm Video Tutorials by DataPig Technologies]
* [http://www.contextures.com/xlfaqPivot.html Pivot Table FAQs by Contextures]