A pivot table in Excel is a powerful feature that lets you quickly summarize, analyze, explore, and present data from a larger table. Instead of manually counting, sorting, or calculating, you can rearrange fields to ask new questions of your data in seconds.
Use this tool when you need clear reports on sales by region, student scores by subject, or project time by team member. The flexible layout helps both analysts and business users spot patterns without writing complex formulas.
| Feature | Description | Benefit | Example Use Case |
|---|---|---|---|
| Interactive Layout | Drag and drop fields between rows, columns, values, and filters. | Build and modify reports without coding. | Compare quarterly revenue by product and region interactively. |
| Automatic Summarization | Excel can sum, count, average, min, max, and more. | Get totals and subtotals instantly. | Sum sales amount by salesperson or category automatically. |
| Built-in Calculations | Percent of column total, running totals, rank, difference from average. | Add advanced metrics without new columns. | Show each product’s contribution to total sales as a percentage. |
| Filter and Slicer Support | Filter data at report level and connect slicers for visual control. | Let users focus on relevant subsets easily. | Filter results for a specific date range or department dynamically. |
How to Create a Pivot Table
Start by selecting any cell inside your source data, then choose Insert, PivotTable, and pick whether to place the report on a new worksheet or the current sheet.
Excel creates an empty pivot table and opens the PivotTable Fields pane, where you can decide which fields appear as rows, columns, values, and filters.
Structuring Data for Pivot Tables
Organize your source data in a clean table with one header row, consistent units, and no blank rows or merged cells.
- Each column should represent one attribute, such as Date, Product, Salesperson, or Region.
- Use plain text headers and avoid duplicate titles.
- Ensure every row has a value in key identifier columns to avoid missing groupings.
Design Options and Customization
The layout engine lets you turn row and column headers into readable summaries quickly, like turning a long list of transactions into a concise matrix of totals per month and category.
Value Field Settings
Change the default aggregation by opening Value Field Settings to choose Sum, Count, Average, Max, Min, or custom calculations.
Report Layout and Style
Use Report Layout to show in outline form, tabular form, or repeated all labels, and apply PivotTable Styles for clearer visual scanning.
Best Practices with Pivot Tables
- Use a formatted Excel table as the source so the pivot table expands automatically when you add rows.
- Name your value fields clearly in the PivotTable Fields pane for easier interpretation by others.
- Leverage timelines and slicers for date-based filtering and interactive exploration.
- Refresh the pivot table regularly if the source data is updated by other teams or systems.
- Save a version of the layout and report structure if you need to rebuild or share standardized dashboards.
FAQ
Reader questions
Can I update the pivot table when the source data changes?
Yes, refresh the pivot table to pull in new rows, updated values, or added columns from the source range.
What happens to the pivot table when I add new columns to the source data?
By default, the pivot table may ignore new columns; you need to change the data source range or use a table reference to include them automatically.
How can I limit the number of items shown in a pivot table without deleting data?
Use label filters or value filters to show only the top or bottom items, or set a slicer to let users choose specific categories.
Can I share a pivot table without exposing the underlying data details?
Copy the pivot table as values or use file formats that display results while hiding the source, but always verify sensitive details before sharing externally.