Excel users spend a lot of time on the same handful of tasks: writing formulas, cleaning messy data, analysing tables row by row, building charts, and pulling together reports. Copilot in Excel can take on a meaningful chunk of that work through plain, natural-language instructions instead of manual formula-writing or clicking through menus.
Knowing how to use Copilot in Excel well comes down to a simple loop: start with a specific problem, ask Copilot in clear terms, review what it gives you, refine the request if needed, then apply the result. This guide walks through 10 ways to solve Excel problems using that loop, along with the prompts that actually get you there.
What Can Copilot Do in Excel?
Before getting into the ten use cases, here’s a quick overview of what Copilot can help with across a typical Excel workflow:
- Creating formulas
- Explaining formulas that already exist
- Analysing data
- Cleaning inconsistent or messy data
- Spotting trends and outliers
- Building charts
- Creating PivotTables
- Filtering and searching data
- Summarising large datasets
- Supporting reporting tasks
Each of these maps to a specific kind of Excel problem, which is really the point. Copilot works best when you already know what you’re trying to fix, and you’re asking it to help you get there faster.
10 Ways to Use Copilot in Excel to Solve Common Problems
1. Create an Excel Formula Without Writing It From Scratch
The problem: You know what calculation you need but not the exact formula syntax to get there. This is one of the most straightforward ways to use Copilot in Excel. You describe the calculation in plain language, and Copilot generates the formula, explains what it does, and can adjust it if the first version isn’t quite right.
Prompt: “Create a formula that calculates the percentage change between the current month’s sales and the previous month’s sales.”
Result: Copilot returns a working formula referencing the relevant columns, along with a short explanation of what each part does.
Tip: If the formula references the wrong columns or ranges, tell Copilot exactly which columns to use instead of starting the prompt over from scratch.
2. Find Out Why an Excel Formula Is Not Working
The problem: A formula is returning an error or a value that clearly isn’t right, and it’s not obvious why. Paste the formula and describe what it’s supposed to do. Copilot can check the logic, point out where it likely breaks, and suggest a corrected version.
Prompt: “This formula is supposed to calculate total revenue for the West region, but it’s returning zero. Here’s the formula: [paste formula]. What’s wrong with it?”
Result: Copilot identifies the likely issue, such as a mismatched range or an incorrect comparison operator, and explains the fix rather than just handing back a corrected formula with no context.
Tip: Always ask Copilot to explain what changed and why. Understanding the fix is what stops the same mistake from happening again in a different sheet. This kind of troubleshooting is where Excel formula troubleshooting tips matter most — knowing what to check first saves time on every future formula error.
3. Analyse Excel Data Without Manually Checking Every Row
The problem: A dataset has hundreds or thousands of rows, and manually scanning for anything meaningful isn’t realistic. Copilot can summarise a dataset, surface the values that matter most, and answer direct questions about what’s in the data, without you having to filter and scroll through it yourself.
Example prompt: “Summarise the key patterns in this sales data and tell me which region contributed the most to total revenue this quarter.”
Result: A summary highlighting the standout figures, along with a direct answer to the specific question asked.
Tip: Ask one specific question at a time rather than a broad “tell me everything about this data.” This kind of Excel data analysis without manual checking works best when questions are narrow and specific rather than open-ended.
4. Find Trends, Outliers and Unexpected Changes
The problem: Something in the data looks off, or you suspect a pattern exists but can’t spot it by eye. This is where Copilot in Excel becomes genuinely useful for spotting what a manual scan would likely miss, since it can scan an entire dataset for irregularities much faster than manual review.
Prompts: “Which product performed worst this quarter? Which month had the biggest decline in sales? Are there any values in this column that look unusual compared to the rest?”
Result: Copilot flags the specific rows, categories, or time periods that stand out, giving you a starting point for further investigation rather than a vague summary.
Tip: Once Copilot flags something unusual, ask it to explain why that value stands out statistically. That context helps you judge whether it’s worth investigating further or just normal variation. Being able to find trends and outliers in Excel data quickly is one of the more time-saving applications covered in this guide.
5. Clean Messy Excel Data
The problem: A dataset has duplicate records, inconsistent entries, missing values, or formatting that doesn’t match across rows. Copilot can identify these issues and either fix them directly or explain what needs correcting before further analysis makes sense.
Prompt: “Check this column for duplicate entries and inconsistent formatting, and tell me what needs to be fixed before I analyse it.”
Result: A list of specific problems, such as duplicate customer names spelled differently, or a date column mixing two different formats.
Tip: Clean the data before asking Copilot to analyse it further. Clean messy Excel data first, since analysis built on messy data produces misleading results no matter how good the analysis prompt is.
6. Create a Useful Chart From Your Data
The problem: You need to visualise data but aren’t sure which chart type actually fits the question you’re trying to answer. Copilot can suggest an appropriate chart type based on what you’re trying to show and generate it directly from the selected data.
Prompt: “Create a chart that compares monthly revenue across the last six months and highlights which month performed best.”
Result: A chart built from the relevant range, using a format suited to comparing values over time.
Tip: If the first chart type doesn’t communicate the point clearly, ask Copilot for an alternative rather than manually rebuilding it yourself. Getting Excel charts and visualisations right on the first attempt usually comes down to being specific about what the chart needs to show.
7. Create a PivotTable for Faster Analysis
The problem: You need a summarised view of a large table, grouped and compared in a way that would take a while to build manually.
Prompt: “Create a PivotTable summarising total sales by region and product category for this data.”
Result: A working PivotTable structured around the categories you specified, ready for further filtering or adjustment.
If you’re still building your foundation in formulas, tables, and PivotTables manually, that groundwork makes it much easier to understand what Copilot is actually doing and to verify its results rather than trusting them blindly. Being able to create a PivotTable for faster analysis with AI assistance still depends on understanding what a PivotTable is doing underneath. If you’re still building these fundamentals, the WSQ Microsoft Excel – Basic Level Course in Singapore covers formulas, tables, PivotTables, and other core Excel skills.
8. Find Specific Information Using Natural-Language Questions
The problem: You need a specific piece of information buried in a large dataset, and manually filtering for it would take several steps.
Prompts: “Show me the top five products by revenue. Which region had the lowest sales in Q2? Find customers whose sales increased by more than 20% compared to last year.”
Result: A direct answer or filtered view addressing exactly what was asked, without needing to build a manual filter or formula first.
Tip: Be as specific as the question actually requires. “Top five products” gets a cleaner answer than “show me the good products”.
9. Compare Business or Sales Performance
The problem: You need to compare performance across different periods, categories, or targets, and doing it manually means building several separate calculations.
Prompt: “Compare actual sales against target for each product category this quarter, and tell me which categories are furthest behind.”
Result: A structured comparison, month vs month, product vs product, or actual vs target, along with a direct answer about where the gaps are.
Being able to compare business or sales performance in Excel this way is especially useful for reports built around comparisons, since these usually need this exact structure repeated across several dimensions.
10. Turn Raw Excel Data Into Useful Insights
This is the strongest use case, because it moves beyond individual tasks and into the actual reason most people analyse data in the first place.
The general path looks like this:
Raw data → analysis → patterns → insights → report.
Instead of asking Copilot to perform one isolated task, ask it to walk through several questions in sequence.
Prompts: “What changed in this data compared to last quarter? Why might that change have happened? Which category needs attention based on this data? What should I look into next?”
Result: A chain of findings that moves from a surface-level observation toward something closer to an actual business insight, rather than a single isolated statistic.
Tip: Treat this as a conversation, not a single prompt. Learning to turn raw Excel data into useful insights this way means each answer should inform the next question you ask, the same way an analyst would work through a dataset step by step.
How to Write Better Copilot Prompts for Excel
How to write better Excel prompts comes down to a few consistent habits:
- Mention the data. Tell Copilot what data you’re working with, including what the columns represent, rather than assuming it will infer this correctly on its own.
- Clearly state the task. Instead of “analyse this,” try something specific like “identify the three products with the highest year-over-year sales growth.” The second version gives Copilot an actual target to work toward.
- Add conditions. For example, “only include products with more than 500 units sold” narrows the result to something directly relevant to your actual question.
- Specify the output. Ask for a table, a summary, a chart, a formula, or bullet points, depending on what you actually need to do with the result afterward.
- Refine the result. If the first answer isn’t quite right, a follow-up prompt, “narrow this to just the West region” or “show this as a table instead”, usually gets you there faster than rewriting the whole request.
How to Get More Accurate Results From Copilot in Excel
Getting more accurate results from AI in Excel comes down to a few habits that make a noticeable difference in result quality:
- Keep column names clear and descriptive,
- Keep column names clear and descriptive, since Copilot relies on them to understand what the data represents
- Use properly organised tables rather than scattered ranges with inconsistent structure
- Check that data types are correct (numbers stored as numbers, dates as dates) before asking for analysis
- Give specific instructions rather than vague, open-ended requests
- Avoid vague prompts that leave too much room for Copilot to guess at what you actually want
- Review generated formulas rather than applying them without checking the logic
- Verify important results independently, particularly for anything feeding into a business decision
Copilot doesn’t replace the need to understand Excel. It speeds up the mechanical part of the work, but the judgement about whether a result makes sense still depends on knowing what the data and the formulas are actually doing.
What Should You Check Before Using Copilot's Result?
Before applying anything Copilot generates, it’s worth checking:
- Whether the formula’s logic actually matches what you intended to calculate
- Whether the correct data was selected or referenced
- Whether any filters applied match what you actually wanted included or excluded
- Whether the underlying calculations make sense given what you know about the data
- Whether a generated chart accurately represents the data, rather than just looking clean
- Whether the business interpretation of a finding actually holds up given the full context
This kind of check matters because Copilot can produce a confident-looking result that’s still built on a misunderstanding of what you asked for, particularly with ambiguous data or an unclear prompt.
Build Your Excel Skills Alongside AI Tools
Copilot works best as an accelerator for people who already understand what Excel is doing underneath it, not as a replacement for that understanding.
If you’re still building your foundation in formulas, formatting, tables, and basic Excel functions, starting with the WSQ Basic Excel Course gives you the base needed to use Copilot’s output with confidence rather than guessing at whether it’s correct.
If you already work comfortably with Excel and want to handle more complex formulas, PivotTables, data analysis, and advanced functions, the WSQ Microsoft Excel – Intermediate & Advanced Level course is a suitable next step.
For users working with complex reporting, advanced formulas, automation, or more involved Excel workflows, the Advanced Excel Course in Singapore covers the skills needed to handle this type of work, while Copilot can help speed up repetitive tasks.
Copilot in Excel: Where It Helps Most
| Excel Problem | How Copilot Can Help |
| Formula creation | Generate and explain formulas |
| Formula troubleshooting | Identify likely issues in the logic |
| Data analysis | Find patterns and answer direct questions |
| Data cleaning | Identify duplicates and inconsistencies |
| Charts | Suggest and create visualisations |
| PivotTables | Summarise and group large datasets |
| Reporting | Extract comparisons and useful findings |
| Repetitive tasks | Speed up common, recurring workflows |
Copilot in Excel is most useful when you start with a specific problem rather than a vague request. Whether you need to understand a formula, analyse sales data, clean a table, create a chart, or identify a trend, a clear prompt gets you to the result faster than working through it manually. Understanding Excel fundamentals still matters, though, since that’s what lets you check and interpret what Copilot produces rather than accepting it on faith.