Excel Pivot Table Function (Summarize & Explore Data) – Examples & Practice
Practice PivotTable workflows online with sample data, clear steps, and spreadsheet-style guidance.
Instruction
Create a pivot summary of total sales by region.
Formula Syntax
PivotTables are created from the ribbon (Insert > PivotTable) rather than a single worksheet function.
- Source range / Table: Clean rectangular data with headers in the first row.
- Rows / Columns / Filters / Values: Field areas that define the layout.
- Refresh: Updates the cache from the source after data changes.
What it does
A PivotTable summarizes a dataset by grouping dimensions and aggregating measures—sums, averages, counts, and more—without writing one-off formulas for every slice. It is the fastest way to answer “what if we group by X?” questions in Excel.
Excel Pivot Table Function Examples
Sum sales by region
Insert > PivotTable, place Region in Rows and Sales in Values with aggregation Sum.
Average score by group
Put Group in Rows and Score in Values, then set aggregation to Average.
Count orders by status
Place Status in Rows and Order ID in Values with aggregation Count (ensure IDs are not blank).
sales-dataset.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Amy | 350 |
| 3 | South | Ben | 280 |
| 4 | North | Cara | 410 |
| 5 | West | Duke | 305 |
| 6 | South | Eva | 390 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Normalize column names (no duplicates, no blank headers).
- Use **Tables** so new rows flow into the pivot source automatically.
- Duplicate a pivot when experimenting so you keep a known-good layout.
Pivot Table Function Use Cases
- Executive summaries by region, product, or time
- Ad hoc exploration before building fixed reports
- Detecting outliers and sparse categories
- Month-over-month rollups with grouping on dates
- Teaching grouping, filtering, and drill paths
Common mistakes - Pivot Table function not working
- Leaving blank rows above the dataset so the range misses rows
- Mixing multiple granularities in one pivot without hierarchy
- Forgetting to refresh after ETL updates
- Using too many fields at once for readability
- Sharing files with external data connections disabled
FAQ
Do PivotTables update automatically?
They refresh when you click Refresh or when the source data changes if options are set to refresh on open—learn your workbook settings.
Can I pivot from an Excel Table?
Yes. Using Insert > Table first makes ranges grow automatically as rows are added.
What is a good first field layout?
Put categorical dimensions in Rows, numeric measures in Values, and use Filters for high-cardinality fields.
Why are my numbers counting instead of summing?
Excel inferred the field as text or count—change the Value Field Settings to Sum (or another aggregation).
Can I show percent of total?
Yes. Value Field Settings > Show Values As > Percent of Grand Total (wording varies slightly by version).
Comparison
| Tool | Best for |
|---|---|
| PivotTable | Interactive exploration |
| SUMIFS | Fixed formula grids |
| Power BI | Larger governed analytics |
Example
Rows: Region · Values: Sum of Sales
Compare with =SUMIFS(C:C, A:A, "North") for a formula-only slice.
Advanced examples
Calculated field (conceptual)
Add simple margin calculations inside the pivot when source columns already contain revenue and cost (validate version-specific steps).
Slicer + timeline
Pair slicers for categorical filters and timelines for date ranges to make dashboards approachable for non-formula users.