Gen AI Tips: How to Use Microsoft Copilot With Microsoft Excel
How to Supercharge Excel Data Analysis with Microsoft Copilot and Generative AI
Microsoft Excel has always been a powerful tool for data analysis — but with Microsoft Copilot and Generative AI, you can now automate deeper insights, streamline repetitive tasks, and uncover patterns that would have taken hours to find manually. Best of all, you can do it without leaving your workbook.
Whether your Excel file contains formulas, Power Query logic, VBA, or just raw data, Copilot can analyze named ranges, generate code, summarize trends, and even integrate external information such as demographics or media sentiment.
Below are five practical, real‑world examples of how Copilot can transform your Excel‑based analytics workflows.
1. FSA‑Level Analysis with Excel and Generative AI
Suppose you’ve analyzed the average dollar value of uncollected parking tickets across Toronto FSAs over the past year. Using the Statistical Outlier Dashboard from the Interactive Statistics Education Add‑In, you identify five FSAs with unusually high uncollected revenue.
One of these might be M4A.
Instead of switching tools, simply click the Microsoft Copilot icon in Excel and ask:
“Provide a demographic analysis of FSA M4A.”
Copilot can instantly generate a detailed profile including:
Age and gender distribution
Ethnic and cultural diversity
Language and religion
Housing and income
Education and employment
These insights may help explain why your Excel analysis flagged M4A as an outlier in parking ticket collections — giving you a richer, more contextual understanding of the issue.
2. Call Centre Interaction Analysis with Excel and Generative AI
If you’re analyzing customer interactions with your call centre, your dataset may include a field such as CALL REASON. Using Copilot, you can perform a sentiment analysis that classifies each interaction as:
Positive
Negative
Neutral
After summarizing interactions by CALL REASON and SENTIMENT, you can run the Statistical Outlier Dashboard again to identify which call reasons generate statistically high levels of negative sentiment.
This workflow helps you pinpoint:
Service issues
Communication gaps
Operational bottlenecks
Policy‑related frustrations
For reference, the 311 Toronto Sentiment Analysis template provides a helpful example of this type of analysis.
3. Media Monitoring and Sentiment Tracking with Excel and Generative AI
Organizations often need to compare public sentiment with customer sentiment. Copilot makes this easy.
If your Excel file contains:
FSA
CALL REASON
SENTIMENT
Flags for unusually negative interactions
…you can ask Copilot to generate a media monitoring report for a specific timeframe:
“Search social and mass media for news affecting my organization over the past month.”
Copilot can then:
Summarize positive, negative, and neutral media stories
Perform sentiment analysis on the coverage
Align media sentiment with your call centre sentiment
Highlight mismatches or reinforcing patterns
This lets you compare:
% of negative media stories vs.
% of negative customer interactions
…and investigate whether certain CALL REASON categories correspond to topics trending negatively in the news.
4. Recoding Variables Automatically with Excel and Generative AI
Large datasets often require recoding — a tedious task even for experienced analysts.
Imagine a dataset with 1 million records and an FSA column containing 96 Toronto FSAs. You want to recode these into six REGION categories (the former Metropolitan Toronto municipalities).
Instead of writing formulas, VBA, or Office Scripts manually, simply ask Copilot:
“Generate Excel VBA code that reads FSA values from Column B and writes the corresponding REGION category to Column AJ.”
Copilot will produce a complete, ready‑to‑run macro in seconds — a task that would normally take an hour to write and debug.
For context, the 2010–2024 Time Series Segmentation Analysis template shows what this type of dataset looks like before recoding.
5. Financial Statement Analysis with Excel and Generative AI
Copilot can also automate financial ratio analysis.
Suppose your worksheet contains:
Sales Revenue ($000s)
Total Expenditures ($000s)
Ten years of data in B1:K3
You can ask Copilot to:
Calculate the Revenue/Cost ratio for each year
Insert the results into row 5
Label the ratio in cell A5
Calculate year‑over‑year revenue growth
Insert growth rates into row 6
Label the metric in cell A6
Then, you can request:
“Analyze the trend in the Revenue/Cost ratio.”
Copilot will generate a narrative trend analysis, highlight anomalies, and even shade ratios above or below benchmarks.
If you ever receive a financial statement without formulas, Copilot can reconstruct the underlying Excel formulas so the workbook becomes usable for scenario analysis — and reveal inconsistencies where formulas cannot be inferred.
Final Thoughts
Microsoft Copilot turns Excel into a far more powerful analytics environment. Instead of switching tools, writing code, or manually building formulas, you can:
Automate repetitive tasks
Generate insights instantly
Integrate external data
Perform advanced statistical analysis
Build smarter, more scalable workflows
