How to Create a Pivot Table in Excel

What a pivot table does and when you need one

A pivot table is a tool that reorganizes raw data into a summary you can actually read. If you have a spreadsheet with hundreds or thousands of rows — sales by date and region, expenses by department and category, survey responses by age group — a pivot table pulls out the totals, counts, or averages you're looking for without writing formulas.

You need a pivot table when your data has repeating values across multiple columns and you want to see patterns. For example, if you have a list of every transaction your business made last year, a pivot table can show you total sales by month, or by product, or by both at once. Without it, you'd manually add up numbers or build a dozen separate formulas.

Pivot tables work best when your data is organized in a single table with headers in the first row — one column for dates, one for regions, one for amounts, and so on. If your data is scattered across multiple sheets or has blank rows in the middle, you'll need to clean it up first.

Key Takeaways

A pivot table summarizes large datasets by grouping data into rows and columns, then calculating totals, counts, or averages for each group.
Your data must have headers in the first row and be organized in a single continuous table with no blank rows or columns in the middle.
You select your data, click Insert > Pivot Table, choose where to place it, then drag fields into the Rows, Columns, Values, and Filters areas.
After you create the pivot table, you can change what it shows by dragging fields between areas or by filtering specific values.
If your source data changes, you refresh the pivot table to update the summary without rebuilding it from scratch.

Preparing your data before you start

Before you create a pivot table, check that your data meets three requirements. First, the very first row must contain headers — column names like "Date", "Region", "Product", or "Amount". Pivot tables use these headers to label the rows and columns in your summary.

Second, your data must be one continuous block with no blank rows or columns in the middle. If you have data in rows 1 through 100, then a blank row 101, then more data in row 102, Excel will treat rows 102 onward as a separate table. Delete any blank rows or columns that interrupt the data.

Third, each column should contain only one type of information. A "Date" column should have dates, not "January 15" mixed with "Q1 2024". If your dates are formatted as text instead of actual dates, the pivot table may group them incorrectly. You can check this by clicking a cell in the date column — if the formula bar shows the date as text (usually left-aligned in the cell), convert it to a real date first.

Selecting your data and opening the pivot table dialog

Click any cell inside your data table. You do not need to select the entire table — Excel will find the boundaries automatically as long as the data is continuous. Then go to the Insert tab at the top of the ribbon and click Pivot Table. A dialog box will appear.

In the dialog, Excel shows you the range it detected. If it's wrong — for example, it included a blank row or stopped too early — you can type the correct range in the box. The range should include your headers and all your data, written as Sheet1!A1:D500 or whatever your actual sheet name and cell range are.

Below that, you'll see two options for where to place the pivot table: "New Worksheet" or "Existing Worksheet". New Worksheet is usually safer because it keeps your original data separate from the summary. If you choose Existing Worksheet, click the box next to it and then click a cell where you want the pivot table to start — leave plenty of empty space because the pivot table will expand as you add fields.

Building the pivot table by dragging fields

After you click OK, a blank pivot table appears on the left side of your screen, and a Pivot Table Fields panel opens on the right. The panel shows all the column headers from your data as a list of fields.

To build your pivot table, you drag fields from the list into four areas at the bottom of the panel: Rows, Columns, Values, and Filters. Here's what each area does:

Rows: The categories that appear down the left side of the pivot table. If you drag "Region" here, each region gets its own row.
Columns: The categories that appear across the top. If you drag "Month" here, each month gets its own column.
Values: The numbers that get calculated — totals, counts, or averages. If you drag "Sales Amount" here, the pivot table sums all sales for each row-and-column combination.
Filters: A dropdown at the top of the pivot table that lets you show only certain categories. If you drag "Year" here, you can filter to show only 2024 data.

Start by dragging one field to Rows — for example, "Product". The pivot table now shows each unique product as a row. Then drag another field to Values — for example, "Sales Amount". Excel automatically sums the sales for each product. If you want to see how sales break down by month, drag "Month" to Columns. Now you see products in rows, months in columns, and the sum of sales in each cell.

Changing how values are calculated

By default, Excel sums numbers in the Values area. If your data contains text or if you want a count instead of a sum, you need to change the calculation. Double-click the field in the Values area — for example, "Sum of Sales Amount" — and a dialog opens.

At the top, you'll see a dropdown showing "Sum". Click it to see other options: Count (how many rows match), Average, Minimum, Maximum, and several others. Choose what you need and click OK. The pivot table updates when ready.

You can also have multiple calculations for the same field. For example, you might want to see both the sum of sales and the count of transactions. Drag the same field to Values twice, then change one to Sum and one to Count. The pivot table will show both columns side by side.

Filtering and sorting your pivot table

Once your pivot table is built, you can change what it shows without rebuilding it. If you added a field to Filters, a dropdown appears at the top of the pivot table. Click it to show only certain categories — for example, only the "North" region or only 2024 data.

To sort the rows or columns, click the small dropdown arrow next to a row or column header in the pivot table itself. You'll see options to sort A to Z, Z to A, or by the values in that row or column. For example, you can sort products by sales amount from highest to lowest.

If you want to hide a specific row or column without removing it entirely, click the checkbox next to its name in the Filters area. Unchecking it hides that category; checking it again shows it.

Updating your pivot table when data changes

If you add new rows to your original data or change existing numbers, the pivot table does not update automatically. To refresh it, right-click anywhere inside the pivot table and select Refresh. The summary recalculates based on your current data.

If you add a new column to your source data, the pivot table won't see it until you rebuild it. Click any cell in the pivot table, go to Insert > Pivot Table, and in the dialog, update the data range to include the new column. Then click OK and rebuild the fields in the panel.

If your data grows significantly — for example, you started with 100 rows and now have 500 — it's worth checking that the pivot table's range still covers everything. Right-click the pivot table, select Pivot Table Properties, and verify the data range is correct.

Frequently Asked Questions

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

No, a single pivot table can only summarize data from one sheet. If your data is split across multiple sheets, copy it all into one sheet first, making sure the headers match. Then create the pivot table from that combined data.

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

This usually means Excel thinks your numbers are text, not actual numbers. Go back to your source data, click a cell in that column, and check the formula bar. If it's left-aligned in the cell, it's text. Select the column, go to Data > Text to Columns, click Next twice, and click Finish. This converts text numbers to real numbers. Then refresh your pivot table.

Can I move or resize a pivot table after I create it?

You can move it by cutting and pasting the entire pivot table to a new location. Right-click it, select Cut, click the cell where you want it, and paste. You cannot resize individual cells like a normal table, but you can hide or show rows and columns using the filter dropdowns.

How do I delete a pivot table?

Click any cell in the pivot table, right-click, and select Delete. Then choose whether to delete just the pivot table or the entire sheet it's on. This does not delete your original data — only the summary.

Can I copy a pivot table and paste it as regular data?

Yes. Select the entire pivot table, copy it, then right-click in a new location and select Paste Special > Values. This converts the pivot table to a static table of numbers that you can edit like any other data. The connection to your source data is lost, so it won't update when the original data changes.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.