What a pivot table does and when you need one
A pivot table takes a large, messy dataset and reorganizes it into a summary that answers a specific question. Instead of scrolling through thousands of rows, a pivot table counts, totals, or averages your data and arranges it so patterns become visible. If you have a spreadsheet of sales transactions and want to know total revenue by region, or a list of expenses and want to see spending by category month-by-month, a pivot table builds that summary for you in seconds.
You do not need to learn formulas or create the summary manually. The pivot table feature in Excel and Google Sheets handles the grouping and calculation. When your data changes, you can refresh the pivot table and it updates automatically. This is why pivot tables are faster than sorting and filtering when you need to see data from multiple angles.
Key Takeaways
- A pivot table reorganizes raw data into a summary that shows totals, counts, or averages grouped by the categories you choose.
- Your data must have headers in the first row, with each column representing a single type of information (like date, amount, or category).
- In Excel, you select your data and use the Insert menu; in Google Sheets, you use the Data menu and choose Pivot Table.
- You drag fields into rows, columns, and values areas to decide how the summary is organized and what gets calculated.
- Pivot tables do not change your original data — they create a separate summary sheet you can refresh when the source data updates.
Preparing your data so a pivot table can read it
Pivot tables work only on data that is organized in a specific way. Each column must represent one type of information — for example, Date, Product, Region, Amount Sold. The first row must contain headers (the column names). Every row below that is one record: one transaction, one expense entry, one survey response.
Before you create a pivot table, scan your data for common problems. If some cells are blank, that is usually fine — the pivot table will group them as "empty" or skip them depending on what you are doing. If a column mixes numbers and text (like "5 units" in one cell and "10" in another), the pivot table may not calculate correctly. If dates are stored as text instead of actual dates, grouping by month or year will not work. Clean these up first by editing the cells directly or using Find and Replace.
You do not need to select every row and column. The pivot table wizard will ask you to confirm the range, and you can adjust it there. But make sure your headers are included — the pivot table uses them to label the fields you will drag around.
Creating a pivot table in Excel
Open your spreadsheet and click any cell inside your data range. You do not need to select the entire range first. Go to the Insert menu and click Pivot Table. Excel will open a dialog asking you to confirm the data range. Check that it includes all your data and your headers, then click OK.
Excel creates a new sheet and opens the Pivot Table Fields panel on the right side. This panel lists all the columns from your data. You will see four areas below the field list: Filters, Columns, Rows, and Values. Drag a field into each area to build your summary. For example, if you want to see total sales by region, drag Region into Rows and Amount into Values. Excel will automatically sum the amounts. If you want to break that down by month, drag Date into Columns as well.
As you drag fields, the pivot table updates in real time on the left. Once it shows what you need, you are done — the pivot table is ready to use. If you need to change it later, right-click the pivot table and choose Pivot Table Options, or use the Fields panel to drag fields to different areas.
Creating a pivot table in Google Sheets
Click any cell in your data range. Go to the Data menu and select Pivot Table. Google Sheets opens a new sheet and shows a setup wizard. The first screen asks you to confirm the data range. Make sure it includes your headers and all your data, then click Create.
Google Sheets opens the pivot table editor on the right side. You will see sections for Rows, Columns, Values, and Filters. Click Add next to Rows and choose a field from your data — for example, Region. Click Add next to Values and choose the field you want to calculate, like Amount. Google Sheets defaults to summing numbers, but you can click the field name to change it to count, average, or other calculations.
The pivot table appears on the left as you build it. If you want to add more detail, click Add next to Rows again and choose another field, like Product. The pivot table will now show totals broken down by both Region and Product. When you are satisfied, close the editor. Your pivot table is now a separate sheet you can reference or share.
Choosing what goes in rows, columns, and values
The layout of your pivot table depends on what question you are trying to answer. Rows are the categories that run down the left side — typically the thing you want to group by, like Region, Product, or Department. Columns are the categories across the top — often time periods like Month or Year, or a second grouping like Status. Values are the numbers that get calculated — usually amounts, counts, or averages.
A straightforward pivot table might have only Rows and Values: regions down the side, total sales in the middle. A more detailed one might have Rows (Region), Columns (Month), and Values (Total Sales), so you see how each region performed each month. You can add multiple fields to any area. If you add both Product and Region to Rows, the pivot table will show products nested under each region.
Start straightforward. Build a pivot table with one field in Rows and one in Values, see if it answers your question, then add more fields if you need them. You can always edit the pivot table later by dragging fields around or removing them.
Refreshing a pivot table when your data changes
Your original data and your pivot table are separate. When you add new rows to your source data, the pivot table does not automatically include them. You must refresh it manually. In Excel, right-click the pivot table and choose Refresh. In Google Sheets, click the refresh icon (circular arrow) at the top of the pivot table editor, or right-click the pivot table and select Refresh.
If you added new rows to your data and the pivot table still does not include them after refreshing, the data range may not be set correctly. In Excel, right-click the pivot table, choose Pivot Table Options, and check the range. In Google Sheets, open the pivot table editor and confirm the range at the top. You may need to expand it to include the new rows.
Refreshing does not erase any changes you made to the pivot table itself — like filtering, sorting, or formatting. It only recalculates the numbers based on the current source data.
Common mistakes and how to avoid them
The most common mistake is putting a field in the wrong area. If you drag a number into Rows instead of Values, the pivot table will list every unique number as a separate row instead of summing them. If you drag a category into Values, it will count occurrences instead of calculating what you wanted. The fix is straightforward: drag the field to the correct area or remove it and add the right field instead.
Another frequent problem is forgetting that the pivot table uses only the data range you specified. If your source data grows, the pivot table will not see the new rows until you refresh and update the range. Check the range before you refresh, especially if you added data recently.
A third issue is mixing data types in a single column. If one cell says "5" and another says "five", the pivot table may not group or calculate them together. Before creating the pivot table, make sure each column contains consistent data — all numbers, all dates, or all text — depending on what that column represents.
Frequently Asked Questions
Can I create a pivot table from data in multiple sheets?
No. A pivot table works only on data in a single sheet. If your data is split across multiple sheets, copy it into one sheet first, making sure the columns line up. Then create the pivot table from that combined data.
What if I want to count how many times something appears instead of summing amounts?
Drag the field you want to count into the Values area. In Excel, it will default to Count if the field contains text, or Sum if it contains numbers. In Google Sheets, click the field name in the Values area and change the function from Sum to Count. You can also choose Average, Min, Max, or other calculations.
Can I edit the numbers in a pivot table directly?
No. A pivot table is a summary calculated from your source data. If you need to change a number, edit it in the original spreadsheet and refresh the pivot table. Any edits you make directly in the pivot table will be overwritten the next time you refresh.
How do I delete a pivot table I no longer need?
In Excel, right-click the pivot table and choose Delete. In Google Sheets, right-click the pivot table sheet tab at the bottom and select Delete Sheet. The original data is not affected — only the pivot table summary is removed.
Can I copy a pivot table to another file?
Yes, but it works differently in Excel and Google Sheets. In Excel, you can copy the pivot table sheet and paste it into another workbook, though it will no longer refresh from the original data. In Google Sheets, you can copy the pivot table to another sheet in the same file, but copying to a different file requires copying the source data as well.