Excel Advanced - Pivoting Data

0 of 8 activities completed

Microsoft Excel Advanced · Lesson 08

Pivoting Data

Rearrange one dependable dataset into different analytical views without rewriting formulas, then filter the results interactively with slicers and timelines.

150-minute lesson120 sales records8 guided activitiesMixed Excel versions

Learning outcomes

Create PivotTablesSelect a clean source and place fields in Rows, Columns, Values and Filters.

Analyse flexiblySort, filter, drill down, group dates and change value calculations.

Maintain accuracyRefresh after source changes and verify the source range.

Filter interactivelyAdd slicers and timelines that make analytical views easy to explore.

Download the practice dataset

The page reconstructs the original 120 fictional transactions used throughout the course. The generated workbook contains Sales Data, Pivot Tasks and Controls sheets.

120records

RM146,236revenue

RM56,646profit

12months

Recommended workflow: download the Excel-compatible file, save a working copy and create PivotTables on new worksheets. Ensure Excel recognises Date as a real date and all currency fields as numbers.

1. Prepare the PivotTable source

ACTIVITY 1

Audit the source before pivoting

1. Open Sales Data and select a cell within A4:P124.
2. Confirm one header row, 120 uninterrupted records and no merged cells or subtotal rows.
3. Check that Date contains real dates and Revenue, Cost and Profit contain numbers.
4. Optionally press Ctrl+T and name the Table tblSales; a Table expands more safely when new records are added.

Controls: 120 records, Revenue RM146,236 and Profit RM56,646.

Source-data finding: the task sheet says “three categories,” but this exact 120-row source contains two populated categories: Accessories and Furniture. A PivotTable correctly reports the data that exists.

My source passes all three control checks.

2. First PivotTable: revenue by region

ACTIVITY 2

Create and rearrange fields

1. Choose Insert → PivotTable and verify the full source.
2. Place the PivotTable on a new worksheet named PT Revenue Region.
3. Drag Region to Rows and Revenue (RM) to Values.
4. Open Value Field Settings and confirm Sum, not Count.
5. Format the values as RM through Value Field Settings → Number Format.

Rows
Region

Columns
—

Values
Sum of Revenue

Filters
—

Region	Revenue
Central	RM30,090
East	RM12,682
North	RM59,679
South	RM43,785
Grand Total	RM146,236

My regional PivotTable reconciles to RM146,236.

3. Profit by category

ACTIVITY 3

Summarise and sort category profit

1. Create another PivotTable from the same source.
2. Place Category in Rows and Profit (RM) in Values.
3. Sort Sum of Profit Largest to Smallest.
4. Double-click a value to drill down into the transactions behind it; Excel creates a detail sheet.

Category	Profit
Furniture	RM34,668
Accessories	RM21,978
Grand Total	RM56,646

My category profit total is RM56,646.

4. Monthly revenue and date grouping

ACTIVITY 4

Group dates into months and quarters

1. Place Date in Rows and Revenue in Values.
2. Right-click any date and choose Group.
3. Select Months and Quarters; include Years if the source may later span several years.
4. Add Region to Filters.
5. Expand or collapse the quarter hierarchy.

Checkpoint: the PivotTable shows all 12 months and still totals RM146,236.

If Group is unavailable: look for blanks, text-formatted dates or invalid entries in the Date column. Every source value must be a valid date.

My grouped hierarchy shows four quarters and 12 months.

5. Channel mix

ACTIVITY 5

Use both Rows and Columns

1. Place Channel in Rows.
2. Place Region in Columns.
3. Place Revenue in Values.
4. Move Region from Columns to Filters and observe how the question changes.
5. Restore Region to Columns.

Row totals: Corporate RM56,728; Online RM50,154; Retail RM39,354.

This exercise demonstrates “pivoting”: moving a field changes the report structure without altering the source.

My channel mix has four region columns and the correct grand total.

6. Average order value and Show Values As

ACTIVITY 6

Change the value calculation

1. Place Region in Rows and Revenue in Values.
2. Open Value Field Settings → Summarise Values By → Average.
3. Rename the field Average Order Value.
4. Add Revenue to Values a second time.
5. For the second copy, use Show Values As → % of Grand Total.

Average order values: Central RM1,003.00; East RM422.73; North RM1,989.30; South RM1,459.50.

Other useful “Show Values As” choices

Difference From compares periods; % Difference From shows growth; Rank Largest to Smallest identifies top performers; Running Total creates cumulative analysis.

My Value Field Settings use Average and % of Grand Total correctly.

7. Refresh, source changes and formatting

ACTIVITY 7

Maintain the PivotTable

1. Edit one Revenue value in Sales Data and note that the PivotTable does not update immediately.
2. Right-click the PivotTable and choose Refresh.
3. Undo the test change and refresh again.
4. Use PivotTable Analyze → Change Data Source to verify all records are included.
5. Compare Compact, Outline and Tabular report layouts.
6. Apply a restrained style, clear field names and RM number formats.

Best practice: use an Excel Table as the source and select “Refresh data when opening the file” when appropriate. Refresh does not automatically expand a fixed range.

I tested a source edit, refresh and source-range verification.

8. Slicers and timelines

ACTIVITY 8

Add interactive filters

1. Select a PivotTable and choose PivotTable Analyze → Insert Slicer.
2. Add a Channel slicer and test multi-select and Clear Filter.
3. Choose Insert Timeline and select Date.
4. Switch the timeline between Months and Quarters.
5. Use Report Connections or PivotTable Connections to connect the slicer to compatible PivotTables.

Version note: timelines require Excel 2013 or later. In older versions, use grouped Date fields or a report filter. Menu names can differ slightly across releases.

My slicer and timeline visibly filter the PivotTable.

Troubleshooting

Numbers counted, not summed
The source may contain text. Convert the field to numbers, refresh and choose Sum.

Dates will not group
Remove blank, text or invalid dates, then refresh.

New records are missing
Change the source range or use an Excel Table and refresh.

Slicer controls one PivotTable only
Use Report Connections; PivotTables must share a compatible cache/source.

Knowledge check

1. Does a PivotTable change the source data?

No. It summarises a cached view of the source. Refresh when the source changes.

2. Why use Value Field Settings for number formatting?

Formatting applied there survives layout and refresh changes more reliably than ordinary cell formatting.

3. What happens when you double-click a PivotTable value?

Excel creates a new sheet containing the source records behind that value.

4. Why might a slicer not connect to another PivotTable?

The PivotTables may use different sources or PivotCaches, making them incompatible for one shared control.

Completion checklist

• Records: 120
• Total Revenue: RM146,236
• Total Profit: RM56,646
• Regions: Central, East, North and South
• Channels: Corporate, Online and Retail
• Grouped periods: 12 months / 4 quarters
• Interactive controls: Channel slicer and Date timeline

Next lesson: turn these PivotTable views into interactive PivotCharts and a compact dashboard.

Lesson 08 · Pivoting Data · Excel Advanced