How to Create an Excel Sales Performance Metrics Dashboard in 3 Minutes, by Excel Visual on YouTube. 2 min 59 s.
- Video length
- 2 min 59 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Put your sales records in one clean table. Each row needs a date, salesperson name, units sold and revenue amount, with a header row and no blank rows.
Select the table and go to Insert > PivotTable. Place it on a new sheet so your raw data stays untouched.
Build the ranking PivotTable: drag Salesperson to Rows and Revenue to Values, then sort the Revenue column largest to smallest.
Add a calculated field or helper column that divides each rep's revenue by the grand total to show their percentage share of sales.
Create a second PivotTable with Date in Rows and Revenue in Values. Group the dates by weekday to see which days each rep sells best.
Make a third PivotTable with Month in Rows and Revenue in Values, and add your target as a second value field to compare actual against target.
Add a fourth PivotTable with Salesperson in Rows and Units in Values to compare sales volume with revenue.
Insert a slicer for Salesperson from PivotTable Analyze > Insert Slicer, then connect it to every PivotTable with Report Connections.
Add a second slicer for Month or Weekday and connect it the same way. Hold CTRL and click to select several reps, or click the clear filter button to show all.
Lay the PivotTables and charts on one sheet as KPI cards. Add a sparkline or small chart to each card and test the slicers before sharing.
Tips and common mistakes
Keep the source data on its own sheet and refresh all PivotTables after adding new rows.
Use Report Connections on every slicer, or some charts will ignore the filter.
Check that revenue and units columns are formatted as numbers, not text, or totals will be wrong.
Save a copy of the template before editing so you can start over if a PivotTable breaks.
Questions
Do I need macros to build this dashboard?
No. PivotTables and slicers handle the filtering and sorting without any VBA code.
How do I select more than one salesperson in the slicer?
Hold CTRL and click each name. Click the funnel icon with a red X to clear the filter and show everyone.
Why are my PivotTable totals wrong after adding rows?
The PivotTable source range may not include the new rows. Right click the PivotTable, choose Refresh, and extend the source range if needed.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
