Pivot Tables in Microsoft Excel

In Microsoft Excel, new features have been added over the years.

Sparklines and slicers, as well as improvements to Pivot Tables and other existing features, can help us to discover patterns or trends in the data.

Here is a quick tutorial on Pivot Tables in Excel which highlights the new features added in Microsoft Office 365, Office 2019, and Microsoft Office 2016.

Earlier 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.

Learn Improved Pivot Tables in Excel

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.

If you would like to learn more about these new features of Microsoft Excel, or would like to attend the Microsoft Excel Training, do contact us at Intellisoft Systems.

If you have any further questions then contact us through email training@intellisoft.com.sg Systems or call at +65 6250-3575!!!

Your Pivot Table Trainer is Vinai Prakash.

Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 10,000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.