Create & Analyze Pivot Tables: From Raw Data to Summary in Minutes

A pivot table turns a large raw table into a clean summary in a few clicks — no formulas needed. This tutorial covers the foundation: preparing your data, creating a pivot, and analyzing it with fields, aggregations, sorting, and layouts.

Table of Contents

1. What is a Pivot Table?
2. Prepare Your Data
3. Create a Pivot Table
4. Add Fields
5. Change the Summary Calculation
6. Sort & Reorder
7. Change the Layout
8. Refresh Data
9. Do It with AI

What is a Pivot Table?

A pivot table is a summary tool that groups and totals your data on the fly. You drag a text field (like Region) into Rows, drag a number field (like Revenue) into Values, and Excel instantly shows total revenue per region.

Why it is useful: it answers questions like "sales by product" or "count of orders per category" in seconds, without writing a single formula. When your source data changes, one refresh updates the whole summary.

Prepare Your Data

Pivot tables only work well on clean, flat data. A flat table has:

• One header row at the top.
• No blank rows or columns inside the data.
• No merged cells.
• No subtotals baked into the data.
• Every column with a unique header name.

Here is the sample dataset used across all tutorials in this series:

Best practice: keep your source data in its own sheet (name it Data), and keep the pivot on a separate sheet. It keeps both sheets clean and makes refreshing predictable.

Common mistake: leaving a blank row or column in your data. The pivot will silently stop at the blank and miss everything below it.

Create a Pivot Table

1. Click anywhere inside your data table.
2. Go to the Insert tab.
3. Click PivotTable.
4. In the dialog, confirm the table range (Excel usually selects it automatically).
5. Choose New Worksheet so the pivot gets its own sheet.
6. Click OK.

You now see an empty pivot on the left and the PivotTable Fields pane on the right. The field list shows every column from your source data.

Add Fields

The Fields pane is split into four drop areas:

Drag Region into Rows and Revenue into Values:

• Region appears as row labels (North, South, East).
• Revenue is automatically summed — each region shows its total revenue.

Drag Category into Columns and the pivot now shows revenue split by category, one column per category.

How Excel decides what to summarize: text and date fields default to Rows/Columns; number fields default to Values. You can always drag a field anywhere you want.

Simple explanation: Rows and Columns are just the two dimensions of your summary table. Values is the math that gets done inside each cell.

Change the Summary Calculation

By default, numbers are Summed and text is Counted. You can switch to Average, Max, Min, Count, and more:

1. Right-click a number inside the Values area.
2. Choose Value Field Settings.
3. Pick a summary like Average or Max.
4. Click OK.

For example, changing Revenue from Sum to Average shows the average order value per region instead of the total.

Sort & Reorder

• Click the arrow on a field header (like Region or Category).
• Choose Sort Largest to Smallest to order by value.
• To reorder fields, drag them up or down in the Rows box of the Fields pane — the top field is the outer level, the one below it is the inner level.

You can also sort with the Data tab → Sort on the pivot's row labels.

Change the Layout

The pivot's visual structure is controlled from the Design tab (or right-click → PivotTable Options):

• Report Layout → Compact / Outline / Tabular. Tabular puts every field in its own column and looks most like a normal table.
• Subtotals — show or hide per-field subtotals.
• Grand Totals — show or hide the row/column totals.

Best practice: choose Tabular layout when you plan to copy the pivot into a report or chart — the columns line up cleanly.

Refresh Data

A pivot table is a snapshot. If you edit numbers in your source data, the pivot does not update on its own.

• Right-click the pivot → Refresh, or use the Data tab → Refresh All.
• Keyboard shortcut: Ctrl + Alt + F5 refreshes all pivots in the workbook.

To refresh automatically when the file opens: right-click the pivot → PivotTable Options → Data tab → tick Refresh data when opening the file.

Common mistake: changing the source data, then reporting stale numbers from the pivot without refreshing. Make refresh part of your workflow.

Do It with AI

Both Copilot in Excel and ChatGPT speed up these basics.

• Copilot in Excel (Microsoft 365): select your data and type Create a pivot table showing total revenue by region. Copilot analyzes the range and inserts the pivot. Follow up with Show the average order value by product. to change the summary.
• ChatGPT: paste a small copy of your table and prompt Check if this is clean enough for an Excel pivot table and list what to fix. It flags blank rows, merged cells, and text dates before you build.

Caveat: AI works from what you give it, not from your whole workbook — always verify totals with a manual check. (The full workflow is in Tutorial 7.)