Tutorials

PivotTable Tutorial for Beginners: Summarise Data in 5 Minutes

Learn how to create an Excel PivotTable step by step: prepare your data, drag fields, group dates, refresh and avoid the common mistakes.

By ExcelPanda Team · Published · 5 min read

What a PivotTable does

Imagine a sales list with thousands of rows. You want total sales per region, or per product, or per month. Writing a formula for each is slow. A PivotTable builds that summary for you and lets you rearrange it by dragging fields around.

Get your data ready

PivotTables work best on tidy data. Before you start, check that:

  • Every column has a heading in the first row, and no two headings are the same.
  • There are no completely blank rows or columns in the middle of the data.
  • Each column holds one kind of thing: all numbers, all dates or all text.
  • There are no merged cells or subtotal rows inside the data.

Turning the range into an Excel Table first (select it and press Ctrl+T) is a good habit, because the PivotTable then grows with your data.

Build your first PivotTable

  1. Click any cell in your data.
  2. On the Insert tab, choose PivotTable.
  3. Check the range is right, choose a new worksheet, then select OK.
  4. In the field list, drag Region into Rows and Sales into Values.

You now have one total per region. Drag another field, such as Product, into Columns to split each region further.

The four areas of the field list

PivotTable field list areas

AreaWhat it doesExample
RowsLists each item down the sideOne row per region
ColumnsSpreads items across the topOne column per year
ValuesThe numbers you total, count or averageSum of sales
FiltersLets you show part of the dataOnly one sales rep

Four tweaks you will use all the time

  • Change Sum to Count or Average. Click the value field, choose Value Field Settings and pick the calculation.
  • Group dates. Right-click a date in the PivotTable and choose Group to summarise by month, quarter or year.
  • Sort. Right-click a value and sort largest to smallest to see your top performers first.
  • Show values as a percentage. In Value Field Settings, use Show Values As to see each item as a share of the total.

Refresh after the data changes

If you change or add data, the PivotTable does not always update by itself. Right-click it and choose Refresh. If you added new rows outside a Table, you may also need to change the source range.

Common mistakes

  • Blank cells or text in a number column. Excel counts the column instead of summing it.
  • Forgetting to refresh. The numbers look right but are out of date.
  • Typing over a PivotTable. Edit the source data, not the summary.

Want a plan for learning PivotTables alongside everything else? See our 30-day Excel plan.

Frequently asked questions

What is a PivotTable in Excel?

A PivotTable is a tool that summarises a large table of data. You drag fields into rows, columns and values, and Excel totals, counts or averages the data for you without writing formulas.

Do PivotTables change my original data?

No. A PivotTable reads your data and shows a summary. Your source table stays exactly as it was.

Why is my PivotTable not updating?

PivotTables do not always refresh on their own. Right-click the PivotTable and choose Refresh, or use the Refresh button on the PivotTable Analyze tab.

Why does my PivotTable count instead of sum?

Excel counts a field when the column contains text or blank cells. Clean the column so it holds only numbers, then set the value field to Sum.

PivotTable or formulas like SUMIFS?

Use a PivotTable when you want to explore and slice data quickly. Use SUMIFS when you need a fixed result in a specific cell that other formulas depend on.

Sources and disclaimers

Menu names can differ slightly between Excel versions. ExcelPanda is not affiliated with or endorsed by Microsoft. Microsoft and Excel are trademarks of the Microsoft group of companies.