What a pivot table does and when you need one

A pivot table takes a large, flat list of data — like a spreadsheet with hundreds of rows of transactions, sales records, or survey responses — and reorganizes it into a summary that shows patterns. Instead of scrolling through raw data, a pivot table lets you count how many times something happened, add up totals by category, or compare values across different groups in seconds.

You need a pivot table when your data has gotten too large to understand by eye. If you have a list of monthly expenses and want to know how much you spent on groceries versus utilities, or a list of sales by date and product and want to see which product sold most in each month, a pivot table will build that summary for you automatically. The original data stays untouched — the pivot table is a separate view of the same information.

Key Takeaways

  • A pivot table reorganizes raw data into a summary without changing the original spreadsheet.
  • Your data must have headers in the first row and no blank rows or columns mixed in.
  • In Excel, select your data and use the Insert menu to create a pivot table; in Google Sheets, use the Data menu.
  • Drag fields into Rows, Columns, and Values areas to decide what the pivot table will show and how it will count or add.
  • You can filter, sort, and refresh a pivot table after you build it if your source data changes.

Preparing your data before you start

Pivot tables work only if your data is organized in a specific way. The first row must contain headers — the names of each column, like "Date", "Category", "Amount", or "Product". Every row below that should contain actual data with no blank rows in between. If your spreadsheet has notes, titles, or empty space at the top, delete those first.

Check that each column contains only one type of information. If one column mixes dates and text, or if a column has some numbers and some blank cells, the pivot table will treat those inconsistently. You do not need to sort or clean the data beyond that — the pivot table will handle duplicates and different formats on its own.

Select all your data including the headers. In Excel, click any cell in your data range, then go to Insert and choose Pivot Table. In Google Sheets, select your data range, then go to Data and choose Pivot Table. Both programs will ask you to confirm the range — make sure it includes the header row and all data rows, but no blank columns to the right.

Building a pivot table in Excel

After you click Insert > Pivot Table and confirm your data range, Excel opens a new sheet and shows you the Pivot Table Fields panel on the right. This panel lists every column header from your original data. Below that are four drop zones: Filters, Columns, Rows, and Values.

Drag a field into the Rows area if you want that information to appear as row labels on the left side of the pivot table. For example, drag "Category" into Rows if you want each expense category to be its own row. Drag a field into the Columns area if you want it to appear as column headers across the top — for instance, drag "Month" into Columns if you want each month to be its own column. Drag a field into the Values area if you want Excel to count or add it up. Excel will usually choose the right calculation (sum for numbers, count for text), but you can change it by double-clicking the field in the Values area.

The Filters area at the top lets you hide certain rows or columns after the pivot table is built. For example, if you drag "Year" into Filters, you can later click a dropdown on the pivot table to show only 2023 data or only 2024 data. Build the basic structure first, then add filters if you need them.

Building a pivot table in Google Sheets

In Google Sheets, select your data range including headers, then go to Data > Pivot Table. Google will open a new sheet and show you the Pivot Table Editor panel on the right. Like Excel, it has Rows, Columns, Values, and Filters sections, but the layout is slightly different.

Click "Add" next to Rows and choose which field you want as row labels. Click "Add" next to Columns and choose which field you want as column headers. Click "Add" next to Values and choose what you want to count or sum. Google Sheets will automatically sum numbers and count text, just like Excel. If you need a different calculation, click the field name in the Values section and choose from options like Average, Count Distinct, or Minimum.

The Filters section works the same way as Excel — add a field there if you want to filter the pivot table after it is built. Google Sheets updates the pivot table in real time as you drag fields around, so you can see the result when ready instead of waiting for a dialog to close.

Choosing what goes in rows, columns, and values

The most common setup is to put one categorical field in Rows and one in Values. For example, put "Product" in Rows and "Amount" in Values, and the pivot table will show each product as a row with its total sales next to it. This works for any data where you want a straightforward list with a total.

If you want to compare across time or categories, put one field in Rows and another in Columns. For instance, put "Expense Category" in Rows and "Month" in Columns, then put "Amount" in Values. The result is a grid where each row is a category, each column is a month, and each cell shows the total spent in that category that month. This layout makes it straightforward to spot which category was highest in which month.

You can put multiple fields in the same area. If you put both "Product" and "Region" in Rows, the pivot table will show each product, then break it down by region underneath. This creates a hierarchy. In Values, you can add the same field twice with different calculations — for example, add "Sales Amount" once as a sum and once as a count, so you see both the total and the number of transactions.

Filtering, sorting, and updating your pivot table

After you build the pivot table, you can filter it without rebuilding. If you added a field to the Filters area, a dropdown will appear at the top of the pivot table. Click it to show only certain values — for example, show only January data or only the East region. In Excel and Google Sheets, you can also click the small dropdown arrow next to any row or column label to hide specific rows or columns.

To sort the pivot table, click any cell in the row or column you want to sort by, then use the Data menu to sort A to Z or Z to A, or sort by value (smallest to largest or largest to smallest). This is useful if you want to see your top products first or your highest expenses at the top.

If your original data changes — you add new rows, update amounts, or add new categories — the pivot table does not update automatically. In Excel, right-click the pivot table and choose Refresh. In Google Sheets, click the refresh icon in the Pivot Table Editor panel. This recalculates the summary based on your updated source data.

Common mistakes and how to avoid them

The most common mistake is including blank rows or columns in your data range. If row 50 is blank and your data continues in row 51, the pivot table will stop at row 49 and miss the rest. Before you create the pivot table, scroll through your data and delete any blank rows. Use Find & Replace to remove extra spaces in cells if you notice that similar values are being counted separately.

Another mistake is putting the wrong field in Values. If you put a text field like "Product Name" in Values, the pivot table will count how many times each product appears instead of adding up sales. If you put a number field like "Quantity" in Values when you meant to put "Amount", you will see quantities instead of dollars. Check the Values area and make sure each field is set to the calculation you want.

A third mistake is forgetting that the pivot table is a separate sheet. Changes you make to the pivot table do not change the original data, and changes to the original data do not appear in the pivot table until you refresh it. If you edit a number in the pivot table directly, it will not update the source data. Always edit the original spreadsheet, then refresh the pivot table.

Frequently Asked Questions

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 each sheet into one master sheet, making sure the headers match, then create the pivot table from that combined data. Some advanced users use formulas or data consolidation tools, but the simplest method is to merge the sheets manually.

What if my data has dates in different formats?

The pivot table may treat them as separate values. Before you create the pivot table, select the date column and format it consistently — in Excel, right-click and choose Format Cells; in Google Sheets, select the column and use Format > Number > Date. Once all dates are in the same format, the pivot table will group them correctly.

Can I edit the numbers in a pivot table directly?

You can type in a pivot table cell, but it will not change your original data. The next time you refresh the pivot table, your edits will disappear. Always edit the source data instead, then refresh the pivot table to see the updated summary.

How do I delete a pivot table?

In Excel, right-click the pivot table and choose Delete. In Google Sheets, right-click the sheet tab at the bottom and choose Delete Sheet. The original data is not affected — only the pivot table sheet is removed.

What is the difference between a pivot table and a formula like SUMIF?

A SUMIF formula adds up values that match one condition, like "sum all amounts where category equals groceries". A pivot table does that automatically for every category at once and lets you reorganize the view without rewriting formulas. For a single calculation, SUMIF is faster; for exploring data from multiple angles, a pivot table is more flexible.