Tech & AIBeginnerPreview
Excel Pivot Tables & Dashboards
A hands-on course that takes you from a 50,000-row export to a clean, interactive dashboard your boss can click through. You learn to shape data into a proper table, build PivotTables that summarize by any dimension, group dates and numbers into buckets, write calculated fields and Show Values As percentages, then connect PivotCharts, slicers, and timelines into a single dashboard that refreshes with one button. Every lesson uses a realistic sales-and-orders dataset and ends with something you paste into your own work.
For analysts, accountants, operations, finance, and marketing staff, small business owners, and anyone handed a messy export who needs a clean, interactive report without writing a single formula.
Course content
Workbook & downloads
Put the course into practice — a printable workbook plus editable templates you can fill in and reuse.
Preview the workbook
This workbook turns the course into a build you can ship: a clean source-data checklist, a metric and grouping planner, a Show Values As and calculated-field reference, and a dashboard layout and refresh routine. Work one section per module against your own export (or the sample Orders data), filling the worksheets and completing the checklists in Excel as you go. By the end you will have a three-sheet workbook, Data, Pivots, Dashboard, with synchronized PivotCharts, slicers, and a timeline that refresh in one click, plus a reusable build sheet you can run on any future export.
From Raw Export to Your First PivotTable
Get a real export into a clean Excel Table and build your first summarizing PivotTable.
Exercise: Clean and Convert a Real Export
Take one real export (a sales, orders, or transactions file) or the sample Orders data. Strip any merged headers, total rows, and blank rows so it is one dense rectangle with a single header row. Click inside it, press Ctrl+T to make it an Excel Table, then rename the Table to Orders on the Table Design tab. Confirm a single Ctrl+A from inside selects the whole block.
- List every column header and confirm each is short, unique, and non-blank.
- Name three things you removed or fixed to make the data flat (merged cells, total rows, text-formatted numbers, stray blanks).
- Verify the date column holds real dates and amount columns hold real numbers by checking they right-align by default.
Worksheet: Source-Data Readiness Check
Fill this in for your dataset before building anything. If any answer is No, fix the source first; a PivotTable built on messy data inherits every flaw.
- Table name (e.g. Orders)
- Number of columns and the exact header names
- One header row only, all unique and non-blank? (Yes/No)
- One record per row, no month-in-columns layout? (Yes/No)
- No blank rows or blank columns inside the data? (Yes/No)
- No subtotal or total rows mixed into detail? (Yes/No)
- Date column holds real dates (right-aligned)? (Yes/No)
- Amount columns hold real numbers, not text? (Yes/No)
- Converted to an Excel Table with Ctrl+T? (Yes/No)
Exercise: Build the Region x Product Summary
Insert a PivotTable on a New Worksheet from your Orders Table. Drag Region into Rows, Product into Columns, and Revenue into Values. Read off the grand totals. Then nest Salesperson under Region in the Rows box and expand one region to see the drill-down. Finally set the value field number format to currency, no decimals, via Value Field Settings.
- Which region has the highest total revenue, and which single Region-by-Product cell is the largest?
- After nesting Salesperson under Region, who is the top salesperson in your highest region?
- Drag Order ID into Values as Count: how many orders does your top region have versus the smallest region?
Checklist: First-Pivot Setup Checklist
- Source data is a named Excel Table (Orders), not a fixed range
- PivotTable inserted on its own sheet from that Table
- I can move fields between Rows, Columns, Values, and Filters and watch it recalc
- Value field number-formatted via the field (Value Field Settings), not by selecting cells
- Report layout set to Tabular or Outline form for readability
- Rows sorted by a value (Largest to Smallest) so the biggest items are on top
Summarizing, Grouping, and Percentages
Choose the right summary functions, bucket dates and numbers, and add percentages and calculated metrics.
Worksheet: Metric and Summary Planner
Before dragging fields, decide what each value field should answer. For every metric, pick the field, the summary function, and the number format. This becomes the spec for your Pivots sheet.
- Metric name (e.g. Total Revenue)
- Source field (e.g. Revenue)
- Summary function (Sum / Count / Average / Max / Min / Distinct Count)
- Number format (currency 0dp / number with thousands / percent)
- Distinct Count needed? If yes, note: add to Data Model on insert
- Renamed header text shown in the Pivot
Exercise: Group Dates and Number-Band Your Orders
On a PivotTable, drop Date into Rows, right-click a date, choose Group, and tick Months, Quarters, and Years to build a collapsible date hierarchy. On a second PivotTable, drop Order Value into Rows, right-click, Group, and set Starting at 0, Ending at 1000, By 100; add Order ID to Values as Count to see the order-size distribution.
- After grouping dates, which quarter had the highest revenue, and which month within it led?
- In the order-size distribution, which 100-wide band holds the most orders?
- If Group is greyed out, what is the most likely cause, and how do you confirm it (check the date column for text or blanks)?
Exercise: Add Percentages, a Running Total, and a Margin Field
On your Region revenue Pivot, add a second Revenue value field and set Show Values As to % of Grand Total. On a date-grouped Pivot, add a Revenue field set to Running Total In (the date field) for year-to-date. Then create a Calculated Field named Margin defined as Revenue - Cost (or Revenue / Units for average price) via Fields, Items & Sets, Calculated Field.
- What percent of grand-total revenue does your top region represent?
- At which month does cumulative year-to-date revenue cross half of the annual total?
- What is the Margin (or average price) for your top product, and does it differ from the average across all products?
Checklist: Summarize and Group Checklist
- Picked the correct summary function for each metric (Sum vs Count vs Average)
- Used Distinct Count via the Data Model where unique counts matter
- Dates grouped into Months / Quarters / Years
- At least one numeric field grouped into custom bands
- Added a Show Values As view (% of Grand Total, Running Total, or Rank)
- Created at least one Calculated Field (Margin or average order value)
PivotCharts, Slicers, and Timelines
Turn the Pivots into linked charts and add the controls that make filtering visual and synchronized.
Exercise: Build Three PivotCharts for the Right Questions
From your PivotTables, create a line PivotChart of monthly revenue (date-grouped Rows), a column or bar PivotChart of revenue by region, and a stacked column of revenue by product over time. For each, hide the field buttons (PivotChart Analyze, Field Buttons, Hide All) and give it a plain-language title.
- Which chart type did you choose for the trend, and why a line over a column here?
- For the region comparison, did a sorted bar read more clearly than a pie? Note the difference.
- Write the plain-language title you gave each chart (it should name the metric).
Worksheet: Controls and Connections Map
Plan your controls before placing them. For each control, list the field, the type, and exactly which PivotTables it must drive via Report Connections. A control that is not connected to a Pivot will silently do nothing to it.
- Control 1 field and type (e.g. Region — slicer)
- Control 2 field and type (e.g. Product — slicer)
- Control 3 field and type (e.g. Order Date — timeline)
- PivotTables each control must connect to (list names)
- Slicer columns / layout setting (e.g. 3 columns)
- Timeline default granularity (Years / Quarters / Months)
Exercise: Wire Slicers and a Timeline Across Every Pivot
Insert slicers for Region and Product and a Timeline for the Date field. Right-click each control, open Report Connections, and tick every dashboard PivotTable. Click a region, then drag the timeline to one quarter, and confirm all charts and tables move together. Style the slicers (set columns, choose a style) and align them into one band.
- After clicking a single region, did every chart update? If one did not, which control connection was missing?
- Select two regions with Ctrl-click and one quarter on the timeline: what is the resulting total revenue?
- Use the timeline granularity dropdown to switch from Months to Quarters: confirm the charts re-aggregate.
Checklist: Interactivity Checklist
- Three PivotCharts built and tied to PivotTables
- Field buttons hidden on every PivotChart
- Slicers added for the top one or two category dimensions
- Timeline added for the date field (real dates confirmed)
- Every control connected to every dashboard Pivot via Report Connections
- One click on a slicer or timeline moves all charts and tables together
Assembling and Maintaining a Dashboard
Lay the dashboard out on one screen and lock in a refresh-and-troubleshoot routine that keeps it trustworthy.
Exercise: Assemble the One-Screen Dashboard
Create three sheets: Data (the Orders Table), Pivots (all PivotTables), and Dashboard (charts, KPIs, controls only). On the Dashboard, place the title, timeline, and slicers across the top, a row of KPI numbers below, the main trend chart in the middle, and two supporting charts beneath. Hide gridlines and add an as-of date cell. Confirm it fits one screen at 100% zoom.
- List the three to five KPI numbers you placed across the top.
- Did you keep all PivotTables off the Dashboard sheet so charts do not get bumped? Confirm.
- How are your KPI cells fed (a one-cell Pivot connected to the controls, or GETPIVOTDATA), and do they update when you filter?
Worksheet: Dashboard Layout and KPI Sheet
Sketch the dashboard in words so the build is intentional. Fill the bands top to bottom and note where each control and chart sits.
- Dashboard title text
- Top band controls (slicers + timeline)
- KPI 1 (name, source Pivot, format)
- KPI 2 (name, source Pivot, format)
- KPI 3 (name, source Pivot, format)
- Main trend chart (metric and type)
- Supporting chart A (metric and type)
- Supporting chart B (metric and type)
- As-of date cell location and refresh-on-open setting
Checklist: Refresh and Trust Checklist
- Source stays an Excel Table so appended rows are picked up automatically
- PivotTable Options set to Refresh data when opening the file
- Routine documented: paste new data, Refresh All, sanity-check the grand total, ship
- Empty cells set to show 0 in PivotTable Options
- Large or multi-table data loaded via the Data Model for speed and Distinct Count
- As-of date visible so viewers know how current the numbers are
Exercise: Run the Troubleshooting Drill
Deliberately reproduce and fix the common failures so you recognize them under pressure. Paste a few new rows and confirm Refresh All updates the dashboard. Then introduce a blank in the Region column and a text-formatted number in Revenue, observe the symptoms, and fix each.
- After adding rows, did the totals change only after Refresh All? Note that refresh is the first fix to try.
- What symptom did the blank in Region cause (a (blank) row label), and how did you remove it?
- What symptom did the text-formatted Revenue cause (Count instead of Sum), and how did you correct the source?
Your Action Plan
- Pick one real export this week and clean it into a single named Excel Table called Orders.
- Build a Region-by-Product revenue PivotTable and set summary functions and number formats on the fields.
- Group the Date field into Months, Quarters, and Years, and band one numeric field into ranges.
- Add Show Values As percentages (percent of grand total, running total) and one Calculated Field such as Margin.
- Create three PivotCharts: a monthly trend line, a revenue-by-region bar, and a product breakdown.
- Insert slicers for your top dimensions and a timeline for dates, and connect them to every Pivot via Report Connections.
- Split the workbook into Data, Pivots, and Dashboard sheets and lay out the dashboard to fit one screen.
- Add KPI numbers, hide gridlines, and place an as-of date so viewers trust the report.
- Turn on Refresh data when opening the file and write your paste-new-data, Refresh All, sanity-check routine on the sheet.
- Run the troubleshooting drill (blanks, text numbers, missing refresh) until the fixes are second nature, then reuse this build on your next export.
Pairs well with
Courses members commonly take alongside this one.
Flagship CoursePreview
Freelance Business Foundations: Position, Price, Sell, and Deliver High-Value Services
Freelancing · Beginner · 16h
Self-pacedPreview
Client GrowthPreview
Freelance Client Acquisition: Outreach, Leads, Referrals, and Deal Flow
Freelancing · Beginner · 15h 30m
Self-pacedPreview
Sales SystemPreview
Freelance Sales & Proposals: Discovery Calls, Scoping, Objections, and Closing
Freelancing · Intermediate · 16h
Self-pacedPreview