Pivot Tables in Excel

A pivot table in Excel turns a long list of data into a short summary report, without formulas. You select your data, go to Insert ▸ PivotTable, and drag the fields you want into the Rows, Columns and Values areas. In a few clicks you can see total sales per region, count of orders per product, or average salary per department. It's one of the most valuable Excel skills for office and analyst jobs.

What a pivot table is

Imagine a sheet with 2,000 rows of sales: date, region, product, amount. Reading it row by row tells you nothing. A pivot table groups those rows for you, so you can answer "how much did each region sell?" in seconds. When your data changes, you click Refresh and the report updates.

Before you start: clean data

A pivot table works best when your data is a proper table:

• Each column has a single heading in row 1 (Region, Product, Amount).
• No blank rows or blank columns inside the data.
• Numbers are stored as numbers, not text.

Step by step: create a pivot table

1. Click any cell inside your data.
2. Go to the Insert tab and click PivotTable.
3. Excel guesses your data range. Choose New Worksheet and click OK.
4. The PivotTable Fields pane opens on the right. You'll see your column names as fields.
5. Drag Region into the Rows box.
6. Drag Amount into the Values box. Excel shows "Sum of Amount" automatically.
7. You now have total sales per region.

Worked example (region-wise sales)

Source data:

Drag Region to Rows and Amount to Values. The pivot table shows:

Now drag Product into the Columns box, and you get sales split by region and product in one grid.

Change how values are summarised

By default Excel sums the numbers. To count or average instead:

1. Click the arrow next to "Sum of Amount" in the Values box.
2. Choose Value Field Settings.
3. Pick Count, Average, Max, or another option.

This is how you switch from "total sales" to "number of orders" without touching the data.

Refresh when data changes

A pivot table does not update on its own. After you edit or add rows, right-click anywhere in the pivot table and choose Refresh. If you added new rows below your original range, use PivotTable Analyze ▸ Change Data Source first, or store your data as a Table (Ctrl + T) so new rows are picked up automatically.

Pro tips

• Turn your source range into a Table with Ctrl + T before inserting the pivot. New rows then flow into the report on Refresh.
• Use the Filters box to add a dropdown, for example to view one month at a time.
• Double-click any number in a pivot table and Excel opens a new sheet showing the exact rows behind that figure.

Common mistakes

• Blank headers. A column with no heading breaks the pivot. Label every column in row 1.
• Data stored as text. If amounts won't sum, they're probably text. Fix the format, then Refresh.
• Forgetting to refresh. Editing the source doesn't change the pivot until you click Refresh.
• Selecting extra empty columns. Include only your real data range, or you'll get "(blank)" rows.

Key takeaways

• A pivot table summarises long data into a readable report with no formulas.
• Insert ▸ PivotTable, then drag fields into Rows, Columns and Values.
• Change Sum to Count or Average in Value Field Settings.
• Click Refresh after the source data changes.

Practice task

Make a 15-row list with columns City, Salesperson and Sale Amount. Build a pivot table showing total sales per city, then add Salesperson to Columns to see who sold what in each city.

Pivot tables are a core job skill. Learn Excel reporting and dashboards hands-on in the ADCA (AI Integrated) program at HCI.