اكسل

Dashboards provide complete overview of a business in one view. However, they are usually complex and tricky to create. But we can use Power Query Excel Data Model, Measures, Pivot Tables, Slicers and Pivot Charts to create business dashboards quickly.

Here is a call center dashboard built using all the advanced pivot table tricks you have seen so far. 

How to create a dashboard with Pivot Tables

1. Identity information goals for your dashboard and list them down.
2. Create a mock-up (rough sketch) of your dashboard on paper or paint.
3. Gather all the necessary data and set up data model (use Power Query if needed)
4. Calculate the numbers using Pivot Tables (and simple formulas if needed)
5. Insert a worksheet for the dashboard and place items on it by linking them to the pivot tables.
6. Add necessary charts, conditional formats
7. Insert slicers / timelines as needed.
8. Include key messages + alerts as needed.
9. Format everything.

While this process may look simple, it requires a fair bit of thought and execution. I created a detailed video explaining the steps and construction for our call center dashboard.  Please watch it below to understand everything.