Pivot tables are the fastest way to turn a long, boring list of rows into an answer you can read at a glance: totals by category, by month, by region, or all three at once, with a few drags and drops.

If you’ve avoided them because the name sounds like a feature for accountants, here’s the honest pitch. A pivot table is just a summary you build by pointing at columns. You do not write formulas, you do not sort anything by hand, and you do not need to understand the data first. Point, click, done. It takes about four steps, and once it exists you can rearrange the whole summary in seconds.

This guide walks through both Excel and Google Sheets with the same example, then covers the beginner mistakes that produce wrong totals. Pivots are easy to build and just as easy to silently break.

What a pivot table actually is

A pivot table groups the unique values of one column and calculates something (usually a total) for each group. Your source data stays untouched; the pivot is a separate summary that reads from it.

Say you have a sales log with columns: Date, Region, Category, and Amount. Each row is one sale. With a pivot you can ask:

  • What’s the total amount per category?
  • What’s the total per category, split by month?
  • What’s the total per category for the West region only?

Without a pivot, the first question means writing a SUMIF formula for every category, and the second question means a SUMIFS per category per month: a wall of formulas that breaks the moment a new category appears. A pivot answers all three by dragging fields around. When you only need one known total, SUMIF is still the right tool; when you want to explore the whole table, a pivot is faster than anything else.

The word “pivot” comes from the ability to rotate the summary: swap rows and columns, drop a field into the filter area, and the same data tells you a different story.

Before you start: the 4 rules of pivot-ready data

Pivots are picky about source data. Fix these four things first and every step below just works:

  1. One row per record. Each sale, expense, or entry is its own row. Never keep subtotals inside the data.
  2. Headers in the first row. Column names like Date, Region, Category, Amount. No title row above them.
  3. No blank rows or blank columns inside the range. A totally empty column makes Excel guess the range wrong; blank cells in a value column force Count instead of Sum (more on that below).
  4. No merged cells. Merged headers confuse the field list. Unmerge everything.

Consistent spelling matters too: if Coffee and coffee both appear, the pivot treats them as two separate categories. That’s exactly the problem data validation dropdowns solve at the entry point: a dropdown list keeps category names identical, which keeps your pivot totals honest.

The example data

To follow along, build this small log (or download your own expense tracker template and use its data; the structure is the same):

Date Region Category Amount
2026-01-05 West Software 120
2026-01-12 East Software 80
2026-01-19 West Training 300
2026-02-02 East Software 150
2026-02-09 West Software 90
2026-02-16 East Training 200
2026-03-03 West Training 250
2026-03-10 East Software 110

Eight rows is enough to see what’s happening; everything scales to 8,000 the same way.

How to create a pivot table in Excel

Step 1: Insert the pivot

Click any single cell inside your data, go to the Insert tab, and click PivotTable. Excel pre-fills the range; check that it covers all rows and columns, choose New Worksheet, and click OK. A blank pivot and the PivotTable Fields pane appear on the right.

(Newer Excel versions also offer Recommended PivotTables on the Insert tab. It suggests sensible summaries from your data. Fine as a starting point, but building it manually once teaches you the pattern.)

Step 2: Drag fields

The right-hand pane lists every column header as a field. Build the summary by dragging:

  • Rows: drag Category here. Each unique category becomes a row.
  • Values: drag Amount here. It appears as Sum of Amount; Excel defaults to Sum for numeric columns.
  • Columns: drag fields like Region here to spread values across the top and compare East vs West side by side.

Watch the pivot update live with every drag. That’s the whole trick: rows, columns, values, filters.

Step 3: Change what it calculates

Right-click any value in the pivot and choose Summarize Values By to switch between Sum, Count, Average, Max, and Min. To see the share of the total instead of the raw sum, right-click and go to Show Values As > % of Grand Total.

To format the numbers (currency, thousands separators), right-click a value, open Value Field Settings, click Number Format, and pick the format.

Step 4: Add a filter

Drag Region into the Filters area (the top box of the pane). A filter row appears above the pivot: pick one region, or several, and every total recalculates. Excel’s fancier option is a slicer: click anywhere in the pivot, go to PivotTable Analyze > Insert Slicer, and you get clickable buttons that filter instantly.

Grouping dates and numbers

Right-click a date value inside the pivot and choose Group to collapse days into months, quarters, or years in one click. Right-clicking a numeric row value lets you group into ranges (for example, every 100).

