To create a PivotTable in Excel, select a cell in clean, column-based data, choose Insert > PivotTable, select where to put the report, and click OK. Then arrange fields in the Rows, Columns, Values, and Filters areas to decide how Excel groups and summarizes the data.
Prepare your data before creating a PivotTable
A PivotTable works best when its source is a straightforward table: each row is a record and each column contains one kind of information. Microsoft Support recommends a single header row with unique, nonblank labels. Avoid merged cells, multiple header rows, and blank rows or columns within the source data; keep the values in each column consistent with its heading.
As an Amazon Associate I earn from qualifying purchases.
Converting the source range to an Excel table is useful when records will be added or updated. After you refresh the PivotTable, it can include the current rows in that table. For a complicated or nested source that needs reshaping, Microsoft recommends using Power Query to turn it into a tabular layout. See Microsoft’s PivotTable creation and source-data guidance and its overview of Excel tables.
Create a PivotTable from one worksheet table
-
Select a cell in the source data, or select the range you want to analyze. Check that the selection has one row of column headers.
-
On the Excel ribbon, choose Insert > PivotTable. Excel opens a creation dialog or pane and identifies the source range or table.
-
Choose New Worksheet to put the PivotTable on a new sheet, or Existing Worksheet and specify a destination cell to place it on a current sheet.
-
Click OK. Excel creates an empty PivotTable and displays the PivotTable Fields list.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Select field checkboxes or drag fields into the report areas. Excel may place numeric fields in Values, nonnumeric fields in Rows, and date or time fields in Columns by default; you can move them to fit the report you want.
Rank #3
The exact controls can differ across Windows, Mac, web, and iOS versions of Excel. In Excel for the web, the Insert PivotTable pane offers a destination and a choice between creating a custom PivotTable and selecting a recommended one. Microsoft documents recommended PivotTables in that web flow as available only to Microsoft 365 subscribers. Check the labels in your own version if they differ from these steps. Microsoft’s platform-specific PivotTable instructions cover the documented workflows.
Arrange fields to shape the report
The four areas control what the PivotTable displays and how it summarizes the source. A field’s placement determines whether it forms a group, becomes a category across the report, supplies a calculation, or filters the results.
- Rows: Groups records into row labels, such as product, employee, or region.
- Columns: Splits results into column labels, such as month or status.
- Values: Summarizes a field, commonly a numeric measure such as units or sales.
- Filters: Lets you filter the report by a field without making it a row or column label.
For example, to summarize sales by region and month, put Region in Rows, Month in Columns, and Sales in Values. Add a field such as Product to Filters if you want to view the report for selected products. This illustrates field placement; your available fields depend on the columns in your source.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteRefresh the PivotTable after source changes
A PivotTable does not automatically recalculate its report every time its source changes. Refresh it after editing or adding source records. If the source is an Excel table, rows added to or updated in that table are included when the PivotTable is refreshed. Microsoft explains the table-based behavior in its PivotTable refresh guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When to use a Data Model for multiple tables
For a PivotTable based on one well-formed table, the standard workflow is sufficient. Use Excel’s Data Model when a report needs fields from multiple related tables—for example, a sales table and a separate products table—rather than treating each source as an unrelated flat range.
| Approach | Source and relationships | What you need | Platform consideration |
|---|---|---|---|
| One-table PivotTable | One range or Excel table; no relationship between tables is needed. | Select the source, create the PivotTable, and arrange fields. Power Query is unnecessary for a clean, tabular source. | Microsoft documents PivotTables across Windows, web, Mac, and iOS, though interface details vary. |
| Data Model PivotTable | Multiple tables whose matching identifiers connect their records. | Import or add the tables, create or confirm relationships, then use fields from the related tables. Power Query can import data from sources including text files, Excel workbooks, websites, Access, and SQL Server. | Microsoft’s multiple-table support article says Data Models are not supported on Excel for Mac; verify support for your current release and workflow. |
Relationships depend on matching identifiers, such as a unique key. Microsoft provides instructions for creating relationships between tables and for building a PivotTable from multiple tables. The Data Model route involves additional setup, so use it only when the analysis actually needs linked tables.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
