Created by the First Role Skills Editorial Desk. This lesson uses a fictional workplace scenario and links to primary product documentation where the interface or feature matters.
Start with the work
A realistic scenario
Your team has one row per support ticket. A manager asks for open volume by owner and priority before the weekly check-in.
Avoid rework
Three beginner mistakes
Building a pivot from merged cells or multiple header rows
Counting a field that contains blanks
Reporting totals without checking the source range
The method
Complete it in six controlled steps
- 01
Make the source rectangular
Use one header row, one ticket per row, and one meaning per column. Remove totals and decorative blank rows from the source range.
- 02
Define the question
Write the question before arranging fields: 'How many open tickets does each owner have by priority?' This prevents random drag-and-drop summaries.
- 03
Create the pivot separately
Select the full source range and place the pivot on a new sheet. Keeping the summary separate reduces accidental edits to raw records.
- 04
Arrange fields deliberately
Put Owner in Rows, Priority in Columns, Ticket_ID in Values as Count, and Status in Filters. Check that the aggregation is Count, not Sum.
- 05
Validate three numbers
Compare the pivot grand total with a filtered source count. Spot-check one owner and one priority. Refresh after changing the source.
- 06
Label the output
Add the reporting period, filter state, refresh time, and data owner above the pivot. A screenshot without this context is easy to misread.
See the difference
Before and after
A manager receives 30 rows and manually counts red cells. The total changes when a hidden row is missed.
Open tickets by owner and priority Avery: P1 1, P2 2, P3 3 Jordan: P1 0, P2 2, P3 4 Refreshed: 3 Aug 2026
Original practice
Complete the exercise
Use the sample ticket log to build the specified pivot. Then change one ticket from Open to Closed, refresh, and record which totals changed.
Use only fictional or fully anonymized data. Never upload an employer's confidential information to a portfolio.
Explain your decisions
Turn the exercise into credible evidence
Present the source question beside the pivot so the reader knows what the summary answers. Record the grand total before and after the test change, the filter state, and the refresh time. Explain why Ticket_ID is counted, why Status is a filter, and how you spot-checked one owner. This short validation log matters more than adding several decorative charts that do not support a workplace decision.
Quality gate
Check your own work
- Single header row
- Question defined first
- Count aggregation checked
- Grand total validated
- Refresh context shown