Quick Summary: Microsoft 365 Copilot in Excel brings the power of AI right into your spreadsheets. From cleaning data to creating charts and writing formulas, Copilot helps you analyze and visualize information faster—no advanced Excel skills required.
Have you ever wished Excel could do the heavy lifting for you—cleaning data, creating formulas, and uncovering insights in seconds? With Microsoft 365 Copilot in Excel, that’s now possible. Powered by AI and integrated directly into Microsoft 365, Copilot turns your natural-language prompts into powerful, time-saving actions.
Let’s look at what you can do with Copilot in Excel and how it can help you work smarter every day. For specific examples of working with Microsoft 365 Copilot in Excel, watch my video:
1. Getting Started with Copilot in Excel
To use Copilot, your Excel files must be saved to OneDrive or SharePoint, since that’s where Microsoft 365’s AI tools securely process and analyze your data. With a paid licensed version of Copilot, you’ll see the Copilot icon on the right side of the Home tab of the Excel ribbon.
Once you launch Copilot, you’ll see suggested prompts like:
- “Summarize this data.”
- “Create a chart showing sales by product category.”
- “Explain what’s driving the biggest changes this quarter.”
These examples show how you can describe what you want rather than knowing the exact Excel feature to use.
2. Analyze and Explore Data Instantly
One of Copilot’s most powerful skills is transforming raw data into meaningful insights. With a simple prompt such as “Show key trends in this table” or “Highlight outliers in sales data,” Copilot can:
- Generate data summaries and visualizations
- Suggest pivot tables and charts
- Highlight patterns, correlations, and anomalies
Copilot can even suggest questions you might ask about your data, which is a great way to explore new datasets or uncover insights you might have missed.
3. Automate Formatting and Cleanup
Instead of manually applying formatting rules, you can ask Copilot to do it for you. For example: “Highlight any row where sales exceed $75,000.”
Copilot creates the conditional formatting for you, even if you don’t remember the feature name or the steps. It’s a great way to save time and reduce errors when setting up consistent visual standards for your data.
4. Generate Charts and Visual Summaries
Need a quick visualization? Simply type something like:
“Create a chart showing total sales by region.”
Copilot can automatically build the related pivot table and chart, saving you the steps of manually selecting data ranges and chart types. It can also generate multiple chart options, letting you choose the one that best tells your story.
5. Write and Explain Formulas
Even complex formulas are easier with Copilot. Just describe what you want in plain language, and Copilot will:
- Generate the right formula (using functions such as VLOOKUP, XLOOKUP, or INDEX MATCH)
- Insert it directly into your sheet
- Provide a plain-language explanation of how the formula works
For example: “Add a column that shows each employee’s division manager based on the division name.”
Copilot can reference other worksheets, find the right lookup functions, and even explain the logic step by step, essentially turning Excel into a built-in formula coach.
6. Discover Insights with One Click
The “Show Data Insights” feature gives you instant AI-generated summaries and visuals. With a single click, Copilot can add multiple charts and pivot tables to your workbook, highlighting key findings such as top performers, category comparisons, and unexpected trends.
You can then refine what’s most useful and remove anything you don’t need, all in a fraction of the time it would normally take.
It’s also helpful to know that your data remains secure within your Microsoft 365 environment. Copilot doesn’t share or store your information outside your organization’s trusted cloud.
7. Get Inspired with the Prompt Gallery
And, if you’re not sure what to ask, the Prompt Gallery offers ready-made ideas organized by task, topic, or industry. Use these as inspiration to explore data analysis, reporting, or forecasting with natural language prompts.
For additional help and prompt inspiration, grab my free Copilot handouts at TheSoftwarePro.com/Copilot.
Final Thoughts
Microsoft 365 Copilot in Excel is transforming how we work with data. Whether you’re cleaning data, exploring patterns, or building formulas, Copilot helps you move from data to decisions faster than ever before.
Want to learn more ways to save time and get better results in Excel?
📘 Explore my other Excel Tips and Techniques at TheSoftwarePro.com/Excel
💌 Or join my Free Tips Newsletter for monthly software tips.
© Dawn Bjork, MCT, MOSE, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
2 Comments. Leave new
Great tips, as always. Super-annoying (not you, Microsoft) is Microsoft’s insistence on use of OneDrive. There’s no justification for that, other than their push to sell you more storage. With a halfway decent speed Internet connection, CoPilot should be able to access files on my PC or files located anywhere else (e.g., Google Drive), all with appropriate permission, of course.
I shouldn’t have to relocate my files to use CoPilot.
I understand your frustration with the “feature” of Copilot, which only allows access to cloud-based files in OneDrive or SharePoint. The default storage allocation per user is 1 TB for most Microsoft 365 Business and Enterprise plans, which typically covers most users’ needs.