how to

Build a Pivot Table From Scratch in Under Ten Minutes

· 3 min read spreadsheetspivot-tablesanalysisreporting

A pivot table turns rows of transactions into a summary you can rearrange by dragging fields. The entire skill is preparing clean source data and knowing which field goes into which area. This guide builds one from a blank sheet to a finished report.

What you need

  • Raw data with one header row and no merged cells
  • Each column holding exactly one type of value (dates as dates, amounts as numbers)
  • Excel, Google Sheets, or LibreOffice Calc with pivot support
  • A question to answer, such as revenue per region per month

Step by step

  1. Clean the source. Remove blank rows, unmerge everything, and confirm each column header is unique and non-empty. Pivot engines choke on gaps in headers.
  2. Convert the range to an official table (Excel: Ctrl+T) or named range. New rows appended later will then be included on refresh instead of requiring you to resize the range.
  3. Select any cell in the data and insert a pivot table onto a new sheet. Never place it beside the source; overlapping ranges cause refresh errors.
  4. Drag the category dimension (Region) into Rows. Verify the field list shows distinct values and the count matches expectations.
  5. Drag the time dimension (Date) into Columns and group by month. In Excel, right-click a date and choose Group; pick Months. In Google Sheets, add a month helper column if grouping is unavailable.
  6. Drag the measure (Amount) into Values and set aggregation to Sum. If numbers appear as counts, some cells in that column are text, usually from stray spaces; fix the source, not the pivot.
  7. Add a second measure or a calculated field for ratios, e.g. margin = profit/amount. Keep ratios out of the source data so they recompute correctly under different groupings.
  8. Format values as currency or percentages via Value Field Settings, not manual cell formatting, so the format survives refreshes and layout changes.
  9. Refresh after source edits (Excel: Alt+F5 or Data > Refresh All). Pivots read cached snapshots; editing the source never updates them automatically.

Common mistakes

  • Leaving blank header cells, which silently drops columns from the field list
  • Building pivots next to source data, causing overlap errors as the source grows
  • Manually formatting cells inside the pivot, losing formats on every refresh

Related guides

Before pivoting, validate entry quality at the source with [[dropdown-data-validation-lists]]. If the pivot output feeds a dashboard, apply [[chart-design-rules]] to whatever charts you place next to it.

← All guides