Turn a tidy ticket log into a compact weekly summary by owner, priority, and status without editing the source rows.
Created by the First Role Skills Editorial Team. This lesson uses a fictional workplace scenario and links to primary product documentation where the interface or feature matters.
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.
DeliverableA reproducible summary that answers how many tickets are open and where the workload sits.
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
Complete it in six controlled steps
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.
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.
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.
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.
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.
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.
Before and after
Before:
A manager receives 30 rows and manually counts red cells. The total changes when a hidden row is missed.
After:
Open tickets by owner and priority
Avery: P1 1, P2 2, P3 3
Jordan: P1 0, P2 2, P3 4
Refreshed: 3 Aug 2026
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.
Download the support-ticket dataset — Thirty fictional tickets designed for a first pivot-table exercise. — Download file
Use only fictional or fully anonymized data. Never upload an employer’s confidential information to a portfolio.
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.
Check your own work
- Single header row
- Question defined first
- Count aggregation checked
- Grand total validated
- Refresh context shown
Primary reference: Microsoft Support: create a PivotTable
Why this matters in your first job
Pivot tables are one of the most frequently mentioned spreadsheet skills in entry-level postings for operations, support, administration and junior analyst roles. They let you answer common questions, such as “How many open tickets does each person have?”, in seconds instead of counting rows by hand. Being able to build one and, just as importantly, validate it, is a concrete skill you can demonstrate in interviews and practical tests.
Common questions
Excel or Google Sheets? Both have pivot tables, with slightly different menus. The concepts here, a clean source table, deliberate fields and validated totals, apply to both.
Why does my pivot show Sum instead of Count? When the value field contains numbers, spreadsheets often default to Sum. Change the aggregation to Count when you are counting records.
How do I show this skill to employers? Describe it in a resume bullet with context and include the exercise in a simple portfolio. See Excel skills employers expect and how to build a professional portfolio.