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 thousands of rows, a pivot table groups information by the categories you choose and shows totals, counts, or averages for each group. If you have a spreadsheet of sales transactions and want to know total revenue by product or by month, a pivot table builds that summary in seconds.

You need a pivot table when your data has many rows and you want to see patterns without doing math yourself. Common situations: comparing sales across regions, counting how many customers bought each product, tracking expenses by department, or seeing how numbers changed month to month. If your spreadsheet is small enough to scan by eye, or if you only need one or two quick calculations, a pivot table is overkill — a formula or a sort will do the job faster.

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, click the Insert menu, choose Pivot Table, and tell Excel which fields to put in rows, columns, and values.
  • The pivot table appears on a new sheet and updates automatically if you change the original data and refresh it.
  • You can drag fields between areas to rearrange the summary without rebuilding it from scratch.
  • Pivot tables work the same way in Excel on Windows and Mac, though the menu location differs slightly on Mac.

Prepare your data before you start

A pivot table only works if your data is organized in a specific way. Each column must have a header — a label in the first row that describes what that column contains. Each row below the header represents one record: one sale, one customer, one transaction. Do not leave blank rows or columns in the middle of your data, and do not mix different types of information in one column.

For example, if you are tracking sales, you might have columns for Date, Product, Region, and Amount. Each row would be one sale. If your data is scattered across multiple sheets or has headers in the middle, move it to a single sheet first and clean it up. The pivot table will only read the range you tell it to, so make sure all the data you want included is in one continuous block.

Select your data and open the pivot table dialog

Click any cell inside your data range — it does not have to be the first cell. Excel will automatically detect the entire block of data as long as there are no blank rows or columns breaking it up. If Excel guesses wrong, you can manually select the exact range by clicking the first cell, holding Shift, and clicking the last cell.

On Windows, go to the Insert menu at the top and click Pivot Table. A dialog box will open asking you to confirm the data range. Check that it shows the correct starting and ending cells. Choose whether you want the pivot table on a new sheet (the default and usually the best choice) or on the existing sheet. Click OK.

On Mac, the path is slightly different: click Data in the menu bar, then look for Pivot Table — it may say Create Pivot Table or Pivot Table Wizard depending on your Excel version. The steps that follow are the same.

Drag fields into the four areas of the pivot table

After you click OK, a new sheet opens with a blank pivot table layout on the right side and a list of your column headers (called fields) on the left. You build the pivot table by dragging fields into four zones: Rows, Columns, Values, and Filters.

Rows are the categories that appear down the left side of your summary — usually what you want to group by, like Product or Region. Columns are categories across the top — often dates or another way to split the data. Values are the numbers Excel will calculate, like sum of sales or count of transactions. Filters let you hide or show specific groups without rebuilding the whole table.

Start by dragging one field to Rows. If you want to compare groups, drag a second field to Columns. Then drag a number field to Values — Excel will usually default to summing it, which is correct for most situations. If you want to count items instead of add them up, click the field in the Values area and choose a different calculation. You can drag fields around as many times as you need; the pivot table updates when ready.

Understand what the pivot table is showing you

Once you have dragged fields into place, the pivot table appears below the field list. Each row shows one category from your Rows field, and each column shows one category from your Columns field. The cells where they meet show the calculated value — the sum, count, or average for that combination.

At the bottom and right edge, you will see totals for each row and column. These are calculated automatically and update if you change the underlying data. If a number looks wrong, go back to your original data sheet and check that the values are actually numbers, not text that looks like numbers. Text will not calculate correctly in a pivot table.

Refresh the pivot table when your data changes

If you add new rows to your original data or change existing values, the pivot table does not update on its own. Click anywhere inside the pivot table, then right-click and choose Refresh. On Mac, the option may be labeled Refresh Data. The pivot table will recalculate using the new or changed data.

If you add entirely new columns to your original data, you will need to rebuild the pivot table because the new fields will not appear in the field list. Click anywhere in the pivot table, go to Insert (or Data on Mac), and choose Pivot Table again. The process is the same, but now you can drag the new fields into the layout.

Rearrange and filter without starting over

One reason pivot tables save time is that you can change what you are looking at without rebuilding from scratch. Drag a field from Rows to Columns, or remove it entirely by dragging it out of the layout area. The pivot table recalculates when ready. This lets you experiment with different views of the same data in seconds.

To hide specific groups, drag a field into the Filters area. A dropdown will appear above the pivot table letting you choose which categories to show. This is useful when you want to focus on one region or one product without deleting rows. You can also click the small arrow next to any row or column header to expand or collapse groups.

Frequently Asked Questions

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

No, a pivot table reads from one continuous range. If your data is split across sheets, copy it all to one sheet first, making sure the headers match. Then create the pivot table from that consolidated sheet.

What if my pivot table shows "Sum" but I want to count items instead?

Click the field in the Values area, then click the dropdown arrow that appears. Choose Value Field Settings and select Count instead of Sum. The pivot table will recalculate to show how many items are in each group rather than the total.

Can I edit the numbers inside a pivot table directly?

No, a pivot table is a summary that updates from your original data. If you need to change a number, go back to the original sheet, find the row, and edit it there. Then refresh the pivot table. Editing the pivot table itself will not change your source data.

How do I delete a pivot table I no longer need?

Click anywhere in the pivot table, right-click, and choose Delete Pivot Table. If the pivot table is on its own sheet, you can also right-click the sheet tab at the bottom and choose Delete Sheet. This removes the entire sheet and everything on it.

Can I copy a pivot table and paste it somewhere else?

You can copy the pivot table and paste it as values into another location, but it will no longer be a live pivot table — it becomes a static snapshot. If you want a second pivot table that updates with your data, create a new one using the same source data instead of copying.