Pivot Table Stands For
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, displaying the results in a second 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.
When should you use a Pivot table?
Generally, a pivot table would serve you in any of the following situations:
- You have a large amount of transactional data that has become increasingly difficult to analyze and summarize in a meaningful way.
- You need to find relationships and groupings within your data.
- You need to find a list of unique values for one field in your data.
- You need to find data trends using various time periods.
- You anticipate frequent request s for changes to your data analysis.
- You need to organize your data into a format that’s easy to chart.
Pivot Table History
In their book Pivot Table Data Crunching, Bill Jelen and Mike Alexander refer to Pito Salas as the “father of pivot tables”. While working on a concept for a new program that would eventually become Lotus Improv, Salas noted 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 Macintosh 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 the Microsoft Windows version of Improv appeared. Early in 1994 Microsoft Excel 5 brought a new functionality called a “PivotTable” to market. Microsoft further improved this feature in later versions of Excel:
- 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
Pivot Table Feauters
In Excel 2010, multi-threading helps improve the overall PivotTable performance. This means that you’ll notice faster results when you work on large amounts of data, for example when you are sorting and filtering PivotTable data.
In Excel 2010, you can fill down labels in a PivotTable so that you can more easily use the PivotTable. You can also repeat labels in PivotTables to display item captions of nested fields in all rows and columns. You can repeat labels for individual fields, but you can also turn the option to repeat labels on or off for all fields in the PivotTable at the same time.
Excel 2010 provides item search in PivotTables to enable users to work with fields and columns that have a large number of items. By using item search, users can find relevant items among thousands or even millions of rows in the PivotTable. You can use item search to look for PivotField or OLAP CubeField item captions in a single column, with AutoFilter turned on or off.
Excel 2010 provides multi-threaded sorting to enable faster sorting of large amounts of data in PivotTables and Excel tables. Multi-threaded sorting can be turned on or off.
Filtering has been improved to enable analysis of large amounts of data. In Excel 2010, filtering on multiple items is much faster, and data that is not visible (such as hidden items in totals) is included when you apply filters in both OLAP and non-OLAP PivotTables. You can use the same improved filtering functionality in Excel Services.
In Excel 2010, you also have the option to use slicers to filter the data. Slicers provide buttons that you can click to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report.
Show Values As feature
In Excel 2010, the Show Values As feature is easier to find and use, and its functionality has been improved in both OLAP and non-OLAP PivotTables. The Show Values As feature includes additional calculations, such as % of Parent Row Total, % of Parent Column Total, % of Parent Total, % Running Total, Rank Smallest to Largest, and Rank Largest to Smallest. It’s easier to specify a field or item to base the calculation on. As in non-OLAP PivotTables, you can now add the same measure multiple times to the values area in OLAP PivotTables so that you can show the value and a calculation based on the value at the same time.
Undo support for large PivotTables
Undoing operations that add multiple items to the undo stack (such as refresh operations) can significantly slow down performance in large PivotTables. To enhance performance in large PivotTables, the Undo command has been implemented to support larger undo stacks. Also, performance decreases when AutoFit and Styles are applied at the same time a PivotTable is updated or refreshed. To address this problem in Excel 2010, you can cancel AutoFit and Styles by pressing ESC. Turning these options off can enhance performance when you refresh and update data in a large PivotTable.
In Excel 2010, it’s easier to interact with PivotChart reports. Specifically, it’s easier to filter data directly in a PivotChart and to reorganize the layout of a PivotChart by adding and removing fields. Similarly, with a single click, you can hide all field buttons on the PivotChart report.