How to Create a Dynamic Excel Dashboard in Just 5 Steps

Microsoft Excel is a great tool for creating business dashboards. Recently, I made the below dashboard using Excel in just 5 steps.


Step 1: Figure out what you need

The first step for any successful dashboard is to understand what information your users want and how they want it.

dashboard-sketch

  • Talk to your users and ask them “what and why” they need the information.
  • Prepare a mock-up sketch of the dashboard and get the buy-in.
  • Identify data sources and refresh options.

At the end of STEP 1, you should have a clear idea about where you data is, how it should be gathered, analyzed and presented.

Step 2: Clean & Set up data

If you look long and hard, you can find the Loch Ness Monster, the truth about Bermuda Triangle before you locate the Perfectly Clean Business Data.

The next step is to clean & structure your data in a tabular format.

  • Use Power Query to connect to the data source(s) and fetch the data.
  • Set up necessary data cleaning processes with in Power Query.
  • Load the data to Excel either as tables or to the data model.
At the end of this step, you should have one or more tables of data in Excel.

Clean and set up data in a tabular format in Excel

Step 3: Calculate the KPIs with Pivots

Load the cleaned data into “data model” based pivot tables in Excel. Now, using Power Pivot DAX measures, calculate the necessary KPIs.

For example, for the above business dashboard, I’ve calculated:

  • Total Sales =SUM(sales[Sales])
  • Total Boxes = SUM(sales[Boxes])
  • Total Expenses = SUM(sales[Expenses])
  • Total Profit = [Total Sales] – [Total Profit]
  • Profit % = [Total Profit] / [Total Sales]

Create the pivot tables using these measures.

Related: Learn more about Power Pivot & DAX in Excel.

Calculate the necessary metrics / KPIs using DAX

Step 4: Make Interactive Charts

First create the necessary pivot charts (refer to the mock-up you made in step 1). 

Then, make them interactive using Slicers, Timelines or Report Filters.

Learn how to create a slicer here.

We can create interactive charts with pivot charts & slicers

Step 5: Bring Everything Together & Format

We are ready for the big finale!

Bring all the charts, KPIs and slicers you have created so far to a new sheet. (Cut & Paste).

Arrange them neatly and format as needed.

Viola, your dashboard is ready!

Final dashboard is a collection of various KPIs, pivots, pivot charts & slicers


Video Tutorial – How to create a dynamic business dashboard

I’ve created a 30 minute tutorial on how to create the above dashboard with all the necessary steps. Watch it below or on my Youtube channel.

Sample Workbooks

Please download the blank template file from here.

If you want the full dashboard (with al the workings), you can find it in my Excel School Online Course.

More Dashboard Tutorials & Examples

I have a confession to make. Dashboards are kind of my thing. So I have been writing about them for almost 15 years now. Here are some of my best Excel & Power BI dashboards.

.elementor-17051 .elementor-element.elementor-element-8dde967:not(.elementor-motion-effects-element-type-background) > .elementor-column-wrap, .elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-column-wrap > .elementor-motion-effects-container > .elementor-motion-effects-layer{background-color:#E4FAFF;}.elementor-17051 .elementor-element.elementor-element-8dde967:hover > .elementor-element-populated{background-color:#FFF191;}.elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-element-populated{border-style:solid;border-width:1px 1px 1px 1px;border-color:#9B9B9B;transition:background 0.3s, border 0.3s, border-radius 0.3s, box-shadow 0.3s;}.elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-element-populated, .elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-element-populated > .elementor-background-overlay, .elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-background-slideshow{border-radius:10px 10px 10px 10px;}.elementor-17051 .elementor-element.elementor-element-8dde967 > .elementor-element-populated > .elementor-background-overlay{transition:background 0.3s, border-radius 0.3s, opacity 0.3s;}.elementor-17051 .elementor-element.elementor-element-7f6ada0{text-align:center;}.elementor-17051 .elementor-element.elementor-element-7f6ada0 .elementor-heading-title{font-family:”Nunito Sans”, Sans-serif;font-size:1.2em;font-weight:700;line-height:1.6em;letter-spacing:-0.3px;}

employee-vacation-dashboard-full-view

Designing a dashboard to track Employee vacations [case study]

200k-bike-ride-dashboard

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

Homer Simpson Sayings - Power BI Dashboard

D’oh – Visualizing Homer’s favorite sayings in Power BI

project management dashboard - interactive & dynamic

How to create a fully interactive Project Dashboard with Excel – Tutorial

easy website dashboard

Easy Website Metrics Dashboard with Excel

Employee Turnover Dashboard – Power BI for HR


Become awesome in Excel

The post How to Create a Dynamic Excel Dashboard in Just 5 Steps appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/how-to-create-a-dynamic-excel-dashboard-in-5-steps/

Close Menu