How to Create a Pivot Table in Excel

Turn a long table into a summary — totals by category, counts by month, averages by region — in a few clicks, and refresh it when the data changes. Here's the step-by-step, plus a faster way to just ask.

By the Quiriz Team · Published July 24, 2026 · 5 min read

The quick answer: click your data, go to Insert → PivotTable → OK, then drag a field to Rows and a number to Values. That's a working summary. Below is each step, plus grouping by month and the Count-vs-Sum gotcha that trips everyone.

1. Insert the PivotTable

Click any cell inside your data — Excel picks up the whole range automatically as long as there are headers and no empty columns. Go to Insert → PivotTable, choose New Worksheet, and click OK. You get an empty pivot and a PivotTable Fields pane on the right.

2. Drag fields into place

The pane has four boxes. The two you need first:

Rows — the thing you're grouping by. Drag Region or Customer here.
Values — the number you're summarizing. Drag Amount here and Excel totals it per row group.

Add a field to Columns to cross-tabulate (e.g. Region down the side, Month across the top), and to Filters to slice the whole thing by, say, one product line.

3. Switch Sum, Count, Average

Click the field in Values → Value Field Settings and pick the calculation. Watch for this: if Excel shows Count when you wanted Sum, the column contains text or blanks somewhere. Clean it, or it silently counts instead of adding.

4. Group by month

Put a date field in Rows, right-click any date in the pivot, choose Group, and pick Months (add Years if the data spans more than one). Daily dates roll up into clean monthly totals.

5. Refresh when data changes

Pivots don't auto-update. Right-click → Refresh after new rows land. Build the pivot on an Excel Table (Ctrl+T) first and the source range grows on its own, so refresh always catches new data.

A pivot table answers one shaped question — "this number, grouped by that." When you find yourself rebuilding pivots for every new question, that's the moment to just ask the question directly instead.

Skip the pivot — just ask

Quiriz answers "total revenue by region this month" in plain English and returns it in seconds — no dragging fields, no refresh. Free to start.

Try Quiriz free →

Frequently asked questions

What is a pivot table used for?

A pivot table summarizes a large table into totals, counts or averages grouped by category — for example, total sales by region or order count by month — without writing formulas. You drag fields around and Excel recalculates the summary instantly.

How do I group a pivot table by month?

Put a date field in Rows, then right-click any date in the pivot table and choose Group. Pick Months (and Years if the data spans more than one year) and click OK. Excel rolls the daily dates up into monthly buckets.

How do I change a pivot table from Count to Sum?

Click the field in the Values area, choose Value Field Settings, and select Sum (or Average, Max, and so on). Excel defaults to Count when a column contains any text or blanks, so clean the column if you expected Sum.

Why isn't my pivot table updating with new data?

Pivot tables don't refresh automatically. Right-click the pivot and choose Refresh. If new rows fall outside the original range, build the pivot on an Excel Table (Ctrl+T) instead so the source grows automatically.