How to Use Copilot in Excel to Generate Sales Insights and Charts
Question details
The user wants to utilize Copilot in Excel to automatically analyze bicycle sales data and produce summaries, charts, and PivotTables.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing sales data to discover product performance, yearly trends, and category comparisons using AI prompts.
- Observed behavior
- The user needs step-by-step instructions on formatting data, triggering the right Copilot prompt, and inserting the generated insights into a worksheet.
Ensure your sales data is formatted as an official Excel Table and that you are actively signed in with an account that has a Microsoft 365 Copilot license.
Use the 'Show Data Insights' Prompt in Copilot
Convert your dataset into a table and use Copilot's built-in data insights prompt to automatically generate charts and PivotTables.
Copilot in Excel excels at identifying trends, category comparisons, and sales frequencies automatically, but it requires structured data to function correctly. By keeping your data in an Excel Table, Copilot can accurately read the fields and establish relationships for meaningful insights.
Select any cell within your sales data range and press 'Ctrl + T'. Check the box for 'My table has headers' and click 'OK'.
Navigate to the 'Home' tab on the Excel ribbon and click the 'Copilot' icon located on the far right.
In the Copilot chat pane, click on the suggested prompt 'Show Data Insights' or manually type it into the chat box, then press Enter.
Wait a few moments while Copilot analyzes your data. It will display a text summary along with preview cards for generated charts and PivotTables.
Click the 'Add all insights to grid' button. Copilot will automatically create a new worksheet and place all the generated charts and PivotTables there for your review.
Analyze Sales Data Seamlessly with WPS Spreadsheet
If you don't have a Copilot subscription or find Microsoft Excel too heavy, WPS Office is an excellent free alternative. WPS Spreadsheet offers comprehensive chart types, advanced PivotTables, and robust data analysis tools with a familiar interface.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx sales data file without losing any formatting.
- 3. Create Charts and PivotTables: Select your data, navigate to the 'Insert' tab, and choose 'Chart' or 'PivotTable' to begin analyzing your sales performance.
Frequently Asked Questions
Why is the Copilot button missing in my Excel ribbon?
The Copilot button may not appear if you lack a Copilot for Microsoft 365 license, if your file is not saved to OneDrive or SharePoint (AutoSave must be enabled), or if you are not signed in with the correct organizational account.
Can Copilot analyze Excel data that is not formatted as a table?
No. Currently, Copilot in Excel strictly requires your data to be formatted as an Excel Table (using Ctrl+T or Insert > Table). It cannot analyze standard cell ranges.
How do I edit the charts after Copilot generates them?
Once you click 'Add all insights to grid', the charts and PivotTables are inserted as standard Excel objects. You can select them and use the 'Chart Design' or 'PivotTable Analyze' tabs on the ribbon to customize them just like manually created objects.
