What a pivot table does and why you'd use one
A pivot table takes a large, flat list of data — like sales records with columns for date, product, region, and amount — and reorganizes it into a summary that shows totals, counts, or averages grouped the way you choose. Instead of scrolling through thousands of rows to find "total sales by region," a pivot table builds that summary in seconds and lets you rearrange the groupings without touching the original data.
The most common reason to build one is to answer a question your raw data can't answer quickly: "Which product sold best in each region?" or "How many customers did we gain each month?" A pivot table does the math and the organizing for you.
Key Takeaways
- A pivot table summarizes large datasets by grouping rows and calculating totals, without changing your original data.
- In Excel, you select your data and use the Insert menu to create a pivot table; in Google Sheets, you use the Data menu and select Pivot Table.
- You drag fields into Rows, Columns, and Values areas to decide how the summary is organized and what gets calculated.
- Pivot tables stay linked to your original data, so if the source data changes, you can refresh the pivot table to update the summary.
How to create a pivot table in Excel
Start by selecting all your data, including the header row. Click anywhere in your data range, then go to the Insert tab at the top and click Pivot Table. Excel will ask you to confirm the data range — make sure it includes every row and column you want summarized.
Choose whether to place the pivot table in a new sheet or an existing one. A new sheet is usually cleaner. Click Create, and Excel opens the pivot table editor on the right side of the screen. You'll see a list of all your column headers under Fields, and four drop zones below: Rows, Columns, Values, and Filters.
Drag a field into Rows to make it appear as row labels down the left side. Drag a field into Values to calculate something — Excel defaults to summing numbers, but you can change it to count, average, or other functions. If you want to break the values down further by another dimension, drag a field into Columns. For example, if you drag "Region" to Rows and "Product" to Columns, you'll see regions down the left and products across the top, with sales totals in each cell.
How to create a pivot table in Google Sheets
Select your data including headers. Go to the Data menu and click Pivot Table. Google Sheets opens a new sheet and shows you the pivot table editor on the right. Like Excel, you'll see your field names and four areas to drag them into: Rows, Columns, Values, and Filters.
The workflow is identical to Excel: drag a field to Rows to create row labels, drag a field to Values to calculate a sum (or change the function to count, average, etc.), and drag a field to Columns if you want a second dimension. Google Sheets updates the pivot table as you drag, so you see the result immediately instead of waiting for a dialog to close.
Changing what gets calculated in the Values area
By default, both Excel and Google Sheets sum numeric fields. If you want a different calculation — count, average, minimum, maximum, or others — you need to change the function on that field. In Excel, right-click the field name in the Values area and select Value Field Settings. In Google Sheets, click the field name in the Values area and a menu appears; select the function you want from the dropdown.
You can also drag the same field into Values multiple times with different functions. For example, you might want both the sum and the count of sales amounts in the same pivot table. Drag "Sales Amount" to Values twice, then set one to Sum and one to Count.
Filtering and sorting a pivot table
Drag a field into the Filters area to add a dropdown at the top of the pivot table that lets you show only certain values. For example, if you drag "Region" to Filters, you can click the dropdown and select only "North" and "South," and the entire pivot table recalculates to show only those regions.
To sort the rows or columns, click the small arrow icon next to a row or column label in the pivot table itself. Both Excel and Google Sheets let you sort A to Z, Z to A, or by the values in a particular column. Sorting a pivot table does not change your original data — it only rearranges the summary.
Refreshing a pivot table when your data changes
A pivot table stays connected to the original data. If you add new rows or change numbers in the source sheet, the pivot table does not update automatically — you have to refresh it. In Excel, right-click anywhere in the pivot table and select Refresh. In Google Sheets, the pivot table updates automatically if you change data in the same sheet, but if your source data is in a different sheet, you may need to refresh manually by clicking the refresh icon in the editor.
If you add new columns to your original data after creating the pivot table, you'll need to edit the pivot table's data range to include them. In Excel, right-click the pivot table and select Pivot Table Options, then update the range. In Google Sheets, click the pivot table, open the editor, and update the data range at the top.
Common mistakes and how to avoid them
The most frequent error is selecting data that has blank rows or columns in the middle. Both Excel and Google Sheets stop reading at the first gap, so they miss data below it. Before creating a pivot table, scan your data for empty rows and delete them, or select only the contiguous block you want to summarize.
Another common issue is forgetting that a pivot table is a summary, not a filter. If you want to see the original rows that make up a total, you have to go back to the source data — the pivot table itself shows only the grouped result. Some users also drag the wrong field to Values and end up with a count when they wanted a sum, or vice versa. Check the function on each field in Values to make sure it matches what you're trying to measure.
Frequently Asked Questions
Can I edit the numbers inside a pivot table?
No. A pivot table is a read-only summary calculated from your source data. If you need to change a number, go back to the original sheet, edit it there, and refresh the pivot table. This design protects you from accidentally changing a total without updating the rows that make it up.
What if my data has text in the Values area instead of numbers?
Pivot tables default to counting text fields instead of summing them. If you drag a text column to Values, you'll see how many times each value appears, not a sum. This is usually what you want for text, but if you need something different, change the function in the Values settings.
Can I copy a pivot table and paste it as regular data?
Yes. Select the entire pivot table, copy it, then paste it as values into a new location. This breaks the link to the source data, so it won't refresh, but it gives you a static snapshot you can edit like any other spreadsheet.
Do I need to delete the pivot table if my source data changes a lot?
No. You can keep the same pivot table and refresh it as often as you need. The only reason to delete it is if you no longer need the summary, or if the structure of your source data changes so much that the old groupings no longer make sense.