What a pivot table does and when you need one
A pivot table takes a large, messy spreadsheet and reorganizes it so you can see patterns without doing the math yourself. If you have a list of transactions, a pivot table can show you total spending by category. If you have survey responses, it can count how many people chose each answer. Google Sheets builds the pivot table for you — you just tell it which columns to use and how to arrange them.
You need a pivot table when your data has hundreds or thousands of rows and you want to summarize it by groups. A regular spreadsheet formula works fine if you have 20 rows and three categories. A pivot table saves hours when you have 5,000 rows and 15 categories, because the table updates automatically if your source data changes.
Key Takeaways
- A pivot table reorganizes raw data into summaries without changing the original spreadsheet.
- You start by selecting your data range, then use the Data menu to create a new pivot table.
- Rows show the categories you want to group by, columns show the breakdowns, and values show the numbers being summed or counted.
- If your source data changes, the pivot table updates automatically when you refresh it.
Prepare your data before you start
Your data needs a header row — the first row should contain column names like "Date", "Category", "Amount", or "Name". Every row below that should contain actual data. If your headers are missing or inconsistent, the pivot table will treat them as data and give you wrong results.
Check that your data has no completely blank rows or columns in the middle of the range. Google Sheets uses blank cells to figure out where your data ends, so a gap in the middle confuses it. If you have notes or labels to the right of your data, move them to a different area of the sheet first.
Select your data and open the pivot table tool
Click on any cell inside your data range. You do not need to select the entire range — Google Sheets will find the boundaries automatically. Go to the Data menu at the top and click Pivot table. A dialog box will appear asking where you want to create the pivot table. Choose New sheet to put it on a separate sheet, which keeps your original data clean and straightforward to find.
Click Create. Google Sheets opens a new sheet with a blank pivot table editor on the right side. This editor has four sections: Rows, Columns, Values, and Filters. You will use these to tell Google Sheets how to organize your data.
Add rows to group your data
The Rows section determines what categories appear down the left side of your pivot table. If you want to see totals by month, add the date column to Rows. If you want to see totals by product, add the product column. You can add multiple rows — for example, add both "Category" and "Subcategory" to see a breakdown within each category.
Click Add field under Rows and select the column you want. The pivot table updates when ready. If you add the wrong column, click the X next to it to remove it. The order matters: the first row you add becomes the main grouping, and any rows you add after that become sub-groups underneath.
Add columns to break down your rows further
The Columns section is optional and creates additional breakdowns across the top of your table. If your Rows show months and you add "Region" to Columns, your pivot table will show each month with separate columns for each region. This is useful when you want to compare two things at once.
Most pivot tables work fine with just Rows and no Columns. Add Columns only if you have a second dimension you want to compare. If your table becomes too wide to read, remove the Columns field and use Rows only instead.
Add values to show what gets counted or summed
The Values section shows the actual numbers in your table. Click Add field and select a column with numbers — usually an amount, count, or price column. Google Sheets automatically sums the values, so if you add a "Sales" column, each row shows the total sales for that group.
You can change how Google Sheets calculates the values. Click the field name in the Values section and choose a different function: SUM adds all the numbers, COUNT counts how many rows exist in each group, AVERAGE shows the mean, and MAX or MIN show the highest or lowest value. Most of the time SUM is what you want.
Use filters to hide rows you do not need
The Filters section lets you show only certain groups. If your pivot table shows all 50 states but you only care about five, add the state column to Filters. A dropdown appears above your pivot table where you can uncheck the states you want to hide. The numbers update to show only the selected states.
Filters are useful when your pivot table is too large to read at once, or when you want to focus on a specific subset. You can add multiple filters — for example, filter by both Region and Year to see only one region in one year.
Frequently Asked Questions
What happens if I change the original data?
The pivot table does not update automatically. You need to refresh it manually. Right-click anywhere in the pivot table and select Refresh, or click the refresh icon in the pivot table editor. After that, the numbers update to match your new data.
Can I use a pivot table with data from multiple sheets?
No. A pivot table can only use data from a single sheet. If your data is split across multiple sheets, copy it all into one sheet first, then create the pivot table. Make sure the headers match exactly across all the data you copy.
How do I sort the rows in my pivot table?
Click on a row label or value in the pivot table itself, then use the sort options that appear. You can sort alphabetically by the row name, or numerically by the values. The entire pivot table reorganizes based on your choice.
Can I edit the numbers in a pivot table?
No. A pivot table is read-only — you cannot change the numbers directly. If you need to fix a value, go back to the original data sheet, make the change there, and refresh the pivot table. The pivot table will then show the corrected number.
What if my pivot table shows a blank row or column?
Blank rows usually mean your original data has empty cells in that column. Check the source data for missing values and fill them in, then refresh the pivot table. If you want to hide the blank row, use the Filters section to uncheck it.