How to Use a Pivot Table in Excel (Step by Step)

To create a pivot table in Excel, select your data, click Insert → PivotTable → New Worksheet, then drag fields into the Rows, Columns, Values, and Filters areas to summarize them instantly. A pivot table condenses thousands of rows into a clean summary you can rearrange with a drag — no formulas, no manual sorting, and no risk of leaving a row out.

What a pivot table is (and why it beats manual work)

A pivot table is a summary view built from a source range. You choose which fields to group by and which numbers to total, and Excel rebuilds the summary every time you move a field.

Compare that to manual analysis. Sorting rows in Excel only puts records in order — it doesn’t add them up. Subtotaling or writing a SUMIF formula works, but you rebuild the formula for every category and update it by hand when the data changes. A pivot table does all of that from one source range and recalculates in a click.

Step by step: create your first pivot table

For this walkthrough, imagine a sales export with the columns Date, Region, Product, and Revenue — about 2,000 rows.

1. Click a single cell anywhere inside your data. Make sure there are no blank columns or rows inside the block.
2. Go to Insert → PivotTable. Excel highlights the range it detected.
3. Confirm the range and choose New Worksheet, then click OK.
4. The PivotTable Fields pane appears on the right, listing every column header.

If your data is a proper Excel Table (Ctrl + T), the pivot expands automatically as you add rows — a big time-saver covered in the pitfalls table below.

The four areas, explained

Drag a field name from the top of the pane into one of the four boxes at the bottom.

Sales by region × month

To build the example report: drag Region into Rows, Month into Columns, and Revenue into Values. Excel builds a grid with one row per region, one column per month, and a grand total — the exact “sales by region by month” view most managers ask for.

Change the summary: Sum, Count, Average

Excel guesses the calculation from the data type: Sum for numbers, Count for text. To change it:

1. Right-click any value cell in the pivot.
2. Choose Summarize Values By.
3. Pick Sum, Count, Average, Max, Min, or Product.

For more control — such as renaming the header or applying a number format — use Value Field Settings instead.

The “Count instead of Sum” gotcha

If a revenue column shows Count rather than Sum, Excel thinks those cells are text, not numbers. This usually happens when figures are imported with currency symbols, apostrophes, or spaces baked in. A pivot can only count text.

To fix it: select the source column, use Data → Text to Columns → Finish to coerce the values back to numbers, then right-click the pivot value → Summarize Values By → Sum and refresh.

Show values as percentages or running totals

The Values area can do more than sum. Right-click a value → Show Values As and choose:

• % of Grand Total — each cell as a share of the overall figure.
• % of Column Total — each region as a share of that month.
• Running Total In — a cumulative figure across a date field.

This turns an absolute grid into a contribution report without adding a single formula.

Group dates into months, quarters, or years

If your Rows field is a date, Excel often groups it automatically. If it doesn’t:

1. Right-click any date label inside the pivot.
2. Choose Group.
3. Select Months, Quarters, and Years — you can pick several at once.
4. Click OK.

Excel adds the grouped levels to the Rows area, and you can drag them to reorder — Year outside, Month inside, for example. It’s the fastest way to turn a daily list into a monthly trend.

Sort and filter inside a pivot

• Sort: open the drop-down arrow on a Row or Column label and choose Sort A to Z, Sort Z to A, or More Sort Options — for example, sort regions by total revenue descending instead of alphabetically.
• Filter: the same drop-down has checkboxes for the values you want, plus Label Filters and Value Filters (for example, show only regions with revenue above $50,000).

Add a slicer for one-click filtering

A slicer is a floating set of buttons that filters the pivot — much faster than opening drop-downs during a meeting.

1. Click inside the pivot table.
2. Go to PivotTable Analyze → Insert Slicer.
3. Tick the field you want to filter by (for example, Product) and click OK.
4. Click a button in the slicer to filter instantly; hold Ctrl to select several.

The same menu offers Insert Timeline, which gives you a date slider for time-based filtering.

Refresh after the source data changes

Pivot tables don’t update themselves when you edit the source. After adding or changing rows:

• Click inside the pivot and press Refresh on the ribbon, or
• Press Ctrl + Alt + F5 to refresh every pivot in the workbook at once.

If new rows were added outside the original range, click PivotTable Analyze → Change Data Source and reselect the range — or convert the source to an Excel Table so it expands automatically.

Common pitfalls

Pivot tables are the building block for a full dashboard - see how to make a dashboard in Excel.

FAQ

What is a pivot table used for?

Summarizing large datasets — totals by category, averages, counts, and cross-tabs — without writing formulas. It’s Excel’s fastest analysis tool.

What’s the difference between a pivot table and VLOOKUP?

A pivot table aggregates many rows (sums, averages). VLOOKUP looks up a single value. They’re complementary — one summarizes a dataset, the other retrieves a record from it.

Can I make a pivot table in Google Sheets?

Yes — the same concept, via Insert → Pivot table. The layout is nearly identical to Excel’s, and fields drag into Rows, Columns, Values, and Filter the same way.

Why is my pivot table showing “Count” instead of “Sum”?

Numbers formatted as text get counted, not summed. Convert the source column to real numbers with Text to Columns, then refresh the pivot table.

Does a pivot table update automatically?

No. Press Ctrl + Alt + F5 (Refresh All) after editing the source. Build the source as an Excel Table and new rows are picked up automatically the next time you refresh.