Excel: Dynamic Dashboards & Pivot Tables

Excel dashboards help us in tracking Key Performance Indicators (KPIs) with ease, which helps organizations track the progress on their targets.

They provide a high-level summary of key aspects of data to keep everyone at par with the progress, hence giving the organization a timely indicator for necessary action in real-time.

Data Source

Move data set can be found on Kaggle (Here).

In the example below, all features of movie data set are unfiltered. Meaning that the visualization takes into account all data (in the left column below) from the original dataset.

What if we need to see the charts for a specific set of data?

In that case, dynamic dashboards allow us to filter the data and make visualizations based on specific criteria.

As an example, in the dashboard below:

Runtime: Feature length and short films (Long movies are excluded)
Budget: Low budget and Mid-Level budget movies only
Decade: 60’s, 70’s and 80’s only

Notice how selection on the left column, and consequently the charts have changed.

Table below is a quick view of select column features and attributes:

Pivot Tables

A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report.

The tool does not actually change the spreadsheet or database itself, it simply “pivots” or turns the data to view it from different perspectives.

Pivot tables are especially useful with large amounts of data that would be time-consuming to calculate by hand. A few data processing functions a pivot table can perform include identifying sums, averages, ranges or outliers. The table then arranges this information in a simple, meaningful layout that draws attention to key values. (Source)

As an example, Average Revenue VS Runtime & Budget chart, with all the data selected appears as below:

Both Row labels and column labels can be filtered.

In the chart below, we have excluded the feature length movies (for the runtime) and included low budget and high budget moves (for the budget)

Gaining new insights by filtering Data

Pivot Tables make the analysis much easer and more accurate, since the stakeholders can look at a specific set of data that falls within their criteria.

This can reward us with new insights that was previously unknown, or actually show us occasions where we misunderstood the data.

In the example above, we can see in the low budget category, the difference of revenue between short and long movies was only 17%. ( $34,537,062 and $40,289,594 )

However in the high budget category, the difference of revenue between short and long movies was a whopping %301 ($78,449,107 and $314,509,461 )