Copilot in Excel: A Full Review With Real Examples

What Is Copilot in Excel?

Copilot in Excel is Microsoft’s generative AI assistant built directly into Excel. It helps users speed up the process of analyzing and cleaning data through natural language commands. You can ask it to summarize trends, help you build PivotTables, generate charts, write formulas, or even provide insights directly from your dataset.

Copilot understands the context of your data. This is true if you are working with financial statements, survey results, or large CSV files, and it can suggest relevant insights or formulas based on what’s actually in your worksheet. For instance, if you upload a dataset of monthly sales, Copilot can identify seasonal patterns or calculate growth rates - whatever you need.

And since it works directly inside Excel’s interface, you don’t need to switch between tools or learn new software. Copilot in Excel is a natural extension of what Excel already does best.

How to Enable Copilot in Excel

Before you can start using Copilot, you need to make sure it’s available. First, check that you’re signed in with a Microsoft 365 Business Standard, Business Premium, or the Enterprise account that includes the Copilot add-on. Personal or standalone versions of Excel typically don’t include it yet.

You can verify your subscription by going to the top ribbon and clicking Excel > About Excel.

If Copilot is included, you’ll see something like: Microsoft 365 Subscription, Copilot for Microsoft 365. This means your Microsoft 365 plan includes the extra Copilot license, which is required for it to work.

If Copilot is not included, it will just say: Microsoft 365 Subscription (with no mention of Copilot). This means you have a Microsoft 365 plan, but Copilot is not part of it, either because your plan doesn’t support it, or the license hasn’t been added yet.

In short, Copilot requires both:

1. A supported Microsoft 365 plan (like Business Standard, E3, or E5)
2. An added Copilot license (it’s not included by default)

If you have issues, try moving through these steps:

1. Make sure you’re signed in with your Microsoft 365 account.
2. Next, confirm your Microsoft 365 subscription includes Copilot. 
3. If you still have issues, try updating Excel via File > Account > Update Options > Update Now. You can also sign out and sign back in to refresh your session. For whatever reason, this resolved an issue on my end.

Once Copilot is available, you’ll see a Copilot icon in the Excel ribbon or near the toolbar at the top of your screen under the Home tab. It's bright and hard to miss.

Testing Copilot in Excel

Let’s test Copilot in Excel along a few dimensions, including its ability to understand and query data and perform calculations and aggregations. To test it out, I’ll use a Kaggle dataset of mathematicians, which you can download for yourself if you want to practice.

Understanding and querying data

My first major test was to see how well Copilot could interpret and retrieve information directly from structured text data. So, using my mathematician's table, which includes columns such as Name, Birthplace, Century, and Contributions, I asked Copilot:

Which mathematician was born in Leuven, Belgium?

This was a simple question I could’ve answered myself, but I wanted to see how Copilot might handle the nuance. (Our dataset lists part of Belgium under an old name, the Spanish Netherlands, at least for this entry.)

For Copilot, the historical name didn’t present a problem: Copilot correctly identified Adriaan van Roomen as the mathematician born in Belgium. It also even summarized a key detail from the dataset: that he calculated π to 16 decimal places. 

This was a good start to my review: Copilot demonstrated one of its strong points: its contextual understanding of data labels and values. I decided to press harder.

Calculations and aggregations

Next, I tested Copilot’s ability to perform numerical operations and filtering. Using the same dataset, I requested this:

Count how many mathematicians were born in Belgium.

Now I felt like I was stacking the requests a bit. This time, I was asking Copilot to perform a calculation while keeping in mind that parts of what is now Belgium were labeled in the historical record as the Spanish Netherlands. 

Copilot generated the correct total and explained the logic behind its method, which was to apply a filtered count across the Birthplace column. In the interest of being thorough, I did take the extra trouble to recode and sort the dataset and count the rows (old habits die hard), and I can verify that Copilot’s answer was correct.

Writing and explaining formulas

Generating formulas might be Copilot’s most practical use case, especially since Excel syntax can be tricky and counterintuitive. For example, one would be forgiven for thinking that the AVERAGEIF() function has the same syntax as AVERAGEIFS(), but it doesn’t because the arguments are reversed. If Copilot really offered a promising shortcut and way to avoid mistakes in writing functions, I would be all ears.

To test the accuracy of formula writing, I asked a new question:

Can you write a formula to count the number of mathematicians who were born in China in the 18th or 19th centuries?

Copilot returned a usable Excel formula, which in this case was a COUNTIFS(), that could be directly pasted into a cell. The formula gave the correct answer (five). It correctly interpreted the two conditions (century and birthplace) and referenced the proper ranges. Copilot successfully turned my instructions into valid, executable Excel syntax.

