Lab 4: PivotTables for Dashboards - Step by Step | Excel Dashboard Masterclass, by Skills Pragati on YouTube. 10 min 39 s.
- Video length
- 10 min 39 s
- Time to do it
- 30 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Open your workbook in Excel for the web and click any cell inside your data table. Make sure the table has headers and no blank rows.
Go to Insert and choose PivotTable. Confirm the data range and pick New worksheet so each PivotTable sits on its own sheet.
For revenue by region, drag Region to Rows and Revenue to Values. Check the value field is set to Sum, not Count.
For the headline numbers, build a PivotTable with no row field and drag Revenue into Values. Add Order ID or rows to Values with Count for order totals.
For the two years, drag the Date field to Rows and group it by Year, or add a Year column to your data and use that. Put Revenue in Values.
For categories and segments, create two more PivotTables: one with Category in Rows and one with Segment in Rows, both summing Revenue.
For the monthly trend, drag Date to Rows, group by Month, and put Revenue in Values. Sort the months in calendar order.
For the top 10 products, drag Product to Rows and Revenue to Values. Open the Row Labels filter, choose Value Filters, then Top 10 and set it to 10 by Sum of Revenue.
Change a value in the source data, then click Refresh on any PivotTable. Watch the numbers update to confirm the link works.
Tips and common mistakes
If a value shows as Count instead of Sum, open Value Field Settings and switch the summary to Sum.
Keep each PivotTable on its own sheet and give the sheet a clear name so the dashboard stays easy to build.
Group dates by Year and Month before building trend tables, or the rows will list every single day.
Refresh does not pick up new rows unless the source range includes them, so extend the range or format the data as a table.
Questions
Do I need a paid Excel licence?
No. Excel for the web is free with a Microsoft account, and PivotTables work there. Some advanced features are limited compared with the desktop app.
Why does my PivotTable show the wrong total?
The field is probably set to Count instead of Sum. Open Value Field Settings and change the calculation to Sum.
Can I build all the PivotTables from one data table?
Yes. Point every PivotTable at the same source range. Each one answers a different dashboard question.
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.
