Home › Excel basics › Pivot tables

Pivot Table in Excel: Step-by-Step Guide for Beginners

A pivot table summarises a large list into a compact report: sales per region, orders per month, hours per project. You build it by dragging fields around, without writing a single formula, and you can rearrange it in seconds.

Excel CSE · 4 min read

Step 1: prepare your data

Pivot tables work on a simple list. Before you start, check that:

• The first row contains a header for every column. An empty header causes the error "The PivotTable field name is not valid".
• There are no empty rows or columns in the middle of the data.
• There are no merged cells and no subtotal rows inside the list.
• Each column holds one kind of data: dates in a date column, numbers in a number column.

We'll use this sales list:

Step 2: create the pivot table

1. Click any cell inside your data.
2. Go to Insert › PivotTable.
3. Check the range Excel suggests, choose New Worksheet and click OK.

You'll see an empty pivot table on the left and the PivotTable Fields pane on the right, with a list of your column headers and four boxes: Filters, Columns, Rows and Values.

Step 3: drag fields into place

To see total sales per region:

1. Drag Region to the Rows box.
2. Drag Sales to the Values box.

That's it: a report that would otherwise need a SUMIF per region. What each box does:

Step 4: change the calculation

Numbers are summed by default. To change that, right-click a number in the pivot table and choose Summarize Values By:

• Count: the number of sales per region instead of the total.
• Average: the average sale per region.
• Max / Min: the largest or smallest sale.

Choose Show Values As › % of Grand Total to see each region's share: North would show 52.44%.

Group dates by month or quarter

Drag Date to Rows. Newer Excel versions group dates into months automatically. If not, right-click a date in the pivot table, choose Group, and select Months, Quarters and/or Years.

Refresh after changing the data

A pivot table does not update by itself when the source data changes. Right-click the pivot table and choose Refresh, or press Alt+F5. Ctrl+Alt+F5 refreshes every pivot table in the workbook. If you added rows below the original range and didn't use a table, change the range via PivotTable Analyze › Change Data Source.

Filter with slicers

Slicers are clickable filter buttons. Click inside the pivot table and choose PivotTable Analyze › Insert Slicer, then pick a field like Region. Clicking a button filters the report instantly, which makes pivot tables great for simple dashboards.