My First Dashboard from a List

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:
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.