What a pivot table does and when to use 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 your data by the categories you choose and shows totals, counts, or averages for each group. You build it by dragging column headers into four zones: rows, columns, values, and filters.
Use a pivot table when you have a large dataset and need to see patterns — like total sales by region, average order value by product, or how many customers bought in each month. The original data stays untouched; the pivot table is a separate view that updates if you change the source data.
Key Takeaways
- Select your data including headers, then go to Insert > Pivot Table and choose where the table should appear.
- Drag field names into Rows, Columns, Values, and Filters areas to shape how your summary looks.
- The Values area usually shows sums by default, but you can change it to count, average, or other calculations.
- Pivot tables do not change your original data — they create a separate summary that refreshes when you update the source.
Selecting your data and opening the pivot table dialog
Start by clicking any cell inside your data range. Your data must have headers in the first row — column names like "Date", "Product", "Region", or "Sales". Excel will detect the full range automatically, but you can also select the entire table manually by clicking the top-left cell and dragging to the bottom-right, or by clicking the top-left cell and pressing Ctrl+Shift+End.
Once your data is selected, go to the Insert tab at the top of the ribbon. Click Pivot Table (in Excel 2016 and later, it may say "Pivot Table" or show an icon). A dialog box opens asking where you want the pivot table to appear. Choose New Worksheet to put it on a fresh sheet, or Existing Worksheet if you want it on the same sheet as your data. Click OK.
Dragging fields into the pivot table layout
After you click OK, the Pivot Table Fields panel appears on the right side of your screen. This panel shows all the column headers from your data as a list of field names. Below that are four drop zones: Rows, Columns, Values, and Filters.
Drag a field name into the Rows area to make that category appear as row labels down the left side. For example, if you drag "Region" into Rows, each region becomes a separate row. Drag a field into Columns to make categories appear as column headers across the top — dragging "Month" into Columns creates a column for each month. Drag a field into Values to show numbers that get summed, counted, or averaged. Drag a field into Filters to add a dropdown at the top that lets you show or hide data by that category.
A simple pivot table might have "Region" in Rows, "Product" in Columns, and "Sales" in Values. This shows total sales for each product in each region, organized in a grid.
Changing how values are calculated
By default, Excel sums the numbers in the Values area. If your data contains text or if you need a different calculation, you can change this. Double-click the field name in the Values area (or right-click it and select Value Field Settings). A dialog opens showing options like Sum, Count, Average, Min, Max, and others.
Choose the calculation you need and click OK. For example, if you drag "Order ID" into Values, Excel will count how many orders exist instead of trying to sum ID numbers. If you drag "Price" into Values but want the average price instead of the total, change the calculation from Sum to Average.
Refreshing the pivot table when your data changes
When you add new rows to your original data, the pivot table does not update automatically. To refresh it, right-click anywhere inside the pivot table and select Refresh, or go to the Analyze tab (or Design tab in older versions) and click Refresh. You can also press Ctrl+Alt+F5.
If you add a new column to your source data, the pivot table will not see it until you rebuild the table. To add the new column, go to Analyze > Change Data Source, select your updated data range, and click OK. Then you can drag the new field into the pivot table layout.
Sorting and filtering pivot table results
Click any cell in the pivot table, then go to the Analyze tab and use Sort to arrange rows or columns in ascending or descending order. You can also click the dropdown arrow next to a row or column label to sort or filter that category — for example, showing only the top three regions or hiding a specific product.
If you added a field to the Filters area, a dropdown appears at the top of the pivot table. Click it to show data for all categories, a single category, or multiple selected categories. This is useful for focusing on one region or time period without rebuilding the whole table.
Common mistakes and how to avoid them
The most common mistake is forgetting headers in the first row. Excel needs column names to recognize your fields. If your data does not have headers, add them before creating the pivot table.
Another mistake is dragging the same field into multiple areas. For example, dragging "Sales" into both Values and Rows will create confusing output. Use each field once, in the area that makes sense for what you want to see.
If your pivot table looks blank or shows unexpected results, check that you have at least one field in Rows, one in Columns, and one in Values. A pivot table with no fields in Values will not display any numbers.
Frequently Asked Questions
Can I create a pivot table from data on multiple sheets?
No. A pivot table reads from a single continuous range on one sheet. If your data is split across sheets, copy it into one sheet first, then create the pivot table. Alternatively, use a consolidation range or link the sheets with formulas before building the table.
What if I want to show the count of items instead of the sum?
Drag the field into Values, then double-click it and change the calculation from Sum to Count. This works for any field — counting how many times a product appears, how many orders per region, and so on.
Can I edit the numbers inside a pivot table?
No. Pivot tables are read-only summaries. To change the underlying data, edit the original sheet, then refresh the pivot table. The pivot table will recalculate automatically.
How do I remove a field from the pivot table?
Drag the field name out of its area in the Pivot Table Fields panel, or right-click it and select Remove Field. The pivot table updates instantly.
Can I copy a pivot table and paste it as regular data?
Yes. Select the entire pivot table, copy it, then right-click and choose Paste Special > Values. This converts it to a static table that you can edit like any other data, but it will no longer update when the source changes.