A PivotTable is useful when a worksheet has too many rows to summarize comfortably with manual totals. Instead of writing a separate formula for every category, you can group and summarize the same source data into a report that can be rearranged.
A good PivotTable starts with good source data. If the source has inconsistent headers, mixed data types or blank rows inside the dataset, the report can be harder to build and harder to trust.
What a PivotTable is good for
Use a PivotTable when you need to answer questions such as:
- How much did each region sell?
- Which products generated the most revenue?
- How many orders were placed each month?
- What is the average order value by salesperson?
- How do results compare across categories and periods?
Microsoft describes PivotTables as a way to calculate, summarize and analyze data so you can see comparisons, patterns and trends.
Prepare the source data first
Your source should normally have a single header row, one record per row and consistent data types within each column.
| Date | Region | Product | Sales |
|---|---|---|---|
| Jan 5 | North | Laptop | $1,200 |
| Jan 8 | South | Monitor | $900 |
| Jan 14 | North | Monitor | $1,450 |
| Feb 3 | North | Laptop | $800 |
Avoid merged cells and decorative subtotal rows inside the source range. Keep each column focused on one kind of information.
Step 1: select your data
Click a cell inside the dataset. If the data is already formatted as an Excel Table, the table can be used as the PivotTable source.
Step 2: insert the PivotTable
- Go to Insert > PivotTable.
- Check that Excel selected the correct table or range.
- Choose whether to place the PivotTable on a new worksheet or an existing worksheet.
- Select OK.
Excel creates a blank PivotTable and displays the field list.
Step 3: place fields in the report
For the sales example, a useful first report is sales by region:
| PivotTable area | Field | Purpose |
|---|---|---|
| Rows | Region | Creates one row for each region |
| Values | Sales | Summarizes the sales amount |
Excel normally places text fields in Rows and numeric fields in Values, but you can move fields manually to build the report you need.
Build a more useful report
To compare sales by region and product, put Region in Rows, Product below it in Rows, and Sales in Values.
To see a time comparison, put a date field in Rows or Columns and use the grouping options available in your Excel version. Always verify that the source date column contains real dates rather than text.
Change how values are summarized
Excel may summarize numeric values with Sum, while text or blank-containing value fields may default to Count. You can change the summary method when the question requires it.
For example, Sum of Sales answers a revenue-total question. Average of Sales answers a different question: what is the average value of the records being summarized?
Example: sales by region
With the four-row sample above, a PivotTable that places Region in Rows and Sales in Values produces totals such as:
| Region | Total sales |
|---|---|
| North | $3,450 |
| South | $900 |
The point of the PivotTable is not just the total. Once the fields are in place, you can rearrange the report to answer a different question without rewriting the underlying formulas.
Refresh the PivotTable when source data changes
A PivotTable is based on a snapshot of its source data. If the underlying records change or new rows are added, refresh the PivotTable so the report reflects the current source.
For recurring reports, using an Excel Table as the source can make expanding data easier to manage than a fixed cell range.
Common PivotTable mistakes
- Mixed data types: Dates and numbers should not be mixed with arbitrary text in the same source column.
- Blank or duplicate headers: Give every source column a clear header.
- Wrong summary: Count and Sum answer different questions.
- Stale report: Refresh after source data changes.
- Bad source range: Check that all required rows and columns are included.
- Reading a total without checking the filter: A filtered PivotTable may be showing only part of the source.
PivotTable vs formulas
A PivotTable is useful for exploration and reporting because fields can be rearranged quickly. Formulas such as SUMIFS and COUNTIFS can be better when you need a fixed dashboard layout or a result embedded directly in a worksheet.
You do not need to choose one approach for every workbook. Many useful spreadsheets use PivotTables for exploration and formulas for the final report.
Practical checklist
- Give every source column a clear header.
- Keep one record per row.
- Keep data types consistent.
- Insert the PivotTable from the correct range or table.
- Put descriptive fields in Rows or Columns and measures in Values.
- Confirm whether Sum, Count or Average answers the actual question.
- Refresh the PivotTable after changing the source data.
Related Excel guides
For formula-based summaries, see SUMIF and SUMIFS and COUNTIF and COUNTIFS. For a broader collection of spreadsheet workflows, browse Excel Guides.
Quick answer
Select clean source data, choose Insert > PivotTable, place fields into Rows, Columns, Values and Filters, then check that the summary method answers the question you are trying to answer. Refresh the report whenever the source data changes.