How to Use Pivot Tables in Excel to Summarise Data

What a Pivot Table Actually Does

A pivot table takes a long list of rows — sales transactions, timesheets, invoices, jobs — and instantly summarises it by any field you choose. Instead of writing formulas, you drag the fields you want to see and Excel does the maths.

If you have ever opened a spreadsheet with hundreds of rows and thought "I just need totals by month" or "I just need a count by client", a pivot table is the answer.

Before You Start

Your data needs to be in a clean table format:

• One row per record (e.g. one row per sale, one row per invoice)
• Each column needs a heading in the first row
• No blank rows or merged cells inside the data
• Numbers in number columns, dates in date columns, text in text columns

If your data is messy, fix it first. Pivot tables are unforgiving of merged cells and blank header rows.

Step 1: Insert the Pivot Table

• Click anywhere inside your data
• On the Insert tab, click PivotTable
• Excel will auto-select your data range — check it covers all your rows
• Choose New Worksheet (recommended for first-time users)
• Click OK

A new sheet opens with an empty pivot table on the left and the PivotTable Fields panel on the right.

Step 2: Build the Summary

The Fields panel has four boxes at the bottom: Filters, Columns, Rows, Values.

To answer "Total sales by month":

• Drag Date into the Rows box
• Drag Amount into the Values box
• Excel automatically groups dates by month and sums the amounts

To answer "Number of jobs per technician":

• Drag Technician into the Rows box
• Drag Job ID into the Values box
• Click the small arrow next to "Sum of Job ID" in the Values box and choose Value Field Settings → Count

Step 3: Add a Second Dimension

Want sales by month and by client?

• With the pivot table from Step 2 open
• Drag Client into the Columns box
• You now have a grid: months down the side, clients across the top, totals in every cell

This is where pivot tables earn their reputation.

Step 4: Refresh When Your Data Changes

Pivot tables do not update automatically. After you add new rows to your source data:

• Click anywhere inside the pivot table
• On the PivotTable Analyze tab, click Refresh

To make this less painful, format your source data as a Table first (Ctrl + T) and the pivot will pick up new rows automatically when you refresh.

Step 5: Common Adjustments

Change Sum to Average, Count, Min or Max: Right-click any number in the pivot → Summarize Values By → pick what you need.

Format the numbers as currency: Right-click a number → Number Format → Currency.

Remove the "Sum of" prefix: Click the cell that says "Sum of Amount" and just type a new label like "Total Sales".

Filter the results: Drag a field into the Filters box at the top, or use the small arrows on the row/column headings.

Common Pitfalls

• Dates appear as text, not grouped by month. Your date column is stored as text, not as a real date. Convert the column to dates first.
• Blank rows in the pivot. You have blank cells in your source data — fill them with 0 or "Unknown".
• "Field name is not valid" error. A column in your source data does not have a heading. Add one.
• Numbers showing as Count instead of Sum. That column has at least one text cell in it. Find and fix the text cell.

When to Use a Pivot Table vs a Formula

Use a pivot table when you want to slice the same data lots of different ways, or when you need a quick summary you can share.

Use a formula (like SUMIFS or COUNTIFS) when you need the result to live inside a larger spreadsheet that recalculates automatically.

Most Netluma IT clients end up using both — a pivot table for ad-hoc analysis, formulas for monthly reports.

Need Help?

If you would like Netluma IT to build a reporting template for your business or train your team on Excel, contact us.

Phone: 1300 521 162 Email: helpdesk@netlumait.com.au