How to Use Pivot Tables in Excel
Learn what an Excel PivotTable is, how to create one, arrange fields, group dates, refresh results, and fix common source-data errors.
What a PivotTable does
A PivotTable groups many source rows into a compact summary. A sales table can become revenue by region, orders by month, or average price by product without writing one formula per category. The summary is a separate report: moving fields or filtering it does not reorder or delete the source records.
The source should be one rectangular list with a single header row, one record per row, and no merged cells or decorative subtotal rows. Each column should describe one field, such as Date, Region, Product, or Revenue. Convert the source to an Excel Table with Ctrl+T when new rows will be appended; a table gives the PivotTable a source that expands more predictably than a fixed address.
Create a PivotTable step by step
Click one cell inside the source list and choose Insert > PivotTable. Verify the displayed table or range; selecting only one column produces an incomplete report. Choose New Worksheet for a clean first attempt, or Existing Worksheet and specify an empty destination. Select OK to create the blank report and open the field list.
Drag a category such as Region into Rows. Drag a numeric field such as Revenue into Values. Add Product to Columns only when the extra breakdown will remain readable, and put a field such as Salesperson into Filters when one selection should affect the whole report. You can drag the same numeric field into Values twice, then change one copy to a different calculation for side-by-side Sum and Average.
Rows, Columns, Values, and Filters
Rows create labels down the left; multiple row fields form a hierarchy. Columns create headings across the top and can make a report very wide. Values perform the aggregation. Open Value Field Settings to choose Sum, Count, Average, Min, Max, or another supported calculation, and use Number Format there so formatting remains attached to the value field.
Filters limit the report without removing source records. A report filter shows one field above the PivotTable; field drop-downs filter individual row or column labels. Blank items usually mean blank source cells. If a numeric field lands in Values as Count instead of Sum, inspect the source for text, error values, or mixed types rather than merely changing the label.
Group dates and refresh the report
For real Excel dates, place Date in Rows, right-click a date label, choose Group, and select Months plus Years when the data spans more than one year. Grouping by Month alone combines January from every year. If Group is unavailable, the source column probably contains text, blanks, or errors; correct the source values and refresh before trying again.
A PivotTable is a cached summary and does not automatically recalculate from every source edit. Right-click inside it and choose Refresh, or use Data > Refresh All. If the source is a fixed range, newly appended rows remain outside it; change the data source or use an Excel Table. After refresh, verify totals and filters, especially when columns were renamed or removed.
Common errors and when SUMIFS is enough
“PivotTable field name is not valid” usually means a blank header or a source selection that includes an empty top row. Wrong totals often come from numbers stored as text, duplicate source records, an unintended Count calculation, or filters that remain active. A report that appears to lose dates may simply be grouped; expand the hierarchy or ungroup it before diagnosing missing records.
Use a PivotTable when you want to explore different groupings quickly. Use SUMIFS when the output layout must stay fixed, feed another calculation, or update directly with worksheet recalculation. For example, =SUMIFS(C2:C6,A2:A6,E2) gives one controlled total for the region in E2. The live sheet below practices that underlying aggregation; FormulaCamp’s sheet does not simulate Excel’s PivotTable interface.
Your turn
In F2, total Revenue for the region in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Revenue | Region | Total | |
| 2 | East | Desk | 120 | East | ||
| 3 | West | Chair | 90 | |||
| 4 | East | Chair | 80 | |||
| 5 | West | Desk | 110 | |||
| 6 | East | Desk | 50 |
Answer: =SUMIFS(C2:C6,A2:A6,E2)
Worked example
Five sales rows in A2:C6. The formulas verify the totals a PivotTable would show for East and West; the interface steps themselves belong to Excel.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Revenue | Region | |
| 2 | East | Desk | 120 | East | |
| 3 | West | Chair | 90 | ||
| 4 | East | Chair | 80 | ||
| 5 | West | Desk | 110 | ||
| 6 | East | Desk | 50 |
| Formula | Result |
|---|---|
=SUMIFS(C2:C6,A2:A6,E2) | 250 |
=COUNTIF(A2:A6,"East") | 3 |
=SUM(C2:C6) | 450 |
=SUMIFS(C2:C6,A2:A6,"West") | 200 |
Quick mistake checklist
- Building the report from a range with blank or duplicate headers.
- Assuming a numeric field is summed without checking Value Field Settings.
- Forgetting to refresh or to include newly appended source rows.
- Grouping dates by month without also grouping by year.
Frequently asked questions
What is a PivotTable in Excel?
It is a separate, rearrangeable summary of source rows. Fields define the groups, filters, and calculations; the original records remain unchanged.
How do I create a PivotTable?
Select one cell in a clean rectangular data set, choose Insert > PivotTable, confirm the source and destination, then place fields in Rows, Columns, Values, and Filters.
Why does my PivotTable show Count instead of Sum?
The source field may contain text, blanks, or errors. Correct mixed types, refresh, and confirm the calculation in Value Field Settings.
Why are new rows missing?
Refresh the report and confirm its source includes the rows. Using an Excel Table avoids a fixed range that stops before appended records.
Practice the skill
Continue with a checked FormulaCamp lesson: PivotTable Free
Open the free lesson