How to Create a Dashboard in Excel (Step-by-Step)

A dashboard is a simple and effective way to showcase data in excel. Learn how to create a dashboard In excel by following this blog. Let’s dive in…

My First Dashboard is given below.

You can Download the exercise file from Here

Introduction

An Excel dashboard is one pager (mostly, but not always necessary) that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on it. It contains charts/tables/views that are backed by data. A dashboard is often called a report, however, not all reports are dashboards.

The Source Data feeding the dashboard can be a list or table or may be multiple sources. Converting a list into a Table allows auto expansion of our data source.

To add interactivity to our dashboards, we can use Table Slicers, dynamic Functions or Controls.

Here is my source data and I would like to create a dashboard that analyses Sales Performance.

Step by Step instructions

1. Convert the source list to a table: CTRL+T OK name it: “Source”
2. Select Amount & Profit ALT + F1
   1. Delete Legend & Title
   2. Change Chart Type to Line
   3. Select X-Axis Select Data (Dates) Format axis as Text
3. Create a Profitability Chart
   1. Select profitability column ALT F1
   2. Copy format from other chart: CTRL + C ALT >E >S Formats
   3. Change Type to: Line with Markers
4. Create 5 Slicers: Year / Quarter / Manager / Customer / Region
   1. Test a slicer it squeezes the chart
   2. Select Both Charts Size & Properties Don’t Move or Size
5. Cut the 2 charts and paste in the Dashboard @ cell B6 Stretch them
6. Cut the 5 Slicers and paste in Dashboard:
   1. Year/ Quarter/ Region                                4 Columns – 7 cm x 1.2 cm
   2. Right Click Slicer Settings                         Un-check Display Header (I’s evident) & Check Hide Items with No Data.
   3. Align the 3 and test
7. Customer & Manager Slicers:
   1. Keep Header 3 columns
   2. Size 7 cm x 5 cm
   3. Hide Items with No Data
8. Create Matrix calculations:
   B3 Total Sales                         C3 = Subtotal(109,Source[Amount])
   B4 Max Sales                          C4 = Subtotal(104,Source[Amount])
   B5 Min Sales                           C5 = Subtotal(105,Source[Amount])
9. Copy Labels & Functions for profit and modify:
   E3 Total Profit                       F3 = Subtotal(109,Source[Profit])
   E4 Max Profit                        F4 = Subtotal(104,Source[Profit])
   E5 Min Profit                         F5 = Subtotal(105,Source[Profit])
10. In cell K3 Total Transactions in            M3: = Subtotal(102,Source[Amount])
11. Calculate Profitability (copy from above & Modify)

B21 Max Profit                              C21 = Subtotal(104,Source[Profit%])
B22 Min Profit                              C22 = Subtotal(105,Source[Profit%])

1. Add a label « Profitability % » in M21
2. Format all matrices : Font Segoe UI 9 pt, Format C21 & C22 as percentages
3. Format Slicers New style with No Borders.
4. Resize & Reposition as needed then Test Interactivity.

The Finished Dashboard