What a pivot table does and when you need one

A pivot table is a tool that summarizes data from a larger spreadsheet by grouping it and counting or adding it up in the way you choose. If you have a list of 500 transactions and want to know how much you spent in each category, or how many sales came from each region, a pivot table does that work for you instead of requiring you to write formulas or sort by hand.

You build a pivot table when your data has gotten too large or too messy to read as a flat list. The original data stays untouched — the pivot table sits beside it and shows only the summary you asked for. If the source data changes, you can refresh the pivot table in one click and it updates automatically.

Key Takeaways

  • A pivot table requires data arranged in rows and columns with headers, where each row is one record and each column is one type of information.
  • You select your data range, then use the Insert menu to create a new pivot table, and Excel opens a dialog where you drag fields into rows, columns, and values.
  • Dragging a field into the Values area automatically sums numbers or counts text, but you can change that to average, minimum, maximum, or other calculations.
  • Once built, you can filter the pivot table, sort it, or refresh it if the source data changes — the original spreadsheet is never altered.

Preparing your data so a pivot table can read it

A pivot table needs data in a specific shape: one header row at the top, and then one row per record, with each column holding one type of information. If your data is scattered across cells, has blank rows in the middle, or mixes headers with data, the pivot table will not work correctly.

Before you start, check that every column has a name in the first row. If one column is "Date" and another is "Amount", those names must appear in row 1. Check that there are no blank rows or columns inside your data — a blank row tells Excel the data has ended. If you have subtotals or notes below your data, move them to a different sheet or delete them.

Select the entire data range including the header row. The easiest way is to click the top-left cell of your data, then press Ctrl+Shift+End (on Windows) or Command+Shift+End (on Mac) to select everything. If that selects too much, click the top-left cell, hold Shift, and click the bottom-right cell of your actual data instead.

Creating the pivot table from the Insert menu

With your data selected, go to the Insert menu at the top of the screen. Look for the option that says "Pivot Table" — in newer versions of Excel it may say "PivotTable". Click it, and Excel will ask you to confirm the data range. The range should match what you selected; if it does not, correct it and click Next or OK.

Excel then asks where you want the pivot table to go. You can place it on a new sheet (the safer choice if you are new to this, because it keeps your original data and the summary separate) or on the same sheet in an empty area. Choose "New Worksheet" unless you have a specific reason not to, then click OK or Create.

A new sheet opens with the Pivot Table Field List on the right side. This is where you tell Excel which columns to summarize and how. Do not close this panel — you need it to build the table.

Dragging fields into rows, columns, and values

The Pivot Table Field List shows every column header from your original data. Below that are four boxes: Filters, Columns, Rows, and Values. You build the pivot table by dragging field names into these boxes.

Start with the Rows box. If you want to see a summary for each category, drag the category field name into Rows. For example, if you have a "Department" column and want to see totals by department, drag "Department" into the Rows box. Each unique department name will appear as a separate row in the pivot table.

Next, drag the field you want to summarize into the Values box. If you drag "Sales Amount", Excel will automatically add up all the sales amounts. If you drag "Transaction ID", Excel will count how many transactions there are. The pivot table now shows each department and its total sales in two columns.

If you want to break the summary down further — for example, by both department and month — drag a second field into the Rows box below the first one. The pivot table will now show department, then month within each department, then the total. You can drag fields into the Columns box to spread the summary across columns instead of rows, which is useful when you want to compare two categories side by side.

Changing how the pivot table calculates: sum, count, average, and more

By default, Excel sums numbers and counts text. If you dragged a column of prices into Values, it adds them up. If you dragged a column of names, it counts how many names appear. Sometimes you need a different calculation.

To change how a field is calculated, double-click the field name in the Values box. A dialog opens with options: Sum, Count, Average, Min, Max, and others. Choose what you need. If you want to see the average sale price instead of the total, select Average and click OK.

You can also drag the same field into Values multiple times and calculate it different ways. For example, you might want to see both the total sales and the average sale in the same pivot table. Drag "Sales Amount" into Values twice, then set one to Sum and one to Average. The pivot table will show both columns.

Filtering and sorting the pivot table after you build it

Once the pivot table is built, you can filter it to show only certain rows. Small dropdown arrows appear next to each row header. Click the arrow next to "Department" and you can uncheck departments you do not want to see — the pivot table updates when ready to hide those rows and recalculate the totals.

To sort the pivot table, click any cell in the column you want to sort by, then use the Data menu and choose Sort A to Z or Sort Z to A. You can also right-click a row header and choose Sort to open more options, like sorting by the values in the summary column instead of alphabetically.

If the source data changes — for example, you add new transactions to the original sheet — the pivot table does not update automatically. Go to the Analyze tab (or PivotTable Tools menu in older Excel), find the Refresh button, and click it. The pivot table recalculates using the new data.

Common mistakes and how to fix them

The most common mistake is having blank rows or merged cells in the source data. Excel reads a blank row as the end of your data and stops there. If your pivot table is missing rows, go back to the original sheet, delete any blank rows, and rebuild the pivot table.

Another mistake is dragging the wrong field into Values. If you drag a text field like "Name" into Values, the pivot table counts names instead of adding numbers. If that happens, drag the field out of the Values box and drag the correct numeric field in instead.

If the pivot table shows "Sum of [Field]" as a header but you wanted Count, double-click the field in the Values box and change the calculation type. If the pivot table is too wide or tall, you can move it by selecting the entire table and dragging it, or delete it and rebuild it with different fields in Rows and Columns.

Frequently Asked Questions

Can I edit the numbers inside a pivot table?

No. A pivot table is a summary that recalculates from the source data, so you cannot change the numbers directly. If you need to change a value, go back to the original sheet, edit the source data, and refresh the pivot table. The change will appear automatically.

What if I want to see the original rows that make up each total?

Double-click any number in the pivot table and Excel opens a new sheet showing only the source rows that created that total. This is called drilling down. It is useful for checking where a number came from or finding specific transactions.

Can I move a pivot table to a different location?

Yes. Click any cell in the pivot table to select it, then select the entire table by pressing Ctrl+A (or Command+A on Mac). Cut it with Ctrl+X, click the cell where you want it, and paste with Ctrl+V. The pivot table moves but keeps all its settings.

What happens if I delete a row or column from the pivot table?

You cannot delete individual rows or columns from a pivot table the way you would from a regular spreadsheet. Instead, use the filter dropdown to hide the rows you do not want to see, or rebuild the pivot table without that field in the Rows or Columns box.

Can I use a pivot table with data from multiple sheets?

Not directly. A pivot table reads from one data range at a time. If your data is split across sheets, copy it all into one sheet first, arrange it so each row is one record, then build the pivot table from that combined data.