BI Simplified Webinar: Analyzing Data with Pivot



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.


Related article:

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.




Also, see:

5 Useful Features of Excel Pivot Tables

Pareto Chart in Excel (vivran.in)

Say Hello to PowerPivots


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.