What a pivot table does and when you need one
A pivot table is a tool that reorganizes raw data into a summary you can actually read. Instead of staring at thousands of rows, a pivot table groups information by categories you choose — like sales by region, expenses by month, or inventory by product type — and shows you totals, averages, or counts for each group.
You need a pivot table when your spreadsheet has grown too large to spot patterns by eye. If you have a list of transactions, customer records, or survey responses and you want to see "how much did we sell in each state?" or "which product appears most often?" a pivot table answers that in seconds. Without one, you would spend hours sorting and adding formulas.
The process works the same way in both Excel and Google Sheets, though the buttons live in slightly different places. Both tools pull from data you already have — they do not change your original spreadsheet, they just rearrange it into a new sheet you can read and filter.
Key Takeaways
- A pivot table summarizes large datasets by grouping rows into categories and showing totals, counts, or averages for each group.
- Your data must have headers in the first row, with no blank rows or columns mixed in, or the pivot table will not recognize all your information.
- In Excel, select your data and go to Insert > Pivot Table; in Google Sheets, select your data and go to Data > Pivot Table.
- You build the pivot table by dragging fields into four zones: Rows (what you want to group by), Columns (optional second grouping), Values (what to count or sum), and Filters (optional way to narrow the view).
- The pivot table appears on a new sheet and updates automatically if you change the original data, so you can refresh it instead of rebuilding it.
Preparing your data so the pivot table recognizes it
Before you create a pivot table, your data must be organized in a specific way or the tool will miss columns or misread your information. The first row must contain headers — the names of each column, like "Date", "Product", "Region", "Sales Amount". Every column should have a header, even if some cells below are empty.
Remove any blank rows or columns in the middle of your data. If row 50 is completely empty, the pivot table may think your data ends at row 49. Delete extra spaces, and make sure each column contains only one type of information — do not mix text and numbers in the same column, and do not use merged cells.
Check that dates are formatted as dates, not text. If a date looks like "1/15/2024" but is stored as text "1/15/2024", the pivot table may not group it correctly by month or year. Select the date column, right-click, and choose Format Cells (Excel) or Format > Number (Google Sheets) to set it as a date type. Once your data is clean, select the entire range including headers — you can click the top-left cell and press Ctrl+A (Windows) or Cmd+A (Mac) to select all, or drag to highlight just the data you want to summarize.
Creating a pivot table in Excel
With your data selected, go to the Insert tab at the top of the ribbon and click Pivot Table. Excel will show you a dialog asking where your data is — it usually guesses correctly if you selected it first. Choose whether you want the pivot table on a new sheet (recommended for clarity) or the existing sheet, then click Create.
A new sheet opens with a blank pivot table and a panel on the right side labeled Pivot Table Fields. This panel lists every column header from your original data. Below the field list are four zones: Rows, Columns, Values, and Filters. You build the pivot table by dragging fields into these zones.
Start by dragging a field to Rows — this is usually the category you want to group by, like "Product" or "Region". The pivot table will list each unique value in that field down the left side. Next, drag a field to Values — this is the number you want to summarize, like "Sales Amount" or "Quantity". Excel automatically sums numbers, but you can change it to average, count, or other calculations by double-clicking the field in the Values zone and choosing a different function.
If you want a second level of grouping, drag another field to Columns. For example, if Rows is "Product" and Columns is "Month", the pivot table will show each product down the left and each month across the top, with sales totals in the cells where they meet. Leave Filters empty for now unless you want to narrow the data — for instance, filtering to show only one region.
Creating a pivot table in Google Sheets
With your data selected, go to the Data menu and click Pivot Table. Google Sheets opens a dialog asking where you want the pivot table — choose a new sheet. Click Create, and a new sheet appears with a blank pivot table and a panel on the right labeled Pivot Table Editor.
The Pivot Table Editor has the same four zones as Excel: Rows, Columns, Values, and Filters. Click Add next to Rows and choose a field from the dropdown — this is your grouping category. Click Add next to Values and choose a field to summarize. Google Sheets defaults to summing numbers, but you can click the field name in the Values zone to change it to average, count, or other options.
Add a second grouping by clicking Add next to Columns if you want to cross-tabulate — for example, showing sales by product and by month on the same table. Use Filters to narrow the view; for instance, if your data includes multiple years, you can filter to show only 2024. Unlike Excel, Google Sheets updates the pivot table when ready as you drag fields, so you see the result when ready.
Changing calculations and sorting your pivot table
By default, pivot tables sum numbers. If you want a different calculation — like an average, a count of rows, or a minimum value — you can change it. In Excel, double-click the field name in the Values zone and choose a function from the list. In Google Sheets, click the field name in the Values zone and select a function from the Summarize By dropdown.
To sort the pivot table, click any cell in the row or column you want to sort, then use the Data menu to sort ascending or descending. In Excel, you can also click the dropdown arrow next to a row or column header to sort or filter that group. In Google Sheets, the same dropdown arrows appear — click them to reorder or hide specific categories.
If you want to rename a field or remove it, right-click the field name in the pivot table itself (not the panel) and choose the option to delete or edit. Changes to the pivot table do not affect your original data — the original sheet stays exactly as it was.
Refreshing the pivot table when your data changes
If you go back to your original data and add new rows or change numbers, the pivot table does not update automatically in Excel — you have to refresh it. Right-click anywhere in the pivot table and choose Refresh, or go to the Analyze tab and click Refresh. In Google Sheets, the pivot table updates automatically whenever the source data changes, so you do not need to do anything.
If you add a new column to your original data after creating the pivot table, you will need to rebuild the pivot table to include that column. The easiest way is to delete the pivot table sheet and create a new one from the updated data. Select your data again (including the new column), go to Insert > Pivot Table (Excel) or Data > Pivot Table (Google Sheets), and build it the same way as before.
Common mistakes and how to fix them
The most common mistake is leaving blank rows or columns in your data. If row 30 is empty, the pivot table thinks your data ends at row 29 and ignores everything below. Delete blank rows before creating the pivot table. Another mistake is forgetting headers — if the first row does not contain column names, the pivot table will treat your first data row as headers and skip it in the summary.
If a pivot table shows "Grand Total" but the numbers look wrong, check that you are using the right calculation. If you dragged a text field into Values, it will count rows instead of summing. Drag a number field instead. If dates are not grouping by month or year the way you expect, the column is probably formatted as text — go back to your original data, select the date column, and format it as a date type.
If you cannot find a field you want to add, scroll down in the Pivot Table Fields panel (Excel) or the field list (Google Sheets) — the field is there, you just need to scroll to see it. If the pivot table is taking a very long time to load, your dataset may be too large; try filtering the original data to a smaller range before creating the pivot table.
Frequently Asked Questions
Can I create a pivot table from data in multiple sheets?
No, a pivot table pulls from a single continuous range. If your data is split across sheets, copy it all into one sheet first, making sure the headers match. Then select the combined data and create the pivot table.
What if I want to show the count of items instead of the sum?
Drag a field to Values, then change the calculation. In Excel, double-click the field and choose Count. In Google Sheets, click the field name and select Count from the Summarize By dropdown. You can count any column, even text columns.
Can I edit the numbers in a pivot table directly?
No, a pivot table is a summary view only. If you need to change a number, go back to the original data, make the change there, and refresh the pivot table. The pivot table will update to show the new total.
How do I delete a pivot table?
In Excel, right-click the pivot table sheet tab at the bottom and choose Delete Sheet. In Google Sheets, click the sheet tab, then click the three dots menu and choose Delete Sheet. Your original data remains untouched.
Can I copy a pivot table and paste it as regular data?
Yes. Select the entire pivot table, copy it, then right-click in a new location and choose Paste Special > Values Only. This converts the pivot table into a static table of numbers that you can edit like any other data.