Key takeaways
- Prepare data as an Excel Table with one header row and no blank rows or merged cells.
- Drag fields into Rows, Columns, Values and Filters to change the view instantly.
- Group dates by month, quarter or year, and numbers into ranges.
- Slicers and timelines give easy filtering and work across PivotTables and charts.
- Refresh after the source changes; most errors come from messy source data.
What is Excel PivotTables?
What do I need before building a PivotTable?
A clean source table: one header row, unique and clear column names, one record per row, no blank rows or columns, no merged cells and consistent data types. Select the data and press Ctrl+T to make an Excel Table. A Table grows automatically when you add rows, so your PivotTable can include new data after a refresh.
How do I create a PivotTable?
Click inside the Table and choose Insert > PivotTable. Choose a new worksheet. In the field list, drag fields into four areas: Rows (categories down the side), Columns (categories across the top), Values (numbers to calculate) and Filters (a page-level filter). For example, put Region in Rows, Month in Columns and Sales in Values to see sales by region and month.
How do I change the calculation?
Click a field in Values, choose Value Field Settings and select Sum, Count, Average, Max, Min or Distinct Count (if the data is in the Data Model). Use Show Values As for percent of grand total, percent of row or column, running total or difference from a previous period. Format numbers with the Number Format button in the same dialog.
How do I group dates and numbers?
Right-click a date in Rows or Columns and choose Group. Select months, quarters and years so that daily data becomes a monthly trend. For numbers, group into ranges such as 0 to 99, 100 to 199, which is helpful for age or price bands. If grouping is not available, the column may contain text or blanks; fix the source.
How do I filter and sort?
Use the filter arrows on row and column labels. Use Top 10 filters for the best items by value. Sort by a value column rather than alphabetically when you want a ranking. Use Slicers (Insert > Slicer) for clickable filters and a Timeline for dates. Connect one slicer to several PivotTables with Report Connections.
How do I add a calculation?
A calculated field creates a new field using a formula on existing fields, such as = Revenue - Cost for profit. Remember that calculated fields work on sums, so ratios use totals: a margin field would calculate total profit divided by total revenue, not an average of row margins. A calculated item adds a formula between items of one field and is used less often. For more flexible measures, use the Data Model and DAX.
How do I make a PivotChart?
Click inside the PivotTable and choose Insert > PivotChart. The chart follows the PivotTable: if you filter or slice the table, the chart changes. Choose a chart type that fits the question, such as a line for months and a bar for categories.
How do I refresh the data?
Right-click the PivotTable and choose Refresh, or use Data > Refresh All. If you added columns, refresh and the new fields appear. If you changed the source range, use Analyze > Change Data Source. Using a Table as the source avoids this problem. Turn on "Refresh data when opening the file" in the options if needed.
How do I show the underlying records?
Double-click any value to see the rows behind it on a new sheet. This is a quick way to check a number or find an error.
What are common problems and fixes?
| Problem | Likely cause | Fix |
|---|---|---|
| Numbers counted instead of summed | Text values or blanks in the column | Convert text to numbers, fill blanks |
| Dates will not group | Dates stored as text | Convert to real dates |
| New rows missing | Source is a fixed range | Use a Table and refresh |
| Duplicate-looking items | Spaces or case differences | Clean with TRIM and consistent names |
| Column width keeps changing | Autofit setting | Turn off autofit on update |
How do I make a PivotTable easy to read?
Use a compact or tabular layout, give fields clear names, apply a consistent number format, remove the grand total if it is not needed and hide field buttons on charts. Keep a note of the source and date.
Which practice exercise should I try?
Create a sheet with about 200 made-up sales rows containing date, region, product, quantity and price. Add a Revenue column. Build a PivotTable of revenue by region, then add months as columns, group the dates, add a slicer for product and make a PivotChart. Finally, double-click the largest value and check the first five records against the total. Doing this once teaches the whole workflow.
Download Excel Pivot Table Guide PDF
Excel Pivot Table Guide PDF
The PDF carries the same content as this page in a clean, print-ready layout, so you can read it offline and keep it for revision.
4 pages · 101 KB · PDF · Updated 5 Nov 2026