A lot of people are excited about Copilot, but they don’t just want to see it summarize the same old generic, synthetic datasets again. They want to see what it can actually do for them.
If you work in insurance, this one’s for you. We’ll walk through how to use Copilot in Excel to extract key details from claims, automate text reviews, and uncover insights that would have taken hours to find manually. From formatting and calculated columns to advanced analysis with Python, here’s how to turn your everyday insurance data into clear, actionable intelligence, all without ever leaving Excel.
To follow along, download the exercise file below:
This dataset contains real-world insurance claims, and we’ll perform a practical, hands-on analysis using that data.
Formatting and calculated columns
The easiest way to get started with Copilot in Excel is often to work with your columns, especially if your data is in a table format (and it should be). Copilot understands structure, so the more organized your data is, the better it performs. A great starting point is reformatting your columns or creating calculated ones. Here are some example prompts.
Notice how I’m being very specific with the column names and phrasing things like a programmer would. That precision helps Copilot understand exactly what you want it to do.
“Convert the ‘policy_bind_date’ and ‘incident_date’ columns into standardized date formats.”
Create a new calculated column ‘policy_tenure_days’ showing the number of days between ‘policy_bind_date’ and ‘incident_date’ to measure how long customers held their policy before filing a claim.
“Categorize claims into ‘High’, ‘Medium’, or ‘Low’ severity based on ‘total_claim_amount’.”
For more help working with formulas and functions, check out my LinkedIn Learning course.
What we did not really cover here is using Copilot for one-off formulas and functions like you might in a financial model. To be fair, this is one area where Copilot struggles a bit. It works best when your data model is structured and clear, not when it has to scan a large, loosely connected workbook.
Financial models often rely on ad hoc formulas and cross-sheet links that only make sense in context, which makes them tricky for Copilot to interpret. In those cases, you might actually find it easier to use Agent Mode to build an updated workbook instead of trying to repair the existing one. Starting fresh gives the AI a cleaner structure to follow and often leads to faster, more reliable results.
Copilot can even help with conditional formatting. This is one of those places where the more specific you are, the more controlled and meaningful the output will be. In the examples below, I’m telling Copilot exactly which colors to use and what thresholds they map to.
“Apply conditional formatting to the ‘total_claim_amount’ column, highlighting amounts above $10,000 in red, amounts between $5,000 and $10,000 in yellow, and amounts below $5,000 in green.”
You could just ask Copilot to “add conditional formatting” to a column and let it take its best guess, but the result might not mean much. Sometimes that’s fine if you’re just brainstorming ideas or exploring trends. Just make sure you can always explain your reasoning and turn those visuals into something useful, not just sugar for your stakeholders.
Summary PivotTables and PivotCharts
Now we can actually get into analyzing the data. I find that PivotTables and charts are usually the best tools to use with Copilot for this stage. It is often a good idea to specifically ask for these artifacts in your prompt. Otherwise, Copilot might generate a random Python code block instead, which probably is not what you wanted (we’ll look at using Python in a more controlled manner later in this post).
If you are looking to explore or summarize a dataset, start by asking Copilot to create PivotTables and PivotCharts. Once they are built, Copilot can help make small adjustments to them, though it may not handle every detail. For more complex refinements, you can ask Copilot more generally for high-level guidance.
If you would like more structured help with PivotTables and Copilot, check out my LinkedIn Learning course on the topic:
“Summarize the total claims by ‘incident_severity’ using a PivotTable and visualize with a bar chart.”
“Visualize claims frequency by the hour of the day using a histogram.”
These summaries go far beyond simple reports. They help you see which claim types drive the most losses, when incidents occur most frequently, and where underwriting or claims operations could focus next. For leaders, that means faster reviews and more consistent insight across teams.
Advanced Analysis with Python
And now we get to my favorite part of Copilot in Excel, the advanced analysis mode.
Maybe you have been a little underwhelmed so far with what Copilot can do. If you are an experienced Excel power user, you can already handle formulas, charts, and PivotTables in your sleep. Fair enough. But once you add Python to the mix, the possibilities expand fast.
Advanced analysis with Python lets you do things that would otherwise be very difficult or impossible to achieve with standard Excel tools. You can run correlations, model claim probabilities, and visualize risk distributions, all with plain-language prompts. You don’t need to be a Python or statistics expert, just analytical enough to know whether the results make sense.
To get started, use the phrase “Advanced analysis with Python” in Copilot. This opens a new message asking if you want to continue in that mode.
“Create box plots comparing claim amounts across different ‘incident_type’ categories.“
“Run a basic correlation analysis between ‘insured_age’ and ‘total_claim_amount’ to understand potential relationships, and visualize with a scatter plot.”
From there, we can ask Python to handle everything from visualizations to exploratory data analysis to full-on predictive modeling. It really is a powerhouse tool, and the best part is that we are doing all of this just through our prompts.
“Perform a logistic regression analysis predicting the likelihood of insurance claim fraud based on policy characteristics and incident details. Generate a ROC curve and confusion matrix to evaluate model performance.”
For insurers, this capability means identifying outliers faster, testing hypotheses on the fly, and validating models directly in Excel. It transforms spreadsheets from static reports into dynamic analytical workbenches.
Conclusion
Generative AI is changing how insurers use Excel. With Copilot, analysts can automate claim reviews, speed up audits, and uncover insights that support underwriting and risk management. It makes data analysis faster, clearer, and more accessible to teams who already know Excel well.
Copilot still depends on clean, well-structured data and human judgment. It can handle formulas, formatting, and even predictive modeling, but it needs clear direction and critical thinking to ensure accurate results. The best outcomes come when analysts combine their business knowledge with Copilot’s automation and analysis features.
As a next step, insurance teams should start by testing Copilot on a few key workflows such as claim reviews or loss ratio reports, learning where it saves the most time and produces the clearest insights. From there, the benefits can scale across departments.
If you want to make this technology work for your insurance company, book a free discovery call: