A PivotTable turns a long spreadsheet into a clean summary you can sort, filter, and compare. If Excel is staring back at you with rows of raw data, start with the normal Insert path, then use the other options only when your version or data source calls for them.
Here are the current Microsoft-supported ways to create one in Excel, ordered from the fastest everyday fix to the more specialized routes.
1. Start with the standard desktop PivotTable
Use this when your data is already in an Excel table or a regular range of cells.
- 1.Select the cells you want to analyze.
- 2.Open Insert.
- 3.Select PivotTable.
- 4.Choose New Worksheet for a fresh sheet, or Existing Worksheet to place it in a specific spot.
- 5.Select OK.
- 6.In PivotTable Fields, check field names or drag fields into Rows, Columns, Values, and Filters.
This path works in Excel for Microsoft 365 for Windows and Mac, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.
2. Let Excel suggest the layout
If you want a quick summary and do not want to build the layout yourself, use Recommended PivotTable.
Select a cell in your source data or table range, open Insert, choose Recommended PivotTable, then pick the layout that matches the summary you want. Older builds show a dialog where you select OK. The redesigned pane instead offers +New sheet or +Existing sheet.
In Excel for the web, recommended PivotTables require a Microsoft 365 subscription.
3. Build it in Excel for the web
- 1.Open the spreadsheet in Excel for the web.
- 2.Select a table or range of data.
- 3.Open Insert.
- 4.Select PivotTable.
- 5.In the Insert PivotTable pane, choose Create your own PivotTable or select a recommended PivotTable.
- 6.Choose New sheet or Existing sheet.
That gives you the same basic PivotTable workflow without switching to the desktop app.
4. Create one on iPad
Excel on iPad supports PivotTable creation in version 2.82.205.0 and above.
- 1.Select a cell in the source data or table range.
- 2.Tap Insert.
- 3.Tap PivotTable.
- 4.Choose Insert on new sheet, or use the Destination field to place it somewhere specific.
- 5.Tap Insert.
Microsoft’s current PivotTable creation steps cover iPad, not iPhone or Android.
5. Pull from external data
Use this when the data is not sitting directly in the worksheet.
For an existing connection, click any worksheet cell, open Insert, choose PivotTable, select From External Data Source, choose Choose Connection, pick the connection, select Open, choose New Worksheet or Existing Worksheet, then select OK.
For a new external source such as a SQL Server, Analysis Services, or Access database, use Data > Get Data (Power Query), the modern default import path in current Excel: pull the data in, then build the PivotTable from it. The older From Other Sources, From Access, and Data Connection Wizard routes are now legacy wizards. To use them, first turn them on under File > Options > Data, then reach them from Data > Get Data > Legacy Wizards.
6. Use the Data Model for multiple tables
When your PivotTable needs multiple related tables, create it from the workbook Data Model in supported Windows versions.
- 1.Click any cell.
- 2.Open Insert.
- 3.Select the down arrow under PivotTable.
- 4.Choose From External Data Source.
- 5.Select Choose Connection.
- 6.Open the Tables tab.
- 7.Select This Workbook Data Model.
- 8.Choose Tables in Workbook Data Model.
- 9.Select Open, then OK.
You can also add a range to the Data Model while creating the PivotTable by selecting a cell in the data, opening Insert > PivotTable, checking Add this data to the Data Model, choosing the destination, and selecting OK. Microsoft notes that Data Models are not supported on Excel for Mac.
7. Ask Copilot to make one
Copilot can create a PivotTable from a prompt when your Excel setup supports it.
Select the Copilot icon in the lower-right corner of Excel, then enter a request such as Create a PivotTable, Make a PivotTable from this data and show a summary by report date, or Create a PivotTable with the sum of sales, with month in rows and category in columns.
Review the result before using it. Copilot in Excel requires an eligible Copilot-capable subscription — a Microsoft 365 Personal or Family subscription with an AI credits plan, a Microsoft 365 Premium subscription, a commercial Microsoft 365 Copilot subscription, or a Copilot Chat-eligible Microsoft 365 or Office 365 business or enterprise subscription — plus a supported Microsoft 365 Apps deployment, account and network setup, and privacy settings that allow the feature.
8. Fix missing PivotTable options
If Recommended PivotTable is missing on a personal Microsoft account, turn on the connected experience that analyzes content.
Go to File > Account > Account Privacy > Manage Settings. Under Connected experiences, allow experiences that analyze your content.
On a work or school account, your organization controls availability. For Copilot, admins need the right Microsoft 365 Apps deployment, eligible licenses, required privacy controls, network endpoints, and the Office Feature Updates task available.
Skip older instructions that tell you to use the PivotTable and PivotChart Wizard as your main fix. Microsoft labels that multiple-consolidation-range workflow as legacy, and newer versions commonly use Power Query before creating the PivotTable.
Frequently Asked Questions
What is the fastest way to create a PivotTable in Excel?
Select the data, go to Insert > PivotTable, choose New Worksheet or Existing Worksheet, select OK, then add fields in PivotTable Fields.
Can I create a PivotTable from a Power BI dataset?
Yes. In Excel for Microsoft 365, select an empty cell, go to Insert, open the arrow under PivotTable, choose From Power BI, then select an available Power BI semantic model (formerly called a dataset). You need Excel for Windows or Excel for the web, a Power BI license, and permission (commonly Build permission) to the semantic model.
Can I use a keyboard shortcut to start a PivotTable?
On Windows desktop Excel, select the source data and press Alt+N to open the Insert tab. Then follow the on-screen KeyTips for PivotTable because the exact next keys vary by build and ribbon state.
How do I delete a PivotTable?
Select the entire PivotTable range and press Delete. If it is the only thing on its worksheet, delete that sheet instead.
