What a pivot table does and when you need one
A pivot table takes a large, flat list of data — like sales records, expense reports, or survey responses — and reorganizes it to show totals, counts, or averages grouped by the categories that matter to you. Instead of scrolling through hundreds of rows to find patterns, a pivot table does the grouping and math automatically.
You need a pivot table when your raw data has too many rows to scan by eye, or when you want to see the same information grouped different ways without rewriting formulas. For example: if you have a list of 500 transactions, a pivot table can show you total sales by month, or by product, or by salesperson — each in seconds, without creating new columns or helper sheets.
If your data is small (under 50 rows) or you only need one specific total, a formula like SUMIF is faster. If you need to share the summary with someone who will change the grouping themselves, a pivot table is the right choice because they can drag fields around without breaking anything.
Key Takeaways
- Your data must be organized in a single table with headers in the first row, with no blank rows or columns in the middle.
- Select all your data including headers, then go to Insert > Pivot Table and choose whether to place it on a new sheet or the same sheet.
- Drag field names from the right panel into Rows, Columns, Values, or Filters to control what the pivot table shows and how it groups the data.
- You can change the pivot table layout at any time by dragging fields between areas or removing them — the original data stays untouched.
- If your source data changes, right-click the pivot table and select Refresh to update all the totals and counts automatically.
Prepare your data before you start
A pivot table only works if your data is organized as a single continuous block with no gaps. The first row must contain headers — the field names that describe each column. Every row below that should contain actual data, with no blank rows inserted in the middle.
Check that each column contains only one type of information. If one column mixes dates and text, or if numbers are stored as text instead of numbers, the pivot table will treat them as separate groups and your totals will be wrong. You can spot this by looking at how Excel aligns the data: numbers sit right-aligned, text sits left-aligned. If a column that should contain numbers is left-aligned, select it, go to Data > Text to Columns, and click Finish — this converts text-formatted numbers into real numbers.
Remove any completely blank rows or columns from your data. If you have a summary row at the bottom of your data (like a grand total), delete it before creating the pivot table, because the pivot table will treat it as a data row and skew your results.
Select your data and insert the pivot table
Click any cell inside your data table. You do not need to select the entire range — Excel will find the boundaries automatically as long as your data is continuous. Go to the Insert tab at the top of the ribbon, then click Pivot Table.
A dialog box will appear asking where you want the pivot table to go. Choose New Worksheet if you want it on a separate sheet (this is usually cleaner). Choose Existing Worksheet if you want it on the same sheet as your data, then click the cell where you want the top-left corner to start. Click OK.
Excel will create a blank pivot table and open the PivotTable Fields panel on the right side of your screen. This panel lists all the column headers from your data. You now drag these fields into four zones: Rows, Columns, Values, and Filters. The layout you create determines what the pivot table shows.
Drag fields into Rows, Columns, and Values
The Rows area controls what appears down the left side of your pivot table — usually the categories you want to group by. If you have a column called "Product" and you want to see one row for each product, drag "Product" into the Rows area.
The Columns area controls what appears across the top. If you want to see data broken down by month, drag a date column into Columns. Excel will automatically group dates by month (or year, or day — you can change this later). If you do not use the Columns area, your pivot table will have just one column of numbers.
The Values area is where the actual numbers go. Drag a column that contains amounts you want to add up — like "Sales" or "Quantity" or "Hours". Excel assumes you want to sum these numbers, but you can change it to count, average, or find the maximum. To change how a field is calculated, double-click it in the Values area and choose a different function from the list.
The Filters area lets you hide rows without deleting them. If you drag "Region" into Filters, a dropdown will appear at the top of your pivot table so you can show only data from one region, or from multiple regions you pick. This is useful when you want to focus on a subset of your data without rebuilding the whole table.
Rearrange and refine your pivot table
Once your pivot table appears, you can change it by dragging fields between areas. If you put "Month" in Columns but decide you want it in Rows instead, drag it from Columns to Rows. The table updates when ready. You can also remove a field by dragging it out of any area, or by unchecking its name in the PivotTable Fields panel.
To change how dates are grouped, right-click any date in your pivot table, select Group, and choose the time period you want — by month, quarter, year, or day. To change the order of rows (for example, to sort products alphabetically instead of by sales amount), click any cell in that row, then use the sort buttons on the Data tab.
If you want to see only certain items — for example, only products that sold more than 100 units — click the dropdown arrow next to the field name in the Rows area, select Value Filters, and set your condition. The pivot table will hide rows that do not meet it.
Update your pivot table when the source data changes
When you add new rows to your original data, the pivot table does not automatically include them. To update it, right-click anywhere inside the pivot table and select Refresh. Excel will scan your data again and recalculate all totals and counts.
If you add a new column to your data, the pivot table will not see it until you rebuild it. Click any cell in the pivot table, go to Insert > Pivot Table, and choose to create a new one — or, in newer versions of Excel, you can click the PivotTable Fields panel and it will show the new column so you can drag it in.
If you delete rows from your source data, the next time you refresh the pivot table, those rows will disappear from the summary. The original data and the pivot table are linked, so changes in one affect the other — but the pivot table never changes your raw data, only how it displays it.
Common mistakes and how to fix them
If your pivot table shows each value as a separate row instead of grouping them, your source data probably has extra spaces or inconsistent spelling. For example, "New York" and "New York " (with a trailing space) will be treated as two different regions. Go back to your original data, find and fix the spelling, then refresh the pivot table.
If numbers are not adding up correctly, check that they are stored as numbers, not text. Select the column in your source data, go to Data > Text to Columns, and click Finish. Then refresh the pivot table.
If your pivot table is taking a very long time to load or refresh, it may be because your data is very large or because you have too many fields in the Values area. Remove fields you do not need, or split your data into smaller tables by using the Filters area to show only certain categories at a time.
Frequently Asked Questions
Can I edit the numbers inside a pivot table directly?
No. A pivot table is a summary, not a data entry tool. If you need to change a number, go back to the original data, edit it there, and refresh the pivot table. This keeps your source data accurate and prevents confusion about where the real information lives.
What if I want to see the same data grouped two different ways?
Create two separate pivot tables from the same source data. Click a cell in your data, go to Insert > Pivot Table, and build a new one with a different layout. Both tables will update when you refresh them, so you can compare the same information from different angles without rebuilding.
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 of numbers that you can edit like any other data. The link to your source data is broken, so it will not refresh, but you can now edit individual cells.
How do I remove a pivot table I no longer need?
Click any cell in the pivot table, right-click, and select Delete. If the pivot table is on its own sheet, you can right-click the sheet tab and delete the entire sheet. Your original data is never affected — only the summary is removed.
What is the difference between a pivot table and a formula like SUMIF?
A formula calculates one specific total based on one condition. A pivot table reorganizes all your data into multiple groups at once and lets you change the grouping without rewriting anything. Use a formula for a single answer; use a pivot table when you want to explore the data from multiple angles.