How to Create a Pivot Table

Pivot tables turn thousands of rows into a clean summary in seconds without using formulas. If you've never built one, this quick guide shows you how to create a pivot table in Excel from scratch, plus how to edit, refresh, and customize it.

 

No time for reading a blog tutorial? Guidy is an AI for Excel that sees your screen and walks you through this in real time in less than one minute, in any language. Try now.

 

What is a pivot table in Excel?

A pivot table in Excel is a tool that summarizes large datasets without writing a single formula. It lets you group, count, sum, and average your data by dragging fields into rows and columns. If you have a sales spreadsheet with hundreds of rows, a pivot table can show total revenue by region in two clicks.

 

How to make a pivot table in Excel

Here's the step-by-step walkthrough:

1. Click any cell inside your data.
2. Go to Insert → PivotTable.
3. Excel auto-selects your data range. Choose whether to place the pivot table on a new worksheet or the existing one.
4. Click OK.

You'll see an empty pivot table and a Field List panel on the right. This is where you build everything.

Drag a field (like "Region") into the Rows area. Then drag a value (like "Revenue") into the Values area. Excel instantly shows a summary, e.g., total revenue per region. That's it. You just created a pivot table.

 

How to create a pivot table in Excel with multiple columns

To break your data down further, drag a second field into the Columns area. For example, with "Region" in Rows and "Product" in Columns, you'll see revenue split by both. You can also drag multiple fields into Rows to create nested groupings like Region → City → Product, etc.

Cool fact: Skipping tutorials is now possible! Get real-time help, right on your PC - in any language, for all software and browsers. Download Guidy now.

 

How to edit a pivot table in Excel

To change what your pivot table shows, open the Field List (click anywhere inside the table). Then:

• Swap fields: drag them between Rows, Columns, Values, and Filters.
• Change the calculation: click the dropdown on a Values field and choose Value Field Settings. Switch from Sum to Average, Count, Max, or Min.
• Sort or filter: click the dropdown arrow on any row or column header to sort A–Z, by value, or apply filters.

You can edit a pivot table in Excel as many times as you want without touching the original data.

 

How to refresh a pivot table in Excel

Pivot tables don't update automatically when your source data changes. To refresh a pivot table in Excel:

• Right-click anywhere inside the pivot table → Refresh.
• Or go to PivotTable Analyze → Refresh.

If you've added new rows to your dataset, you may also need to update the data range:

PivotTable Analyze → Change Data Source

 

How to use a pivot table in Excel at work

A few things that make pivot tables more useful:

• Group dates by month or quarter: right-click a date field in the pivot table, choose Group, and pick your interval.
• Add a filter: drag a field to the Filters area to add a dropdown at the top of your table (useful for filtering by year or category).
• Format numbers: right-click a value, choose Number Format, and apply currency, percentage, or whatever you need.

 

Key takeaways

A pivot table is the fastest way to summarize data in Excel. Create one from Insert → PivotTable, drag your fields in, and customize from there. Refresh it when your data changes, and edit it whenever you need a different view.