To create a pivot table in Excel: select any cell inside your data, then on the Insert tab select PivotTable, confirm the source range and choose New Worksheet or Existing Worksheet, and select OK. In the PivotTable Fields pane that opens, drag a category field to the Rows area and a number field to the Values area. Excel groups the list by category and totals the numbers, so a few hundred rows collapse into a short summary you can rearrange by dragging fields.
This page covers the whole feature at a working level: creating a pivot table and the fields pane, what the Rows, Columns, Values, and Filters areas do, changing a total from Sum to Count or Average and fixing the pivot that counts when it should sum, refreshing when the source data changes, and adding a slicer, a calculated field, and a pivot chart. The Insert > PivotTable path is the same across the desktop app, Excel for the web, and Mac; the contextual ribbon tab you use afterward is named PivotTable Analyze on current Windows Excel and is scoped below where platforms differ.
How do I create a pivot table?
Select any one cell inside your data, then on the Insert tab select PivotTable. Excel reads the block around that cell and proposes it as the source range, so lay your data out in columns under a single header row. Confirm the range, choose New Worksheet to drop the report on its own sheet or Existing Worksheet and a cell for where it lands, and select OK. A blank pivot table and the PivotTable Fields pane appear.
Take a flat list like this one:
| A | B | |
|---|---|---|
| 1 | Region | Amount |
| 2 | East | 100 |
| 3 | West | 250 |
| 4 | East | 150 |
| 5 | West | 300 |
Drag Region to Rows and Amount to Values, and the four rows collapse to one line per region with a total, plus a Grand Total:
| A | B | |
|---|---|---|
| 1 | Row Labels | Sum of Amount |
| 2 | East | 250 |
| 3 | West | 550 |
| 4 | Grand Total | 800 |
If you would rather not build the layout by hand, Insert > Recommended PivotTable proposes a few finished layouts from your data and drops in the one you pick (this route is available to Microsoft 365 subscribers). Nothing you do in the pivot table alters the source, because a pivot table works from a cached snapshot of the data rather than the live cells.
What do the Rows, Columns, Values, and Filters areas do?
The fields pane lists your column headers at the top and has four drop zones below. Where you drag a field decides how the report is shaped:
- Rows puts one line per unique value down the left side. Region in Rows gives you an East row, a West row, and so on.
- Columns does the same across the top, so a field in both Rows and Columns builds a grid, for example Region down the side and Quarter across the top.
- Values is what gets calculated in the body of the table, usually a Sum of a number field. This is the area that fills the cells where a row and a column meet.
- Filters puts a field above the report as a dropdown that limits the whole pivot table to the values you choose, without taking up a row or column.
You can drag a field straight to an area, or select its check box in the pane. By default Excel routes non-numeric fields to Rows, date and time fields to Columns, and numeric fields to Values, and you can move any field to another area by dragging it. Drop two number fields into Values and you get two total columns side by side.
Why is my pivot table counting instead of summing?
By default a number field in Values is summed. When it shows a count instead, the field reads as text to Excel: “If Excel interprets your data as text, the data is displayed as a COUNT.” Almost always one value in that source column is a number stored as text, or a stray text entry, and it tips Excel to treat the whole field as text.
The lasting fix is in the source, not the pivot table: clean the offending cells so the column is uniformly numeric, which is the subject of numbers stored as text in Excel, then refresh. To set the calculation directly, select the arrow to the right of the field name in the Values area, choose Value Field Settings, and under Summarize Values By pick Sum, Count, Average, Max, Min, or another function. That same dialog is how you switch a legitimate count to an average, or a sum to a maximum, whenever the default is not the number you want.
How do I refresh a pivot table when the source data changes?
In the desktop app, a pivot table reads a snapshot, so editing the source list does not update the report until you refresh it by hand. Select a cell in the pivot table, then on the PivotTable Analyze tab select Refresh in the Data group, or press Alt+F5, or right-click anywhere in the pivot table and select Refresh. To update every pivot table in the workbook at once, select the arrow under Refresh and choose Refresh All. In Excel for the web the command sits on the PivotTable tab as Refresh All, and new pivot tables built from local workbook data have Auto Refresh on by default, so on the web those pick up source edits on their own.
One catch is worth building a habit around. Refresh re-reads the exact range the pivot table was built on, so edits to cells already inside that range show up, but rows you add below the list do not, because they fall outside the original range. There are two durable ways to keep new rows flowing in:
- Base the pivot table on an Excel table before you create it (Insert > Table). A table stretches to take in each new row you type directly beneath it, so because the pivot table points at the table, a refresh then always covers the current rows.
- Or point the pivot table at a wider range after the fact: on the PivotTable Analyze tab, in the Data group, select Change Data Source, and enter the new range in the Table/Range box.
Without one of those, a refreshed pivot table can silently omit last week’s new rows while looking complete.
How do I add a slicer to filter a pivot table?
“Slicers provide buttons that you can click to filter tables, or PivotTables.” A slicer is the visual version of the field dropdown: a floating box with one button per value, so a Region slicer has an East button, a West button, and so on, and the buttons also show which values are currently selected. Select a cell in the pivot table, choose Insert > Slicer, check the fields you want a slicer for, and select OK. Click a button to filter to that value, or hold Ctrl to select several. To clear it, select Clear Filter in the top corner of the slicer.
A slicer filters the same rows as the column dropdowns and as the Filters area, so it does not do anything a filter cannot; it is a faster, more visible control for the same job. If you mostly want to hide non-matching rows in the flat list itself rather than in a pivot table, that is AutoFilter, and reordering the list is sorting. In Excel for the web you can create a slicer for a local pivot table from the PivotTable Analyze tab.
Can I add a calculated field or a pivot chart?
A calculated field adds a value column computed from the fields already in the source, such as a commission that is a percentage of sales. Select a cell in the pivot table, then on the PivotTable Analyze tab, in the Calculations group, select Fields, Items, & Sets, then Calculated Field. In the dialog, type a Name, enter a formula such as =Sales*0.1 in the Formula box (select a field and Insert Field to reference it), and select Add. One thing to expect: the formula runs on the summed totals for each row, not on each underlying record, so =Sales*0.1 takes ten percent of each region’s total. Microsoft’s calculated-field page labels that ribbon tab Analyze; it is the same contextual tab shown as PivotTable Analyze on current Windows, and calculated fields are not offered in Excel for the web.
A pivot chart is a chart wired to the pivot table, so filtering or rearranging one updates the other. On Windows, select a cell in the pivot table and choose Insert > PivotChart, pick a chart type, and select OK. On a Mac you create the PivotTable first and then insert an ordinary chart, which then behaves like a pivot chart as you change the fields. In Excel for the web you likewise build the pivot table before charting it.
The short version
- Create: select a cell in the data, then Insert > PivotTable, confirm the range, choose New Worksheet or Existing Worksheet, and select OK.
- Shape it: in the PivotTable Fields pane, drag a category field to Rows and a number field to Values; Columns builds a grid and Filters limits the whole report.
- Sum vs count: a Values field sums by default but shows a count when one text value taints the column; clean the source (numbers stored as text) or set Value Field Settings > Summarize Values By > Sum.
- Refresh: PivotTable Analyze > Refresh (or Alt+F5, or Refresh All); new rows below the list only flow in if the source is an Excel table or you widen it with Change Data Source.
- Slicer: Insert > Slicer adds clickable filter buttons; it filters the same data as the dropdowns, just visibly.
- Extras: PivotTable Analyze > Fields, Items, & Sets > Calculated Field adds a computed column (on the totals, not each row); Insert > PivotChart adds a linked chart.
