A manager receives a file named Sales_Export.xlsx on Monday morning. It contains 2,000 rows of sales data, customer names with extra spaces, dates in three different formats, and no profit column. The review meeting is scheduled for 3 p.m.
Microsoft Copilot in Excel can help handle this type of situation. It is an AI assistant that responds to plain-English instructions and helps users work with formulas, tables, PivotTables, charts, and formatting features available in Excel. This guide demonstrates how to use Copilot to work through five common tasks using the same sales workbook, from cleaning data to preparing a written summary.
If you want to strengthen your Excel skills while learning how AI-assisted features work, explore the Microsoft Basic Excel Course in Singapore, which introduces Copilot and its use in everyday Excel tasks. Copilot can generate an initial result, but you should always check the formulas, figures, and outputs before using them in a report.
What Is Microsoft Copilot in Excel?
Copilot in Excel is a chat panel inside the workbook. A request typed into it is turned into an action on the data, such as adding a column, building a PivotTable or explaining a formula. The menu path that would normally lead to the same result is skipped.
Traditional Excel work follows a fixed order: find the right tab, choose the right tool, set the options. Copilot reverses that order. The goal is stated first, and Copilot picks the tool.
Depending on the Excel version and Microsoft 365 plan, Copilot may offer different working styles. Some versions answer questions only. Others edit the sheet directly or carry out several steps in sequence and show each one. Microsoft renames these options often, so the current list is best confirmed on Microsoft’s official Copilot in Excel support page.
What Can Copilot Do in Excel?
- Clean text, remove spaces and flag duplicate rows
- Write formulas and explain existing ones
- Build pivot tables and charts from a table
- Point out trends, outliers and top performers
- Apply conditional formatting, sorting and filters
- Write a short summary of a dataset
- Answer what-if questions, such as the effect of a cost increase
What You Need Before Using Copilot in Excel
Copilot responds better when the file is prepared. Check these points first:
- The Microsoft 365 plan includes Copilot.
- The data sits inside an Excel table (click any cell in the data and press Ctrl + T).
- Every column has a short, clear header, such as Order Date, Region or Revenue.
- The data has no merged cells and no blank rows.
- The file is saved to OneDrive or SharePoint with AutoSave on, if Copilot asks for it.
- A backup copy of the original file exists.
Does Copilot Work With Any Excel File?
Not always. Copilot reads structured data most reliably. Complex layouts, such as merged title rows, stacked tables or multi-line headers, can make it harder for Copilot to interpret the data correctly.
If Copilot reports that it cannot work with a file, three causes are common: the data is a loose range instead of a table, the file is stored only on a local drive, or the account has no Copilot licence. Requirements differ between Microsoft 365 setups, so the organisation’s IT administrator can confirm the exact conditions.
Excel Practice Workbook
The five tasks below use a single table named SalesData, so each step builds on the one before it.
| Column | What it holds |
| Order Date | Date of the order |
| Customer | Customer name |
| Region | Sales region |
| Product | Item sold |
| Units | Quantity sold |
| Revenue | Sales amount |
| Cost | Cost of the order |
The file has the usual faults of an exported report. Customer names carry extra spaces, “Mumbai” is typed in three ways, several orders appear twice and there is no Profit column. Prompts here are samples, so the column names should be changed to match the file in use.
5 Practical Ways to Use Copilot in Excel
1. Clean and Organise Excel Data With Copilot
Dirty data distorts every report built on top of it. A duplicated order inflates revenue, and a region spelt three ways splits into three PivotTable rows.
What to ask
- “Trim the extra spaces in the Customer column of SalesData.”
- “Highlight rows in SalesData that appear to be duplicate orders. Do not delete any row.”
- “List every different spelling used in the Region column.”
What to expect: Copilot trims the text, colours the suspected duplicates and returns a list of spelling variants. Each of these would otherwise be a separate manual step.
What to check
- Ask for highlighting before deletion, so every flagged row can be reviewed first.
- Compare each flagged pair. Two customers can buy the same product on the same day, and that is not a duplicate.
- Confirm that the corrected Region names match the spellings used in other reports.
Cleaning comes first because the four tasks that follow depend on it.
2. Create and Understand Excel Formulas With Copilot
The manager asks for profit and margin, but the file has only Revenue and Cost.
What to ask
- “Add a Profit column that subtracts Cost from Revenue.”
- “Add a Margin % column that divides Profit by Revenue and shows the result as a percentage.”
- “Add an Order Size column that shows Large when Units are 50 or more and Small for anything lower.”
- “Explain the formula in the Margin % column step by step.”
What to expect: Copilot proposes a formula, inserts the column into the table and, on request, explains the logic in plain language.
What to check
- Click one cell and read the formula. It should point at the intended columns.
- Calculate one row by hand to confirm the result.
- Look for divide-by-zero errors in rows where revenue is empty.
Reading the explanations Copilot gives is also a steady way to learn how formulas work. Copilot can explain a formula, but understanding how tables, references and calculations behave makes the checking step much faster and more reliable. These Excel fundamentals are useful when working with AI-assisted features because they help a user recognise whether a suggested formula actually makes sense. For learners who want to strengthen these skills further, Microsoft Excel Intermediate & Advanced Level Course covers advanced formulas, data analysis, PivotTables and other techniques used in more complex Excel tasks.
3. Analyse Data With PivotTables and Charts
The meeting needs sales by region and the monthly movement of revenue, and there is little time to build either.
What to ask
- “Create a PivotTable showing total Revenue and total Profit for each Region.”
- “Plot monthly Revenue as a line chart.”
- “Which Region earned the highest Profit, and which Product sold the most Units?”
- “Now show the same view for Q2 only.”
What to expect: Copilot builds the PivotTable and chart from the table and answers the questions in the chat. Follow-up requests refine the result because Copilot keeps the thread of the conversation.
What to check
- The chart covers the full date range.
- The PivotTable grand total equals the total of the source table.
- The chart has a clear title and labelled axes.
- Any outlier Copilot mentions is a genuine pattern in the data and not a leftover typo from Task 1.
The time saved here goes to the more useful work of deciding what the figures mean.
4. Format and Filter Excel Data
A correct sheet is still hard to present if it is cluttered.
What to ask
- “Shade rows light red where Margin % is under 10%.”
- “Sort SalesData by Order Date with the newest first.”
- “Show only the orders from the North region.”
- “Style the header row with a dark fill and white bold text.”
What to expect: Copilot applies conditional formatting, sorting, filtering and table styling. Short, single-purpose requests give the cleanest results. A complicated layout works better when it is split into small steps.
What to check
- After sorting or filtering, every column moves together and no row is separated from its data.
- The filter is cleared before the file is sent, unless the recipient should see the filtered view only.
5. Summarise Excel Data and Find Insights
A manager rarely reads 2,000 rows. A short summary is what gets read.
What to ask
- “Summarise the main trends in SalesData in five bullet points.”
- “List the five customers with the highest Revenue.”
- “If Cost rises by 10%, what happens to total Profit?”
What to expect: Copilot writes a plain-language summary, names patterns and runs simple what-if scenarios. This is where Copilot works as a tool for data analysis, turning a large table into something readable.
What to check
- Match every figure in the summary against the PivotTable.
- Delete any sentence that the data cannot confirm. A summary can read confidently even when one detail is wrong.
- Judge whether each insight makes business sense. Copilot sees the numbers but not the context behind them, such as a one-time bulk order or a pricing change.
- Some Excel versions also include a COPILOT function that places AI analysis directly in a cell, for example, to classify a list of comments.
- Availability depends on the version, so it is worth testing on a small range first.
How to Write Better Copilot Prompts for Excel
Specific prompts produce predictable results. A reliable structure has four parts:
Action + Data + Rule + Output
- Action: what to do, such as add, highlight or summarise
- Data: the table or column involved
- Rule: the calculation or condition to apply, such as Revenue minus Cost, below 10% or only North
- Output: the form of the result, such as a new column, a chart or five bullets
Examples built on this structure:
- “Add (action) a Profit column to SalesData (data) using Revenue minus Cost (rule) and format it as currency (output).”
- “Highlight rows in SalesData where Margin % is below 10% in light red.”
- “Summarise monthly Revenue by Region and name the three largest month-to-month changes in a short list.”
- “Create a PivotTable of Units by Product for the North region only, sorted from highest to lowest.”
A vague request such as “fix my data” leaves Copilot guessing. If a prompt fails, splitting it into two smaller prompts usually solves the problem.
What Copilot in Excel Cannot Do Reliably
Copilot predicts likely answers from the data it sees. It does not understand the business behind the sheet, and that gap explains most of its weak spots.
- Formulas can be wrong. A formula may run without an error and still point at the wrong column.
- Summaries can misread data. A seasonal dip may be described as a decline.
- Duplicate detection needs judgement. Matching rows are not always repeated entries.
- Business context is missing. Copilot does not know about discounts, returns or a changed pricing policy unless the sheet says so.
- Large or irregular files can cause trouble. Complex layouts and heavy workbooks may give partial or inconsistent answers.
- Output is not authoritative. A figure produced by Copilot should be treated as an output to verify, not as an authoritative result.
Common Mistakes When Using Copilot in Excel
- Giving vague instructions. Name the column, the rule and the result wanted.
- Working with poorly structured data. Merged cells and missing headers confuse the tool, so tidy the layout first.
- Accepting output without checking. Treat each result as a draft.
- Editing without a backup. Save a copy before any large change.
- Sharing confidential information carelessly. Follow the organisation’s rules on what data may be used with AI tools.
- Asking for too many actions at once. One request per step is easier to check and to undo.
When Should Human Review Come First?
The mistakes above are things a user does wrong. The situations below are different because they call for a person to lead and for Copilot to assist at most.
- Financial reporting, audits and anything tied to compliance
- Legal or contractual calculations
- Datasets that contain personal or commercially sensitive information
- Decisions with a high cost if the figure is wrong
- Results that cannot be checked against a second source
In these cases, Copilot can suggest an approach or a first draft, and every number should then be confirmed by hand.
Final Thoughts
Applied to one messy sales export, Copilot covered the whole path from cleaning to a meeting-ready summary. The gain comes less from raw speed than from where the effort goes. Less time is spent searching menus, and more is spent checking figures and deciding what they mean.
