How to Create and Use PivotTables with Copilot in Excel
Question details
The user wants to learn how to utilize AI assistance to generate and manipulate PivotTables for data summarization.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing and summarizing large datasets quickly using automated AI prompts and PivotTable layouts.
- Observed behavior
- The user seeks a clear method to prompt Copilot to build a PivotTable, arrange specific fields, and keep the data refreshed.
Ensure your dataset is organized in a proper tabular format with clear column headers and absolutely no blank rows or columns. You must also be signed in with a Microsoft 365 account that has an active Copilot license.
Create and Customize PivotTables Using Copilot
Use natural language prompts in the Copilot chat pane to automatically generate and structure your PivotTable.
Copilot can instantly analyze your selected dataset and build a PivotTable layout based on your text instructions, saving you the time of manually dragging and dropping fields.
Highlight your data range and verify that every column has a unique header. Convert the data into an Excel Table (Ctrl+T) for the best results with Copilot.
Click the 'Copilot' button located on the right side of the Home tab in the Excel ribbon.
In the Copilot pane, type a specific request such as 'Create a PivotTable to summarize sales by product and region' and press Enter.
Review the preview generated by Copilot in the chat pane. Click the 'Add to a new sheet' button to insert the PivotTable into your workbook.
Manually Create a PivotTable via the Insert Menu
If Copilot is unavailable or fails to interpret your prompt correctly, you can use the traditional method to build a PivotTable.
Create PivotTables Easily in WPS Spreadsheet
WPS Office provides robust and intuitive tools to create, manage, and analyze PivotTables without needing expensive AI subscriptions. Experience seamless data analysis with full compatibility for Microsoft Excel formats.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your formatted data.
- 2. Insert a PivotTable: Select your data range, navigate to the 'Insert' tab on the top menu, and click the 'PivotTable' icon.
- 3. Choose the destination: Select 'New Worksheet' or specify a cell in an existing worksheet, then click 'OK'.
- 4. Build your layout: Use the field list task pane on the right to drag and drop your data fields into the Filters, Columns, Rows, and Values boxes.
Frequently Asked Questions
Why is the Copilot button missing in Excel?
Copilot may not appear if you lack a qualifying Microsoft 365 subscription (like Copilot Pro or Copilot for Microsoft 365), if your file is not saved to OneDrive or SharePoint, or if AutoSave is turned off. Also, ensure your data is formatted as an Excel Table.
Can Copilot automatically refresh my PivotTable when data changes?
No, Copilot does not actively monitor and refresh PivotTables in real-time. You must manually right-click the PivotTable and choose 'Refresh', or go to the Data tab and click 'Refresh All' after updating the source data.
Why does Copilot fail to create a PivotTable from my data?
Copilot usually fails if the source dataset is messy. Check for and remove any blank columns, empty row spaces, merged cells, or missing headers. The data must be structured as a continuous table for Copilot to process it accurately.
Can I ask Copilot to change the summary calculation from Sum to Average?
Yes, you can prompt Copilot in the chat pane by saying 'Change the PivotTable summary from sum of sales to average of sales'. Alternatively, you can right-click the Values field in the PivotTable, select 'Value Field Settings', and manually choose 'Average'.
