||This article needs additional citations for verification. (September 2012)|
In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. 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.
Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.
In their book Pivot Table Data Crunching, authors Bill Jelen and Mike Alexander refer to Pito Salas as 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. A tool that could help the user recognize these patterns would help to build advanced data models quickly. With Improv, users could define and store sets of categories, then change views by dragging category names with the mouse. This core functionality would provide the model for pivot tables.
Lotus Development released Improv in 1991 on the NeXT platform. A few months after the release of Improv, Brio Technology published a standalone Mac implementation called DataPivot (with technology eventually patented in 1999). Borland purchased the DataPivot technology in 1992 and implemented it in their own spreadsheet application Quattro Pro.
In 1993, 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 write Visual Basic for Applications macros to create and modify pivot tables.
- Excel 2000 introduced “Pivot Charts” to represent pivot table data graphically .
Explanation of a pivot table
For typical data entry and storage, data usually appears in flat tables, meaning that it consists of only columns and rows, as in the following example showing data on shirt types:
While tables such as these can contain a lot of data, it can be difficult to get summarized information from them. A pivot table can help quickly summarize the data and highlight 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 datum we would like to see is (sum of) Units. These fields allow several kinds of aggregations including: sum, average, standard deviation, count etc. In this case, the total number of units shipped is displayed here using a sum aggregation.
How a pivot table works
Using the example above, software 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 datum 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 (i.e. cells E2 to E7) together to get a final result.
Pivot tables are an integral part of a spreadsheet application. In addition to Microsoft Excel and numberGo Publisher, competing software programs such as Apache OpenOffice Calc provide similar functionality; the implementation in Apache OpenOffice and LibreOffice up to release 3.3 is called DataPilot. In version 3.4 of both LibreOffice and OpenOffice, DataPilot is renamed 'Pivot Table'.
In 2010 numberGo launched their numberGo Publisher 2.0, an updated application with enhanced pivot, dynamic charting and simplified user interface. Other companies such as Quantrix provide similar implementations.
Pivot functionality also operates in other data visualization tools, including business intelligence packages.
Google Docs initially allowed the creation of basic pivot tables via the pivot table gadget from Panorama called Panorama Analytics, but as of 2011[update] this gadget provided limited functionality and was extremely slow with large amounts of data. In May 2011, Google announced the roll-out of a natively hosted pivot table feature in the Google spreadsheets editor.
ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications.
As an OLAP client
Excel Pivot Tables includes the feature to directly query an OLAP server for retrieving data instead of getting the data from an Excel spreadsheet. On this configuration a pivot table is a simple client of an OLAP server. Excel's Pivot Table not only allows for connecting to Microsoft's solution (Analysis Service), but to any XMLA (OLAP standard) compliant server.
Other OLAP clients are JPivot, Dundas, IcCube (Client Library).
- Comparison of office suites
- Data drilling
- Comparison of OLAP Servers
- OLAP cube
- Group by (SQL)
- Contingency table
- "United States Trademark Serial Number 74472929". 1994-12-27. Retrieved 2013-02-17.
- Jelen, Bill; Alexander, Michael (2006). Pivot table data crunching. Indianapolis: Que. p. 274. ISBN 0-7897-3435-4.
- Patent #5915257, retrieved 2010-02-16
- 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-0-470-13231-9)
- Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
- Beginning Pivot Tables in Excel 2007 (ISBN 1-59059-890-3)
- A quick, free Pivot Table Tutorial
- An explanation of pivot tables with ZK
- PivotTable video for Excel 2007, including compatibility mode
- Introduction to pivot tables by Microsoft Business Solutions
- Video Tutorials by DataPig Technologies
- Pivot Table FAQs by Contextures
- Web-based pivot table example
- Free introduction to pivot tables video in Excel 2007