What's New: Changes in PivotTables
The following improvements and new functionality make PivotTables and Online Analytical Processing (OLAP) PivotTables easier and faster to use in Microsoft Excel 2010.
In this article
New and improved functionality in PivotTables
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.
For example, the repeat labels feature is useful when you have a PivotTable that has value fields in columns and grand totals and subtotals turned off for all fields on rows.
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.
New and improved functionality in OLAP PivotTables
In Excel 2010, you can create multi-hierarchical named sets. A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. If you are not familiar with the MDX language, you can use a dialog box to define simple named sets. If you are familiar with MDX, you can use the MDX Editor to create more advanced named sets. Named sets are supported in both PivotTables and OLAP Formulas. With named sets, you can build OLAP PivotTable reports that show different metrics for different areas of your business.
Write-back is an important feature for working with data in Analysis Services cubes. Write-back is often used in a variety of scenarios, from high-end planning and budgeting to simple data gathering from multiple users.
In Excel 2010, you can change values in the OLAP PivotTable Values area and have them written back to the Analysis Services cube on the OLAP server. You can use the write-back feature in what-if mode and then roll back the changes when you no longer need them, or you can save the changes. You can use the write-back feature with any OLAP provider that supports the UPDATE CUBE statement.