My First Dashboard from a List
You can Download the exercise file from Here
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
- Convert the source list to a table: CTRL+T OK name it: “Source”
- Select Amount & Profit ALT + F1
- Delete Legend & Title
- Change Chart Type to Line
- Select X-Axis Select Data (Dates) Format axis as Text
- Create a Profitability Chart
- Select profitability column ALT F1
- Copy format from other chart: CTRL + C ALT >E >S Formats
- Change Type to: Line with Markers
- Create 5 Slicers: Year / Quarter / Manager / Customer / Region
- Test a slicer it squeezes the chart
- Select Both Charts Size & Properties Don’t Move or Size
- Cut the 2 charts and paste in the Dashboard @ cell B6 Stretch them
- Cut the 5 Slicers and paste in Dashboard:
- Year/ Quarter/ Region 4 Columns – 7 cm x 1.2 cm
- Right Click Slicer Settings Un-check Display Header (I’s evident) & Check Hide Items with No Data.
- Align the 3 and test
- Customer & Manager Slicers:
- Keep Header 3 columns
- Size 7 cm x 5 cm
- Hide Items with No Data
- 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])
- 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])
- In cell K3 Total Transactions in M3: = Subtotal(102,Source[Amount])
- Calculate Profitability (copy from above & Modify)
B21 Max Profit C21 = Subtotal(104,Source[Profit%])
B22 Min Profit C22 = Subtotal(105,Source[Profit%])
- Add a label « Profitability % » in M21
- Format all matrices : Font Segoe UI 9 pt, Format C21 & C22 as percentages
- Format Slicers New style with No Borders.
- Resize & Reposition as needed then Test Interactivity.
The Finished Dashboard