=COUNTIFS(G2:G3169, "China", E2:E3169, ">=1700", E2:E3169, "<=1899")

I tried quite a few more formula prompts, including things that involved logical filters using functions like AND() combined with SEARCH(), as one example, and XLOOKUP() combined with IFERROR() as another. I also tried asking Copilot to put LEFT(), FIND(), and MID() together to reformat data, as we did in our Excel Substring() article. In every case, Copilot did a good job.

These examples taken together really showed me that Copilot isn’t limited to surface-level automation, and that it really is able to bridge the gap between those kinds of Excel users who understand what they want to calculate with those other users who already know how to write formulas in Excel.

Data cleaning and organization

For my next test, I wanted to try Copilot in Excel to help me find formulas that I would use to clean up and change the actual dataset. At issue were inconsistencies in the place names. 

For example, I noticed that Bertrand Russell was listed as having been born in Wales in a village called Trellech. But this was in the 19th century, after Wales had been considered a part of the UK. Other mathematicians from the same time were listed as having been born in the UK more generally. It was as if the dataset interpreted “born in the UK” to mean “born in the UK except for Wales.” This was confusing.

I tested to see if Copilot could help put my data on the same level:

Give me the formula where I can create a new column next to born_country called standardized_country. I want to standardize the place names in born_country so they’re all at the same level — for example, I will convert regions or parts of countries (like ‘Wales’) to the broader country name (‘United Kingdom’) for consistency.

Copilot gave me back a working formula using SWITCH(). I got a good result, and I was impressed. 

=SWITCH(A2,"Wales","United Kingdom","Scotland","United Kingdom","England","United Kingdom","Northern Ireland","United Kingdom",A2)

Insights and summaries

For my final test, I asked Copilot to generate insights from the data that could inform new forms of analysis. Copilot gave me some very cool ideas about what I could do:

It told me about female representation over time: 

A time-series analysis could show an increase in female representation beginning in the 20th century, coinciding with broader social reforms and higher education access.

It gave me some thoughts on migration patterns:

Many mathematicians were born in one country and died in another, offering a view of intellectual migration — for example, European scholars moving to the U.S. in the 20th century.

It gave me ideas on mathematical influences through advisor networks or specific schools of thought:

Map advisor–student networks to visualize academic lineages. Analyze the expansion of mathematical schools of thought (e.g., topology, algebra, logic) using the classification indices.

Limitations and Observations

After working through the dataset and pushing Copilot with more requests, I came to believe that Copilot’s limitations were most visible in edge cases and also in longer workflows:

• Complex logic: Multi-layered formulas that mix dynamic ranges or arrays occasionally fail or reference the wrong cells. Sometimes, I asked for a formula and got back a placeholder variable instead of the actual range that I needed.
• Prompt ambiguity: Accuracy depends on clarity. Ambiguous phrases like “recent mathematicians” or “famous discoveries” lead to mixed results because Copilot naturally can’t infer implicit definitions. Copilot needs specific, structured references. 
• Performance variability: The math dataset was not huge, but it wasn’t tiny, either. The response times varied. Once or twice, it felt like I got a timeout.
• Language inconsistencies: This is not something I experienced, but I did read about it as a possible issue: Locale-specific formula syntax (like commas versus semicolons) could cause confusion.

Best Practices

I tried different versions of the above prompts, fiddling with the level of detail and other issues, so I can verify that these ideas do make a difference. 

• Be explicit: Define the columns or fields you want analyzed.
• Iterate logically: Ask follow-ups that refine rather than restart the query.
• Keep data clean: Clear headers and consistent formatting improve accuracy significantly.
• Review formulas: Copilot can make mistakes, so verify the output. You are using Copilot to learn and get better at using Excel and improve your analysis. Don’t just treat it as a crutch.

And remember, Copilot is not a replacement for spreadsheet literacy. Data analysts still need to validate outputs. In my case, I think Copilot handled my questions with remarkable competence, but it's always worth double-checking your numbers.

Conclusion

If you’re an Excel user and you’re worried about the AI revolution, fear not. You won’t get left behind. Excel isn’t going away. It’s only getting better, and it's more worth learning than ever.

I think it really is a real step forward in how AI integrates with the everyday work of analysts. I had consistently good results in my own review, but - as a word of caution - I have seen others struggle. Some of this might come down to better or worse prompting skills. You can take our Understanding Prompt Engineering course to keep improving your prompting. Also, try our Introduction to Microsoft Copilot course and our Data Analysis in Excel course to learn in-demand data skills and enhance your productivity.