What a pivot table does and why you need one
A pivot table is a tool that takes raw data — rows and rows of transactions, survey responses, or sales records — and reorganizes it into a summary you can actually read. Instead of scrolling through 5,000 lines to find patterns, a pivot table groups the data by the categories you choose and shows you totals, counts, or averages in seconds.
The most common use is turning a list of sales by date and product into a table where each row is a product and each column is a month, with the cell showing total revenue. You can drag fields around to change what you're looking at without touching the original data. The original spreadsheet stays untouched.
Both Excel and Google Sheets have pivot table features built in. The steps are similar but not identical, so this guide covers both.
Key Takeaways
- A pivot table reorganizes raw data into a summary grouped by the categories you choose, without changing the original spreadsheet.
- Your data must have headers in the first row, with each column representing one type of information (date, product, amount, region).
- In Excel, select your data and go to Insert > Pivot Table; in Google Sheets, select Insert > Pivot table.
- You then drag field names into Rows, Columns, Values, and Filters to build the summary layout you want.
- Once built, you can click on any number in the pivot table to see which original rows it came from.
Preparing your data before you start
Pivot tables work only if your data is organized in a specific way. Each column must have a header in the first row — something like "Date", "Product", "Amount", "Region", or "Customer Name". Every row below that should contain one record (one transaction, one survey response, one sale).
Check for blank rows or columns in the middle of your data. If row 50 is completely empty, the pivot table may stop reading at row 49. Delete any blank rows. Also check that each column contains only one type of information — don't put both the customer name and the order number in the same column.
If your data has merged cells (cells that span multiple rows or columns), unmerge them first. Pivot tables cannot read merged cells correctly. You can unmerge in Excel by selecting the merged cell, going to Home > Merge & Center, and clicking it again. In Google Sheets, select the merged cell, go to Format > Merge cells > Unmerge.
Building a pivot table in Excel
Select all your data, including the headers. The easiest way is to click the top-left cell, then press Ctrl+Shift+End (Windows) or Cmd+Shift+End (Mac) to select to the last cell with data. Then go to the Insert tab at the top and click Pivot Table.
Excel will ask where your data is. If you selected correctly, it will show the range automatically. You can also choose whether to put the pivot table on a new sheet or the same sheet. A new sheet is usually cleaner. Click OK.
You will now see the Pivot Table Fields panel on the right side. This panel lists every column header from your original data. Below that are four boxes: Filters, Columns, Rows, and Values. Drag field names into these boxes to build your summary. For example, if you want to see total sales by product and month, drag "Product" into Rows, "Date" into Columns, and "Amount" into Values. Excel will automatically sum the amounts.
The pivot table appears on the left as you build it. If you drag the wrong field into the wrong box, just drag it out and try again. You can also right-click any field in the Values box to change how it summarizes — sum, average, count, minimum, maximum, and others.
Building a pivot table in Google Sheets
Select all your data including headers. Go to the Insert menu and click Pivot table. Google Sheets will ask you to confirm the data range. If it looks right, click Create.
Google Sheets opens a new sheet and shows the Pivot table editor panel on the right. Like Excel, it has sections for Rows, Columns, Values, and Filters. Drag field names from the list at the top into these sections. The pivot table updates in real time as you drag.
If you need to change how a field is summarized, click the field name in the Values section. A menu will appear with options like Sum, Average, Count, and others. Select what you want. You can also click the field name in the Rows or Columns section to sort or filter by that field.
Common layouts and what they show
The simplest pivot table has one field in Rows and one in Values. For example, Product in Rows and Amount in Values shows the total amount for each product — a single column of numbers next to a list of product names.
Adding a field to Columns creates a two-way table. Product in Rows, Month in Columns, and Amount in Values shows each product down the left and each month across the top, with totals in the cells. This is useful for spotting seasonal patterns or comparing performance across regions.
Putting a field in Filters adds a dropdown at the top of the pivot table. You can use this to show only certain products, dates, or regions without rebuilding the whole table. For example, if you add "Region" to Filters, you can click the dropdown and select only "North" to see just that region's data.
You can drag multiple fields into the same box. If you put both Product and Category in Rows, the pivot table will group by Category first, then by Product within each category. This creates a hierarchy that you can expand or collapse by clicking the plus and minus signs.
Refreshing the pivot table when your data changes
If you add new rows to your original data, the pivot table does not update automatically. In Excel, right-click anywhere in the pivot table and select Refresh. In Google Sheets, the pivot table updates automatically if you add data to the range it is reading from, but if you added data outside that range, you may need to edit the pivot table to expand the range.
To edit the range in Google Sheets, click the Pivot table editor panel (if it closed, click anywhere in the pivot table and it will reappear). At the top, you will see the data range. Click it and drag to include the new rows, then press Enter.
Drilling down to see which rows a number came from
One of the most useful features of a pivot table is the ability to click on any number and see the original rows that created it. In Excel, double-click any cell with a number in the pivot table. Excel will create a new sheet showing only the rows from your original data that contributed to that total.
In Google Sheets, click any number in the pivot table. A panel will appear on the right showing the rows that make up that number. You can scroll through them or copy them to another sheet if you need to investigate further.
Frequently Asked Questions
Can I edit the numbers in a pivot table?
No. A pivot table is a summary of your original data, not a separate dataset. If you need to change a number, go back to the original spreadsheet, find the row, and edit it there. Then refresh the pivot table and it will update automatically.
What if my data has text in a column I want to sum?
The pivot table will skip that column or treat it as a count instead of a sum. Go back to your original data and make sure the column contains only numbers. Remove any text, dollar signs, or commas. If the column is formatted as text instead of numbers, select it, right-click, and choose Format Cells (Excel) or Format > Number (Google Sheets) and change it to Number.
Can I create a pivot table from data in multiple sheets?
Not directly. You need to combine the data into a single sheet first. Copy all the data from the second sheet and paste it below the data in the first sheet, making sure the headers match. Then select all the combined data and create the pivot table.
How do I remove a field from the pivot table?
In Excel, drag the field name out of its box in the Pivot Table Fields panel. In Google Sheets, click the X next to the field name in the Pivot table editor. The pivot table will update when ready.
Can I sort a pivot table by the values instead of alphabetically?
Yes. In Excel, click any cell in the column you want to sort, then go to Data > Sort and choose ascending or descending. In Google Sheets, click the field name in the Rows or Columns section of the Pivot table editor, then click Sort range and choose ascending or descending.