A PivotTable turns a detailed list of records into a summary you can explore. It can show spending by category, sales by month, hours by project, or counts by status without writing separate formulas for each group. The quality of the result depends on the quality and shape of the source data.
Prepare the source data first
Use a tabular layout: one header row, one record per row and one variable per column. Avoid completely blank rows inside the range, merged header cells and totals inserted between records. Convert the range to an Excel Table when helpful so new rows can be included more reliably.
Example columns could be Date, Category, Owner, Status and Amount. Each row should represent one transaction or event, not a summary of several transactions.
- Use unique, descriptive column names.
- Check date and number formats.
- Remove blank header cells.
- Keep totals outside the raw transaction rows.
Insert a PivotTable
Select a cell in your data range, choose Insert and then PivotTable, and confirm the range and destination. The wording and panel layout may vary by Excel version. Start on a new worksheet if you want the summary separate from the source data.
Excel shows a field list. Drag fields into Rows, Columns, Values or Filters based on the question you want to answer.
- Rows: the categories you want to group by.
- Columns: a second grouping across the top.
- Values: amounts to sum or records to count.
- Filters: an optional high-level selection such as year or department.
Example: total spending by category
For a spending report, drag Category to Rows and Amount to Values. Excel usually sums numeric fields, but it may count a field if the source values are stored as text or contain incompatible data. Use Value Field Settings to switch between Sum, Count, Average and other supported calculations.
If you see Count of Amount rather than Sum of Amount, inspect the source values before simply changing the calculation. Numbers stored as text may be the real issue.
Example source columns:
Date | Category | Amount
Oct 1 | Books | 45
Oct 3 | Food | 18
Oct 5 | Books | 32
Expected summary:
Books = 77
Food = 18Group dates and add a comparison
If the source has valid Excel dates, you can often group them by month, quarter or year from the PivotTable. If grouping fails, check for text dates, blank cells or mixed formats. You can also add a second field, such as Month in Columns, to compare categories over time.
Keep the summary focused on one clear question. Too many row and column fields can make a PivotTable difficult to interpret.
Refresh and verify the result
A PivotTable may not automatically include changes to the original data until refreshed, depending on the source and settings. Use Refresh or Refresh All after source records change. If your source is an Excel Table, appended rows are more likely to be included when refreshed.
Compare the grand total to an independent total from the source data. A beautiful pivot summary can still be wrong if records were excluded, duplicated or stored in an unexpected format.
- Refresh after changing the source data.
- Check the source range includes every record.
- Verify totals with a SUM formula or trusted report.
- Label the units and time period clearly.
- Avoid drawing conclusions from categories with very small counts without context.
Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.