Pivots make the heavy lifting of summarizing and analyzing data simpler. It is easy to start with and has some useful features which simplify the slicing and dicing of data and view the performance from multiple perspectives. In this webinar, I covered a few essential aspects which can get you started with pivots in no time.
You can download the practice file from here: Sample Data file
The current demonstration is happening on iOS instead of Windows; hence you may find some of the features presented differently. However, the functionalities and the feature’s name are common among both the operating systems.
The webinar is in six parts for convenience.
Part 1: Getting Started with Pivots
Start analyzing a large data table in no time. Add interactivity to your table using slicers and calculated fields.
Windows users may also refer to the following article for recommended pivot table settings:
3 Pivot table settings you should use
Part 2: Descriptive statistics with Pivots
The default calculation of any numerical column in a pivot table is a sum, and for a text column, it is count. However, using the pivot table, we can calculate average, min, max, standard deviation. All of these metrics are part of descriptive statistics.
Statistics Simplified: Central Tendency
Statistics Simplified: Variation
Part 3: Pivot table options & layered analytics
Adding layers to a table and allowing users to drill down and analyze performance from multiple perspectives is a useful pivot table feature.
Part 4: Calculating percentages in Pivots
Percentage calculation is a vital aspect of any analysis. It helps in creating a baseline for comparing different metrics or periods. It is quite simple to calculate percentages in a pivot table using value filed settings
Related article: Do More with Pivot Tables - Value Field Settings
Part 5: Groups & Slicer settings
Create additional categories using numerical fields with the grouping feature in the pivot table. These groups add another dimension to understand performance.
Part 6: Adding visual experience.
Visual analytics is an essential aspect of any analysis. Learn how to create an interactive dashboard-like experience using conditional formatting to generate heat maps, sparklines to depict trends, and more.
5 Useful Features of Excel Pivot Tables
Pareto Chart in Excel (vivran.in)
In the next webinar, we will introduce Power Pivot, where we can use pivot tables with multiple tables and use relationships between the tables.
Join the WhatsApp group BI Simplified to ask questions, share best practices, and get notifications for future webinars.