Build a Pivot Table from a Practice Dataset

What you will make

A summary of revenue by region and category, built from twelve fictional sales records. You will compare its totals with an answer key and observe what happens after changing a source value. Allow about 25 minutes.

Before you start

Use Microsoft Excel for Microsoft 365 on Windows and download the practice workbook below. The desktop steps are described here; Mac and web menus can differ. You need permission to edit a local copy. The workbook contains original RDE practice data, not business results. Each row represents one sale and revenue is in GBP.

1. Inspect the source

Open pivot-practice.xlsx and select the Practice data worksheet. The source is A1:D13: Record ID, Region, Category and Revenue GBP. Check that there is one header row, no blank rows inside the records and twelve distinct record IDs. Revenue cells must contain numbers, not text such as “£120” typed into a text cell.

2. Create the PivotTable

Select a cell within A1:D13. Choose Insert > PivotTable. Confirm that the source covers all four columns and all twelve records, including the headers. Select New Worksheet and confirm. Rename the new sheet Revenue summary. The Expected totals sheet supplied with the workbook is an answer key calculated with formulas; it is not a pre-built PivotTable.

3. Assign the fields

In the PivotTable Fields pane, place Region in Rows, Category in Columns and Revenue GBP in Values. Leave Record ID out of the layout. The value field should show Sum of Revenue GBP. If it shows Count, inspect the source numbers and use the value-field settings to select Sum once the data is numeric. Format the value field as GBP currency with no decimal places.

4. Compare the result

Your Books and Tools totals should be: North £180 and £290; South £120 and £260; West £120 and £230. Region totals should be £470, £380 and £350. The category totals should be £420 and £780, with a grand total of £1,200. Compare these figures with Expected totals. A matching grand total alone is insufficient: also check each category and region.

5. Change the source and refresh

On Practice data, change D2 from 120 to 150. The answer-key formulas update, but the PivotTable may still show the previous figures. Select the PivotTable and use Refresh, available from its context menu or the PivotTable ribbon. North Books should become £210, North total £500 and grand total £1,230. Restore D2 to 120 and refresh again.

6. Practise a different question

Move Category from Columns to Filters. Select Books in that filter. You should see North £180, South £120 and West £120, totalling £420. Clear the filter and restore Category to Columns. Save your workbook with the summary included.

If your result differs

• Count instead of Sum: check whether source revenue was stored as text.
• Missing region: check the source range and any active filters.
• Old figures: refresh the PivotTable after editing the source.
• A new sale is missing: expand the source range or use an Excel Table before refreshing.

Check your learning

You have completed the exercise when the initial totals match, the £30 change appears after refresh and the Books-only total is £420. Explain why a PivotTable refresh differs from the automatic update of the answer-key formulas.

Official guidance

Microsoft: create a PivotTable

Cover image: AI-generated illustrative scene created specifically for this resource.

Download and complete the exercise

Download the PivotTable practice dataset and answer key (Excel XLSX)

Practice files in the formats shown. Version 1.0, 8 October 2026. Personal and internal organisational adaptation permitted with Jefferson Otonbara Imgbi author credit retained.