Pivot table
Encyclopedia
In data processing
, a pivot table is a data summarization tool found in data visualization programs such as spreadsheet
s or business intelligence
software. Among other functions, pivot-table tools 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 tabulation
s. 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, Microsoft Corporation has trademark
ed the specific form PivotTable.
Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.
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:
While those data can contain a lot of information
, it can be difficult to get summarized information. 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 data we would like to see is 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.
provide similar functionality; the implementation in OpenOffice.org and in the related LibreOffice
up to release 3.3 is called DataPilot. In LibreOffice
3.4 DataPilot is renamed 'Pivot Table'. Other companies such as numberGo
and 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 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. http://googledocs.blogspot.com/2011/05/summarize-your-data-with-pivot-tables.html
ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications (Demo).
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).
Data processing
Computer data processing is any process that a computer program does to enter data and summarise, analyse or otherwise convert data into usable information. The process may be automated and run on a computer. It involves recording, analysing, sorting, summarising, calculating, disseminating and...
, a pivot table is a data summarization tool found in data visualization programs such as spreadsheet
Spreadsheet
A spreadsheet is a computer application that simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains alphanumeric text, numeric values or formulas...
s or business intelligence
Business intelligence
Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
software. Among other functions, pivot-table tools 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 tabulation
Cross tabulation
Cross tabulation is the process of creating a contingency table from the multivariate frequency distribution of statistical variables. Heavily used in survey research, cross tabulations can be produced by a range of statistical packages, including some that are specialised for the task. Survey...
s. 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, Microsoft Corporation has trademark
Trademark
A trademark, trade mark, or trade-mark is a distinctive sign or indicator used by an individual, business organization, or other legal entity to identify that the products or services to consumers with which the trademark appears originate from a unique source, and to distinguish its products or...
ed the specific form PivotTable.
Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.
History
In their book Pivot Table Data Crunching,authors Bill Jelen and Mike Alexander refer to Pito Salas
Pito Salas
Pito Salas is a Curacaoan-American Cambridge, Massachusetts-based software developer. While working with the Lotus Advanced Technology Group in 1986, Pito Salas invented a next-generation spreadsheet concept which was released by Lotus in 1989 as Lotus Improv....
as the "father of pivot tables". While working on a concept for a new program which would eventually become Lotus Improv
Lotus Improv
Lotus Improv was a spreadsheet program from Lotus Development that attempted to re-define the way a spreadsheet should work. Instead of treating the grid as the system for referencing data, Improv made all data exist in named ranges. Operations on the data then referred to these names, rather than...
, 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
NeXT
Next, Inc. was an American computer company headquartered in Redwood City, California, that developed and manufactured a series of computer workstations intended for the higher education and business markets...
platform. A few months after the release of Improv, Brio Technology
Brio Technology
Brio Technology was a San Francisco Bay area software company cofounded in 1984 by Yorgen Edholm and Katherine Glassey. The company is best known for their business intelligence software systems, starting with DataPivot on the Apple Macintosh. Brio Software was acquired by Hyperion in 2003...
published a standalone Mac implementation called DataPivot (with technology eventually patented in 1999). Borland
Borland
Borland Software Corporation is a software company first headquartered in Scotts Valley, California, Cupertino, California and finally Austin, Texas. It is now a Micro Focus subsidiary. It was founded in 1983 by Niels Jensen, Ole Henriksen, Mogens Glad and Philippe Kahn.-The 1980s:...
purchased the DataPivot technology in 1992 and implemented it in their own spreadsheet application Quattro Pro
Quattro Pro
Quattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office.Historically, Quattro Pro used keyboard commands similar to Lotus 1-2-3. It is commonly said to have been the first program to use tabbed sheets. Actually, Boeing Calc...
.
In 1993, at the time when the Windows version of Improv appeared, Microsoft Excel
Microsoft Excel
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
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 usually appear in flat tables, meaning that it consists of only columns and rows, as in the following example showing data on shirt types:While those data can contain a lot of information
Information
Information in its most restricted technical sense is a message or collection of messages that consists of an ordered sequence of symbols, or it is the meaning that can be interpreted from such a message or collection of messages. Information can be recorded or transmitted. It can be recorded as...
, it can be difficult to get summarized information. 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 data we would like to see is Units. These fields allow several kinds of aggregations including: sum, average, standard deviation
Standard deviation
Standard deviation is a widely used measure of variability or diversity used in statistics and probability theory. It shows how much variation or "dispersion" there is from the average...
, 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 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 an integral part of a spreadsheet application. In addition to Microsoft Excel, competing software programs such as OpenOffice.org CalcOpenOffice.org Calc
OpenOffice.org Calc is the spreadsheet component of the OpenOffice.org software package.Calc is similar to Microsoft Excel, with a roughly equivalent range of features. Calc is capable of opening and saving most spreadsheets in Microsoft Excel file format...
provide similar functionality; the implementation in OpenOffice.org and in the related LibreOffice
LibreOffice
LibreOffice is a free and open source office suite developed by The Document Foundation as a fork of OpenOffice.org. It is largely compatible with other major office suites, including Microsoft Office, and available on a variety of platforms...
up to release 3.3 is called DataPilot. In LibreOffice
LibreOffice
LibreOffice is a free and open source office suite developed by The Document Foundation as a fork of OpenOffice.org. It is largely compatible with other major office suites, including Microsoft Office, and available on a variety of platforms...
3.4 DataPilot is renamed 'Pivot Table'. Other companies such as numberGo
NumberGo
numberGo is a multi-dimensional data viewing program written and distributed by numberGo for use with Microsoft Windows. It makes extensive use of pivot tables to give depth to flat data. Using the same source data, multiple Layouts can be created to allow the user to see the data from different...
and Quantrix
Quantrix
Quantrix is a spreadsheet program based on the pioneering Lotus Improv, and introduced by Lighthouse Design for NeXTSTEP and OPENSTEP in the early '90s when Lotus stopped selling Improv...
provide similar implementations.
Pivot functionality also operates in other data visualization tools, including business intelligence
Business intelligence
Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....
packages.
Google Docs initially allowed the creation of basic pivot tables via the pivot table gadget
Google Gadgets
Google Gadgets are dynamic web content that can be embedded on a web page. They can be added to and interact strongly with Google's iGoogle personalized home page and the Google Desktop application, as well as Google Wave...
from Panorama
Panorama Software
Panorama Software is a Canadian software and consulting company specializing in business intelligence. The company was founded by Rony Ross in Israel in 1993; it relocated its worldwide headquarters to Toronto, Canada in 2003....
called Panorama Analytics, but 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. http://googledocs.blogspot.com/2011/05/summarize-your-data-with-pivot-tables.html
ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications (Demo).
As an OLAP client
Excel Pivot Tables includes the feature to directly query an OLAPOLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
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
XML for Analysis
XML for Analysis is an industry standard for data access in analytical systems, such as OLAP and data mining. XMLA is based on other industry standards such as XML, SOAP and HTTP...
(OLAP standard) compliant server.
Other OLAP clients are JPivot, Dundas, IcCube (Client Library).
See also
- Comparison of office suites
- Data drillingData drillingData drilling refers to any of various operations and transformations on tabular, relational, and multidimensional data...
- Comparison of OLAP ServersComparison of OLAP ServersThe following tables compare general and technical information for a number of Online analytical processing servers. Please see the individual products articles for further information.-General information:-Data storage modes:-APIs and query languages:...
- OLAP cubeOLAP cubeAn OLAP cube is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives...
- Group by (SQL)
- Contingency tableContingency tableIn statistics, a contingency table is a type of table in a matrix format that displays the frequency distribution of the variables...
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-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)
External links
- A quick, free Pivot Table Tutorial
- An excellent explanation of pivot tables
- 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