In Microsoft Excel , new features are sparklines and slicers, and improvements to Pivot Tables and other existing features, can help us to discover patterns or trends in the data. In the previous post we had a look at the Sparklines and Slicers features of Excel so now we will look at the improved pivot table feature of excel.
Improved Pivot Tables
PivotTables are now easier to use and more responsive. Key improvements include:
- Performance enhancements: In Excel, Multi-threading helps advanced sorting, data retrieval and filtering in Pivot Tables.
- Write-back support: In Excel, we can update values in the OLAP PivotTable Values area and then transferred to the Analysis Services cube on the OLAP server. We can use the write-back feature in what-if mode and then roll back the changes when we no longer need them, or we can save the changes. We can use the write-back feature with any OLAP provider that supports the UPDATE CUBE statement.
- Enhanced filtering: We can use slicers to quickly het the required data in a PivotTable and see which filters are applied without having to open additional menus. In addition, the filter interface includes a handy search box that can help us to find what we need among potentially thousands (or even millions) of items in the PivotTables.
- Pivot Table labels: We can add labels in a Pivot Table and also replicate them in the Pivot Tables. This will help us to display item captions of nested fields in all rows and columns.
- PivotChart enhancements: It has made things easy to interact with PivotChart reports. Specifically, it’s easier to get the required data directly in a PivotChart and to reorganize the layout of a PivotChart by adding and deleting fields. Similarly, we can hide all field buttons on the PivotChart report.
- Show Values As feature: The ‘show values as’ feature includes a number of new, automatic 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.
Trainer: Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 5000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.