Refreshing

A pivot is a snapshot of its source. If you edit values in the source, right-click the pivot and choose Refresh (or press the Refresh button on the Analyze tab). If you add rows below the original range, refresh is not enough; the range itself must grow. The permanent fix is to convert the source to a table (Ctrl+T) before inserting the pivot. Tables extend automatically, and a Refresh then picks up new rows forever.

How to create a pivot table in Google Sheets

Step 1: Insert the pivot

Select every cell of the data including the headers, go to Insert > Pivot table, keep New sheet selected, and click Create. Sheets opens a new tab with an empty pivot and the Pivot table editor on the right.

Step 2: Add rows, columns, and values

In the editor:

  • Next to Rows, click Add and choose Category.
  • Next to Values, click Add and choose Amount; it defaults to SUM of Amount for numbers.
  • Next to Columns, click Add and choose Region to spread totals across the top.

The table fills in as you add each field. To switch a value to an average, a count, or a max, click the value item in the editor and change the Summarize by dropdown. To show percentages, use the Show as dropdown on the same item (for example, % of grand total).

Step 3: Filter

Under Filters, click Add and pick a field. The filter works like Excel’s filter area: limit to one region, or uncheck categories you don’t want in the totals.

Step 4: Grouping and refreshing

Right-click a date or number inside the pivot and choose Create pivot date group or Create pivot group rule to collapse dates into months and numbers into ranges.

Google Sheets refreshes automatically when you edit cells inside the source range; there is no manual refresh step. The caveat is the same as Excel: rows added below the range are not seen until you update the range. Click the pivot, open the editor, click Select data range, and extend it to include the new rows.

The 5 mistakes that break beginner pivots

  1. Counting instead of summing. If a value column contains text or empty cells (most commonly numbers stored as text), Excel and Sheets quietly default to Count. A total of 5 rows shows 5, not the real sum. Clean the column so every cell is a true number with no blanks.
  2. Blanks in the data. A blank row inside the range splits your totals into a mysterious (blank) group. Delete blank rows and columns before inserting the pivot.
  3. New rows never appearing. You appended 200 rows below the source range and the pivot ignores them, because the range did not grow. Use Ctrl+T tables in Excel, update the data range in Sheets.
  4. Typing next to or inside the pivot. A pivot is calculated output; anything typed into it vanishes on refresh. Adding a column right next to the pivot in Excel triggers the “PivotTable will overwrite” block. Keep your source data on its own sheet and let the pivot live beside it.
  5. Not refreshing after source edits. In Excel, edits in the source do not flow into the pivot until you Refresh. Get into the habit: edit source, refresh pivot, check totals.

When to use something else

Pivots summarize and explore. They are not the answer to every spreadsheet question:

  • Need one specific total (“what did Client X spend?”)? A SUMIF formula is faster than building a pivot.
  • Need to look up a value from a table? That’s VLOOKUP or XLOOKUP territory, not a pivot.
  • Need a live, formula-driven report that updates as people type? Pivots need a manual Refresh in Excel; a SUMIFS-based report updates instantly.

Learn pivots first, because most “how do I total this by that?” questions end there. When they don’t, you’ll know exactly which tool does fit.

FAQ

Why doesn’t my pivot table include the new rows I just added? A pivot only reads the range it was built from. Convert the source to a table (Ctrl+T) in Excel or update the data range in Sheets, then refresh. The full fix is in the official Google Sheets docs on pivot tables.

Why does my pivot table show Count instead of Sum? Text or blank cells in the value column (including numbers stored as text) force Count. Clean the column to real numbers and change Summarize by to SUM.

Can I make a pivot table from data on multiple sheets? Not directly. Combine the ranges into one sheet first (Power Query in Excel, QUERY in Sheets), then pivot the combined tab.

Why is ‘Group’ grayed out on my dates? Grouping requires real date values. If the column contains text that merely looks like a date, convert it to an actual date first.

Microsoft’s official walkthrough, Create a PivotTable to analyze worksheet data, is worth bookmarking for the Excel-specific dialogs. But the four-step pattern above is the same everywhere: clean data, insert pivot, drag fields, refresh.

Start with the eight-row example in this guide, build the summary three different ways (by category, by region, by both), and you’ve learned 90% of what most spreadsheet users ever need from pivot tables. The other 10% is just more columns to drag.

Sources