Master Dynamic Excel Dashboards in 10 Minutes (Beginner to Pro Guide), by Learning Connect on YouTube. 11 min 3 s.
- Video length
- 11 min 3 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Open your sales data and make sure each column has a single header and no blank rows. Remove merged cells and totals rows so the data is one clean block.
Click any cell in the data and press Ctrl + T to turn it into an Excel Table. Tick My table has headers and confirm.
Insert a pivot table from the Table on a new sheet. Drag Region and Category to Rows, Date to Columns or Rows, and Revenue to Values.
Add Profit as a calculated field: PivotTable Analyze, Fields Items and Sets, Calculated Field. Use Revenue minus Cost.
Create three or four KPI cards above the charts. Use GETPIVOTDATA or a simple SUM formula on the Table for total revenue, total profit and profit margin.
Build the charts: a bar chart for category and region, a line chart for monthly revenue, and a donut chart for profit margin split. Place each next to its pivot table.
Insert a Slicer for Region and Category, and a Timeline for Date. Right-click each slicer and choose Report Connections to link it to every pivot table.
Arrange the KPI cards on top, charts below and slicers on the left or right. Use one color palette, align edges and delete gridlines, buttons and extra labels.
Test the dashboard by clicking a slicer value and a timeline range. Check that all charts and KPI cards update together.
Tips and common mistakes
Keep the source data on its own sheet and never type inside a pivot table output, or the layout will break on refresh.
Use Report Connections for every slicer, otherwise only one chart will filter and the dashboard will look wrong.
Set number formats to currency or percentage on the pivot values so KPI cards read clearly.
Press Ctrl + S before adding many charts so you can undo a messy layout quickly.
Questions
Do I need the desktop version of Excel?
Yes, slicers, timelines and Report Connections work best in Excel for Windows or Mac desktop. Excel for the web has limited slicer support.
Why do my charts not update when I click a slicer?
The slicer is probably connected to only one pivot table. Right-click the slicer, choose Report Connections and tick every pivot table on the dashboard.
Can I add new rows to the data later?
Yes, if the data is an Excel Table. Add rows directly below the table and refresh the pivot tables with Alt + F5.
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.
