What a pivot table does and when you need one

A pivot table is a tool in Excel that reorganizes raw data into a summary you can read at a glance. Instead of scrolling through hundreds of rows to find patterns, a pivot table groups data by the categories you choose and adds up the numbers automatically. If you have a list of sales by date and product, a pivot table can show you total sales by product, or by month, or by both at once.

You need a pivot table when your spreadsheet has become too large to scan by eye, or when you need the same summary in multiple different arrangements. Without a pivot table, you would manually sort and add formulas for each view you wanted. A pivot table does this in seconds and updates if your source data changes.

The data you start with must be organized in rows and columns with headers at the top — one row per transaction or record, one column per data type. If your data is scattered across cells or lacks clear headers, the pivot table will not work correctly.

Key Takeaways

  • Your source data must have headers in the first row and be organized as one record per row, with no blank rows or columns in the middle.
  • Excel creates the pivot table in a new sheet and lets you drag fields into four zones: rows, columns, values, and filters.
  • The rows zone becomes the left side of your summary, the columns zone becomes the top, and the values zone is what gets added up.
  • You can change what the pivot table shows by dragging fields between zones or removing them entirely, and the summary updates when ready.
  • If your source data changes, you refresh the pivot table with one click to pull in the new numbers.

Prepare your data before you start

Open the spreadsheet with the data you want to summarize. Look at the first row — it should contain headers that describe what each column holds, like "Date", "Product", "Quantity", "Price", or "Region". If the first row is blank or contains data instead of labels, add headers now.

Scroll down and check that every row is a single record with no blank rows between groups of data. Blank rows in the middle of your data will cause the pivot table to stop reading. If you have blank rows, delete them. Also check that you have no blank columns — if column C is empty, delete it.

Click any cell inside your data range. You do not need to select the entire range; Excel will find the boundaries automatically as long as you are inside the data.

Open the pivot table dialog

In Excel, go to the Insert tab at the top of the screen. Look for a button labeled Pivot Table — the exact location depends on your version of Excel, but it is always on the Insert tab. Click it.

A dialog box will appear asking you to choose where your data is. It should already show the range of your data — for example, "Sheet1!A1:E500". If the range looks wrong, you can type the correct one. Below that, you will see two options for where to place the pivot table: "New Worksheet" or "Existing Worksheet". Choose New Worksheet unless you have a specific reason to put it on the same sheet as your data. Click OK.

Excel will create a new sheet and open the Pivot Table Field List on the right side of the screen. This is where you tell Excel how to organize your summary.

Drag fields into the four zones

The Pivot Table Field List shows all the column headers from your data. Below the list, you will see four boxes labeled Filters, Columns, Rows, and Values. These zones control how your pivot table is laid out.

Start with the Rows zone. This becomes the left side of your pivot table — the categories that run down the page. Drag a field from the list into the Rows box. For example, if you want to see totals by product, drag "Product" into Rows. You can drag multiple fields into Rows to create nested groups.

Next, drag a field into the Columns zone. This becomes the top of your pivot table — the categories that run across the page. If you want to see totals by month, drag "Month" into Columns. If you do not need columns, leave this box empty.

Drag a field into the Values zone. This is the number that gets added up. If you drag "Quantity", the pivot table will sum all quantities. If you drag "Price", it will sum all prices. Excel usually guesses correctly, but you can change it later if needed.

The Filters zone is optional. Drag a field here if you want a dropdown at the top of the pivot table that lets you show only certain categories — for example, a filter for "Region" so you can view one region at a time.

Read and interpret your pivot table

Once you have dragged fields into the zones, Excel builds the pivot table automatically. The left column shows the row categories you chose. The top row shows the column categories. Where they meet, you see the sum of the values.

Look at the bottom and right edges of the pivot table — Excel adds a row and column labeled "Grand Total" that sum everything. This is useful for checking your work.

If you added a filter, a dropdown arrow appears at the top. Click it to show only certain categories. For example, if you filtered by Region, you can click the dropdown and uncheck "West" to hide that region from the summary.

Change the pivot table layout

The Pivot Table Field List stays open on the right. You can drag fields between zones to change the layout when ready. If you want to see data grouped by month instead of by product, drag "Product" out of the Rows zone and drag "Month" in. The pivot table updates in seconds.

You can also drag a field out of a zone entirely to remove it from the pivot table. Right-click a field in one of the zones to see options like "Remove Field" or "Move to Filters".

If you want to change what gets added up — for example, to show the average instead of the sum — right-click the field in the Values zone and choose "Value Field Settings". A dialog will open where you can pick a different calculation like Average, Count, or Maximum.

Refresh the pivot table when your data changes

If you go back to your original data sheet and add new rows or change numbers, the pivot table does not update automatically. To refresh it, right-click anywhere inside the pivot table and choose Refresh. The summary will recalculate with the new data.

If you add a completely new column to your source data, the pivot table will not see it until you rebuild it. Go back to Insert > Pivot Table, and this time the dialog will show the new range that includes your new column. Create a new pivot table with the updated range.

Frequently Asked Questions

Can I move the pivot table to a different sheet?

Yes. Right-click the pivot table sheet tab at the bottom and choose "Move or Copy". You can move it to an existing sheet or create a new one. If you move it to a sheet that already has data, make sure you choose a location that does not overlap with other content.

What if my pivot table shows "Count" instead of "Sum"?

Right-click the number field in the Values zone of the Pivot Table Field List and choose "Value Field Settings". Change the calculation from Count to Sum. This usually happens when Excel detects text in a column you meant to add up.

Can I format the numbers in a pivot table?

Yes. Click any number in the pivot table, then use the formatting tools on the Home tab — you can change decimal places, add currency symbols, or change the number format the same way you would in a regular spreadsheet.

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

Deleting from a pivot table is different from deleting from regular data. Right-click the row or column header and choose "Delete" to remove it. This removes it from the summary view but does not delete anything from your source data.

Can I create a pivot table from data on multiple sheets?

Not directly. You need to combine the data into a single sheet first. Copy all the data from your other sheets into one master sheet, making sure the headers match, then create the pivot table from that combined range.