How to Make a Pivot Table in Excel: 4 Easy Ways in 2026
A pivot table summarizes a large table of data into totals, counts, or averages grouped by the categories you choose. To make a pivot table in Excel, select your data, go to Insert, then PivotTable, and choose where to place it. Then add fields to Rows, Columns, and Values in the PivotTable Fields pane.
This guide covers what a pivot table is, when to use one, and how to set up your data. It then walks through four ways to make a pivot table in Excel: manually, with a Recommended PivotTable, from multiple tables, and with AI. The last sections cover summarizing values, refreshing, and common mistakes.
What a pivot table is
Microsoft's support page calls a PivotTable "a powerful tool to calculate, summarize, and analyze data." It says the tool "lets you see comparisons, patterns, and trends in your data." Excel calls the feature PivotTable, as one word, while most people search for "pivot table."
It does not change your data. Microsoft notes that "PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way."
Every one has four areas:
- Rows. The categories listed down the left side, such as product or region.
- Columns. The categories listed across the top, such as month or quarter.
- Values. The numbers being summarized, such as sales or units.
- Filters. Optional fields that limit what the whole table shows.
The Field List has two parts. Microsoft describes "a field section in which you pick the fields you want to show." An Areas section at the bottom lets you arrange them. If you cannot see it, click anywhere in the PivotTable, then select Analyze, then Field List.
Excel places fields in these areas for you at first. Microsoft says "non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values."
When to use a pivot table
Use one when you have many rows and want a summary by category. It answers questions like "What were total sales by region last quarter?" in a few clicks.
| Question | Good fit for a pivot table? |
|---|---|
| Total sales by region and month | Yes |
| Count of orders by customer type | Yes |
| Average deal size by sales rep | Yes |
| A single total for the whole sheet | No, use SUM |
| A trend shown as a picture | Use a pivot table, then a PivotChart or a line chart |
It also handles counts. Drag a text field such as Product into Values, and Excel counts the rows instead of adding them.
If you only need one or two totals, a formula may be quicker. Our guide on how to summarize Excel data without pivot tables covers SUMIFS and other options.
For Google Sheets, the steps differ. Our article on Google Sheets pivot tables covers the grouped fields Google added in 2026.
How to set up your data first
The summary is only as good as the table behind it. Microsoft's data format tips list the essentials:
- "Use clean, tabular data for best results."
- "Organize your data in columns, not rows."
- Use one header row. Microsoft says to "Avoid double rows of headers or merged cells."
- Format the data as an Excel table. Rows you add later are then included when you refresh.
- Keep one data type per column. Microsoft says "you shouldn't mix dates and text in the same column."
Here is an example table you can copy. The numbers are invented.
| Date | Region | Product | Units | Sales |
|---|---|---|---|---|
| 2026-07-03 | North | Basic | 12 | 1,440 |
| 2026-07-05 | South | Pro | 4 | 1,196 |
| 2026-07-09 | North | Pro | 6 | 1,794 |
| 2026-08-02 | West | Basic | 20 | 2,400 |
| 2026-08-15 | South | Basic | 9 | 1,080 |
| 2026-09-01 | West | Pro | 5 | 1,495 |
Remove blank rows and subtotal lines before you start. Microsoft says your data "should be organized in a tabular format, and not have any blank rows or columns."
Way 1: Make a pivot table manually
This is the standard way, and it works in every recent version of Excel for Windows. Microsoft's support page describes the steps:
- Select the cells you want to create a PivotTable from.
- Select Insert, then PivotTable.
- Choose where the PivotTable report should go: New Worksheet or Existing Worksheet.
- Select OK.
Excel creates an empty PivotTable and opens the PivotTable Fields pane. Microsoft says "To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane." To move a field, drag it to another area.
You can stack more than one field in an area. Microsoft says rows "may be nested inside rows that are higher in position," depending on the order of the fields. To remove a field, drag it out of its area or select Remove Field.
For the example table, drag Region to Rows, the Date field to Columns, and Sales to Values. Excel shows total sales by region and period. Swap Region for Product to see the same totals by product.
With Region in Rows and Sales in Values, the example produces three totals:
| Region | Sum of Sales |
|---|---|
| North | 3,234 |
| South | 2,276 |
| West | 3,895 |
| Grand Total | 9,404 |
West leads mainly because one Basic order of 20 units was the largest in the table. Every region has two orders, so the gap comes from order size, not order count. That kind of detail is why a summary should send you back to the rows. Add Product to Columns to see which product drove each region's total.
In Excel for the web, the steps are similar. Select a table or range, then Insert, then PivotTable, to open the Insert PivotTable pane. Choose New sheet or Existing sheet on the card for building your own.
It can also start from other sources. The Insert PivotTable menu lists external data sources, a workbook Data Model, and Power BI datasets.
Way 2: Use a Recommended PivotTable
If you are not sure which layout you want, let Excel suggest one. Microsoft says "a Recommended PivotTable is a good choice" when you are new to the feature. Excel "determines a meaningful layout by matching the data with the most suitable areas in the PivotTable."
On a Mac, the steps are:
- Select a cell in the source data or table range.
- Go to Insert, then Recommended PivotTable.
- Select the PivotTable that looks best to you, and press OK.
Excel builds it on a new sheet and opens the Fields list, so you can adjust it. In Excel for the web, the Insert PivotTable pane shows recommended layouts next to a card for building your own.
One limit applies on the web. Microsoft notes that "Recommended PivotTables are available only to Microsoft 365 subscribers."
Way 3: Make a pivot table from multiple tables
Sometimes the data sits in more than one table, such as orders in one and customers in another. Excel can combine them through the Data Model.
Microsoft describes three basic steps for multiple tables:
- Import related tables. Bring them in from a database such as SQL Server, or add tables from the workbook to the Data Model.
- Add fields to the PivotTable. The Field List shows each table, and you drag fields from any of them.
- Create relationships if needed. If Excel says a relationship is needed, select Create and match the tables on a common field.
If the workbook already has a Data Model, Microsoft lists a short path. Select Insert, then the arrow under PivotTable, then From External Data Source. Choose Connection, then Tables in Workbook Data Model.
Two limits are worth knowing. Microsoft notes "Data Models are not supported on Excel for Mac." Several database connections are also marked as not supported on Mac.
Way 4: Make a pivot table with AI
Excel itself now offers an AI route. Microsoft's Copilot page says "When you want to summarize a large data set, Copilot can build a PivotTable without the manual steps." Its sample prompts include "Create a PivotTable with the sum of sales, with month in rows and category in columns." Copilot needs the right license.
Another route works outside Excel, and it helps when data is spread across several files. Here is the method in three steps, using Powerdrill Bloom. It returns the grouped summary as a table and a chart, rather than an Excel PivotTable object.
Step 1: Upload your spreadsheet
Start with the raw export, not a cleaned copy. Powerdrill Bloom's Excel AI Assistant page says "Upload your Excel files and let Powerdrill Bloom automatically analyze data, uncover insights, and create visual reports." The Free plan lists "Upload Excel, CSV, PDF, and docs."
If the file has several sheets, name the sheet that holds the rows you want to summarize.
Step 2: Ask for the summary in natural language
Describe the pivot table you want in one sentence. For example: "Summarize total sales and units by region and month, with regions in rows and months in columns."
Then ask a follow-up about what the table shows. "Which region grew fastest from July to September?" turns the summary into an answer. The Vibe Data Analysis page says "Whether it's a bar chart, line graph, or pivot table, Vibe creates dynamic visuals on the fly."
Step 3: Check the totals and export them
Check two or three totals against the source before you share anything. The Powerdrill Bloom homepage says "Every number comes back with the page, the row and the figure behind it."
Then take the result where you need it. The Excel AI Assistant page says you can "Export insights as PowerPoint slides, infographics, or Notion pages in one click." For a chart of the trend, our guide on how to make a line graph covers the next step.
Summarize, show values as, and refresh
Once it exists, three settings change what it shows.
Summarize Values By. Microsoft says "By default, PivotTable fields placed in the Values area are displayed as a SUM." It adds a warning: "If Excel interprets your data as text, the data is displayed as a COUNT." You can switch to average, minimum, maximum, and other functions.
Show Values As. Instead of raw totals, you can show each value as a share. Microsoft gives the example of showing amounts "as a % of Grand Total instead of the sum of the values." To show both, drag the same field into Values twice.
Refresh. The table does not update on its own. Microsoft says "If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed." Right-click inside it and select Refresh.
Group. Long lists of dates are hard to read, so group them. Microsoft's grouping guide says you can group "date and time fields in the PivotTable into quarters and months." Right-click a date, select Group, and pick the periods under By. For numbers, enter an interval, such as bands of 100 units.
You can also group text items by hand. Hold Ctrl, select two or more items, then right-click and select Group. This works for combining regions into larger territories, for example.
Common pivot table mistakes
Watch for these mistakes before you share the result.
- Merged cells or double headers. They break the field list. Use one header row with unique labels.
- Numbers stored as text. Excel then counts them instead of summing them. Fix the column format first.
- Forgetting to refresh. New rows stay out of the summary until you refresh.
- Blank rows in the source. They can split your data into an incomplete range.
- Renaming fields too early. Microsoft advises not to rename fields "until you're finished setting up your PivotTable."
- Using a range instead of a table. An Excel table picks up new rows on refresh; a fixed range does not.
Want to skip the setup? Try Powerdrill Bloom with one sales export and ask for totals by region and month.
Frequently asked questions
How do I make a pivot table in Excel?
Select the cells in your data, then go to Insert and select PivotTable. Choose New Worksheet or Existing Worksheet and select OK. In the PivotTable Fields pane, check the fields you want, then drag them to Rows, Columns, Values, or Filters.
What is a pivot table used for?
A pivot table summarizes large amounts of data by category. It can total sales by region, count orders by customer type, or average deal size by rep. Microsoft describes it as a tool to "calculate, summarize, and analyze data" and spot patterns and trends.
How do I make a pivot table from multiple sheets?
Add each table to the workbook's Data Model, then create relationships between them on a common field. Insert a PivotTable from the Data Model, and the Field List shows fields from every table. Microsoft notes that Data Models are not supported on Excel for Mac.
Why is my pivot table not showing new data?
A pivot table works on a snapshot of the source, so it needs a refresh after new data is added. Right-click inside the pivot table and select Refresh. If the source is a fixed range rather than an Excel table, update the source range as well.
Can AI make a pivot table for me?
Yes. Copilot in Excel can build a PivotTable from a request such as "Create a PivotTable with the sum of sales," with the right license. Powerdrill Bloom returns the same kind of grouped summary as a table and chart from an uploaded Excel or CSV file.
Sources: Microsoft Support, Create a PivotTable to analyze worksheet data · Microsoft Support, Use multiple tables to create a PivotTable in Excel · Microsoft Support, Visualize your data with Copilot in Excel · Microsoft Support, Use the Field List to arrange fields in a PivotTable · Microsoft Support, Group or ungroup data in a PivotTable.