How to create a Simple Dashboard Report in Microsoft Excel, by Ajay Anand on YouTube. 18 min 56 s.
- Video length
- 18 min 56 s
- Time to do it
- 25 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Open your sales data in Excel and click any cell inside the range. Press Ctrl+T, tick My table has headers, and click OK.
Click Insert, then PivotTable. Choose New Worksheet and click OK to place a blank PivotTable on its own sheet.
Drag Region to the Rows area and Sales Amount to the Values area. If Excel shows Count instead of Sum, right-click the value and choose Summarize Values By, Sum.
Drag Order Date to the Columns area. Right-click any date in the PivotTable, choose Group, tick Months, and click OK.
Click inside the PivotTable, go to Insert, then PivotChart, and pick a clustered column chart. Move the chart to a new sheet named Dashboard.
Select the PivotTable, click Insert, then Slicer. Tick Product and Salesperson and click OK. Place the slicers beside the chart.
Right-click a slicer, choose Report Connections, and tick every PivotTable that should respond to that filter. Repeat for the other slicer.
On the Dashboard sheet, click View and untick Gridlines. Add a title in cell A1, then resize and align the chart and slicers so they fit one screen.
Tips and common mistakes
Keep the source data in one clean table with a single header row and no blank rows or merged cells.
Format the value field as currency so the chart axis is easy to read.
Refresh the PivotTable after editing the data by pressing Alt+F5.
Put slicers on the dashboard sheet, not the data sheet, so the layout stays tidy.
Questions
Do I need a PivotTable to build a dashboard in Excel?
Not always, but a PivotTable is the fastest way to summarise sales data and feed a PivotChart. It also lets you add slicers for quick filtering.
How do I refresh the dashboard after adding new sales rows?
Click any cell in a PivotTable, go to PivotTable Analyze, and click Refresh, or press Alt+F5. If the source is a table, new rows are included automatically.
Why does my PivotChart show Count instead of Sum?
The value field contains blank cells or text, so Excel counts the entries. Right-click the value, choose Summarize Values By, and select Sum.
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.
