Pivot Tables in Excel and Google Sheets
A pivot table turns hundreds of rows into a neat summary, like total sales by region and month, in a few clicks and with no formulas. Learn how to set up your data, build one in Excel or Google Sheets, and practice with the live pivot builder.
What a pivot table does
Say you have a sheet with every sale your shop made: date, region, salesperson, product, units and dollars. Hundreds of rows. Your boss asks: “What were total sales in each region?”
A pivot table answers that in seconds. It groups matching rows together and summarizes them with a sum, count or average. Then you can “pivot”: drag a different field in and instantly see sales by product, by month, or by salesperson.
One row per sale
East, Coffee, $180
West, Coffee, $225
East, Tea, $64
South, Tea, $80
…and many more
One row per region
East: $1,159
South: $654
West: $1,035
Grand total: $2,848
Your original data is safe. A pivot table is a separate summary that reads from your data. Changing or deleting the pivot table never changes the rows it came from.
Set up your data first
Pivot tables are picky about layout. Most problems come from messy source data, so spend a minute on this:
- One header row at the top, with a short, unique name for every column (Date, Region, Rep, Product, Units, Sales).
- One record per row. Each sale gets its own row. Don't put months side by side as columns.
- No blank rows or columns in the middle of the data, and no totals or subtotal rows mixed in.
- One kind of data per column. Numbers in number columns, with no text like “N/A” or “$” typed in as text, and real dates in date columns.
- No merged cells. Unmerge them and fill in the value on every row.
Long and simple
Month | Region | Product | Sales
Jan | East | Coffee | 180
Jan | East | Tea | 64
Already a summary
Region | Jan | Feb | Mar
East | 328 | 330 | 501
(Months as columns: pivot tables can't group these well.)
Excel tip: make it a Table first. Click in your data and press Ctrl+T (Cmd+T on a Mac). When you add new rows to a Table, a pivot table built from it picks them up on the next refresh, so you never have to change the range.
Create a pivot table
- Click any cell inside your data (or select the whole range or Table).
- Go to Insert › PivotTable.
- Check the range shown is right. Choose New Worksheet (easiest) or Existing Worksheet, then click OK.
- An empty pivot table appears with the PivotTable Fields pane on the right. The top lists your column headers. The bottom has four areas: Filters, Columns, Rows and Values.
- Check a field's box or drag it into an area. For “sales by region”: drag Region to Rows and Sales to Values.
Pane disappeared? It only shows when a cell inside the pivot table is selected. Click inside the pivot table, or right-click it and choose Show Field List. Excel can also suggest layouts: Insert › Recommended PivotTables.
- Select the cells with your data, including the header row. Every column needs a header.
- In the menu, click Insert › Pivot table.
- Choose New sheet (easiest) or Existing sheet, then click Create.
- The Pivot table editor opens on the right with sections for Rows, Columns, Values and Filters.
- Click Add next to a section and pick a field. For “sales by region”: Add Region under Rows and Sales under Values.
Editor disappeared? Click any cell in the pivot table and an Edit button appears next to it. Google Sheets also shows Suggested pivot tables at the top of the editor.
Rows, columns, values and “summarize by”
| Area | What it does | Example |
|---|---|---|
| Rows | One row in the pivot for each unique value | East, South, West |
| Columns | One column for each unique value, making a grid | Jan, Feb, Mar across the top |
| Values | The numbers you summarize | Sum of Sales |
| Filters | Limit the whole pivot to certain records | Only Coffee |
Summarize by decides the math. Sum adds up, Count counts how many rows, Average gives the mean, and Max/Min find the largest and smallest. In Excel, click the value field in the Values area and choose Value Field Settings (or right-click a number and use Summarize Values By). In Google Sheets, use the Summarize by dropdown under the value.
24 rows of coffee shop sales: Month, Region, Rep, Product, Units and Sales. Pick the fields and watch the summary change.
See the 24 rows of source data
Grand totals are calculated from the raw rows, not from the numbers above them. So for Average, the grand total is the average of all the sales, which isn't the same as averaging the regional averages. Try it in the builder.
Filter, sort and refresh
Filter and sort
- Filters area: drag a field like Product into Filters (Excel) or click Add next to Filters (Google Sheets), then pick which values to show.
- Row and column filters: in Excel, click the dropdown arrow on the Row Labels or Column Labels header to tick values on and off, or sort.
- Sort by the numbers: in Excel, right-click a value and choose Sort › Sort Largest to Smallest. In Google Sheets, under Rows use Order and Sort by to sort by a value such as SUM of Sales.
- Slicers (Excel): big clickable filter buttons. Click in the pivot table, then PivotTable Analyze › Insert Slicer. Great for dashboards.
Refresh when the data changes
Doesn't update by itself
After you change the source data, right-click the pivot table and choose Refresh, or go to PivotTable Analyze › Refresh (Alt+F5). Use the arrow under Refresh for Refresh All. To refresh every time the file opens: PivotTable Analyze › Options › Data tab › Refresh data when opening the file.
Updates automatically
The pivot table refreshes whenever the source cells change. If you add rows below the original range, open the editor and check the data range at the top includes them.
New rows missing in Excel? If your source isn't a Table, rows added below the old range aren't included, even after Refresh. Fix it with PivotTable Analyze › Change Data Source, or convert your data to a Table (Ctrl+T) so it grows automatically.
Common problems and fixes
| Problem | Why | Fix |
|---|---|---|
| Shows “Count of Sales” instead of Sum | Excel uses Count when the column has text or blank cells | Fix the text or blanks in the source, or change it to Sum in Value Field Settings |
| A row says “(blank)” | Empty cells in that field, often a blank row inside your range | Fill in or delete the blank rows, then refresh |
| Same item listed twice (“East” and “East ”) | Typos or extra spaces in the data | Clean the source data (Find and Replace, or TRIM), then refresh |
| Dates turned into Years, Quarters, Months | Excel groups dates automatically | Right-click a date and choose Group or Ungroup |
| Numbers look wrong | Wrong summary type, a filter is on, or it wasn't refreshed | Check Summarize by, clear filters, refresh |
Check any number: in Excel, double-click a value in the pivot table. Excel opens a new sheet listing exactly the rows that make up that number.
FAQCommon questions
What's the difference between a pivot table and SUMIF formulas?
Both can total by category. A pivot table builds the whole summary for you, finds every category automatically, and lets you rearrange it with clicks. Formulas like SUMIF are better when you need a fixed layout or want the result to update instantly in Excel without refreshing. See spreadsheet formulas.
Can I make a chart from a pivot table?
Yes. In Excel, click in the pivot table and choose PivotTable Analyze › PivotChart. In Google Sheets, select the pivot table cells and use Insert › Chart.
Can I type over numbers in a pivot table?
No. Pivot table numbers are calculated. Change the source data instead, then refresh. If you need a static copy, copy the pivot table and use Paste Special › Values somewhere else.
Do pivot tables work in Excel on a Mac or on the web?
Yes. Excel for Mac and Excel for the web both have Insert › PivotTable. Some advanced options are only in the Windows desktop version, but the basics are the same.
Why can't I add a calculated column in my source and see it?
A new column only shows up in the field list after a refresh, and only if it's inside the pivot table's data range. A Table in Excel includes new columns automatically.