An imported worksheet can look simple until you start cleaning it. A request to standardize names or remove duplicates can affect formulas, table structure, data validation, and formatting that someone else relies on.
Copilot in Excel modes provide a useful safety boundary before that work begins. Use Chat to inspect the workbook, Plan to review a proposed approach, and Edit only when you are ready to let Copilot make changes.
Important: mode availability and behavior can vary by Excel version, platform, organization, and feature rollout. Microsoft documents different availability across desktop, web, and mobile experiences. Check the modes shown in your Copilot pane before relying on a workflow.
Quick Fix: start with the lowest-risk mode
| If you need to… | Start in | Why |
|---|---|---|
| Understand columns, anomalies, formulas, or data quality | Chat | Ask for analysis without modifying the workbook. |
| Define a cleanup sequence and review its scope | Plan | Review the proposed steps before any changes begin. |
| Carry out a defined workbook task | Edit | Edit can change the workbook, so use a separate file copy and a narrow request. |
Safest workflow: inspect in Chat, review the scope in Plan, then use Edit only for an approved task. Do not begin in Edit simply because the final goal is cleanup.
Table of Contents
- What each mode is best for
- What to do before Edit mode
- A safe cleanup example
- Post-edit Excel audit checklist
- Common mistakes and safer alternatives
Microsoft describes Chat, Plan, and Edit in its current Copilot in Excel guidance. Chat is for workbook analysis, Plan produces a reviewable approach, and Edit can make workbook changes. See the sources at the end of this article for current availability and mode details.
What each mode is best for
Chat: inspect before you touch anything
Start with Chat when you do not yet know the shape of the problem. It is the best first choice for an unfamiliar export, a handoff workbook, or a sheet containing formulas you have not checked.
Ask for findings, not action:
- “Analyze the table on the Import sheet. List inconsistent values in the Region column, but do not change the workbook.”
- “Identify columns containing formulas and summarize the formula patterns.”
- “Find likely duplicate customer records using Customer ID, email address, and phone number. Return a summary only.”
Keep review findings separate from the imported source data until you have approved the cleanup rule.
Plan: make the cleanup rule reviewable
Use Plan when you know the desired outcome but need to define the steps. “Clean the customer export” is not a sufficient instruction. It could mean trimming spaces, filling blanks, deleting rows, changing date formats, or overwriting source values.
“Create a cleanup plan for the Import sheet. Identify leading and trailing spaces in Customer Name and Region, and identify duplicate Customer IDs for review. Do not apply changes. List any formulas, validation rules, table names, or conditional formatting that could be affected.”
Review the target sheet, target columns, proposed changes, and any step that could delete, replace, or fill values. For a deeper planning workflow, see how to use Copilot Plan mode in Excel.
Edit: apply a bounded, approved task
Use Edit only after you have defined what may change and what must be checked afterward. A request to preserve formulas, formatting, validation, or table structure is a useful safeguard, but it is not a guarantee. You still need to audit the result.
“On the Import sheet only, trim leading and trailing spaces in Customer Name and Region. Do not delete rows. Do not change formulas, number formats, validation rules, conditional formatting, or table structure. Report the number of cells changed.”
Avoid prompts such as “fix this spreadsheet” or “make the data consistent.” They do not identify the authoritative value, explain how blanks should be handled, or distinguish true duplicates from separate business records.
Before you use Edit mode
Create a separate file copy before direct editing: use File > Save As and give it a clear name such as Customer Import - Copilot working copy.xlsx. A copied worksheet is useful for comparison, but it may not fully isolate workbook-level items such as named ranges, connections, queries, or other shared workbook features.
- Keep the original import unchanged.
- Write down the approved columns and allowed values.
- Decide whether blanks, duplicates, and unusual spellings require review rather than automatic changes.
- Use one cleanup task per request when practical.
- Record a key total or row count before editing for comparison afterward.
Copilot in Excel modes: a safe cleanup example
Assume an imported table named ImportData contains the following rows:
| Customer ID | Customer Name | Region | Order Total |
|---|---|---|---|
| C-1042 | Northwind Ltd | west | 1,250 |
| C-1043 | Northwind Ltd | West | 980 |
| C-1044 | Alpine Ski House | WEST | 2,300 |
The visible problem is inconsistent spaces and capitalization. The hidden risk is that Customer Name may feed lookups, Region may have data validation, and Order Total may be formula-driven elsewhere in the table.
1. Inspect with Chat
“Inspect ImportData. Which columns contain formulas, validation, blanks, or inconsistent text casing and spaces? Do not make changes.”
Use the findings to identify columns that should not be edited. A formula column should remain out of scope unless formula changes are explicitly approved.
2. Review the rule with Plan
“Propose a plan to standardize Region values to West, East, North, or South, and remove leading and trailing spaces from Customer Name. Keep Customer ID and Order Total unchanged. Do not apply the plan.”
Confirm that west and WEST are truly intended to mean West. If the business uses more detailed regional labels, clarify the approved list before moving on.
3. Make the approved change with Edit
“Apply the approved change to ImportData only. Change Region only where the intended value is unambiguous. Do not delete records. Report each change type and the number of cells changed.”
This is safer than asking Copilot to “clean duplicates.” Similar records can represent separate orders, contacts, or transactions.
Run an Excel audit after Edit mode
Copilot’s completion summary is useful, but it is not the final control. Verify the working copy in Excel after any direct edit.
- Check sheet and table boundaries. Confirm no unexpected rows, columns, sheets, or table headers changed.
- Filter edited columns. Filter Region and inspect its unique values for blanks, alternate spellings, or unexpected categories.
- Check formulas. Compare several formula cells with neighboring rows. Watch for changed references, constants replacing formulas,
#REF!, and#VALUE!. - Check validation. Select representative input cells and use Data > Data Validation to confirm the expected rule remains.
- Check formatting and totals. Review number formats, conditional formatting, filters, and totals rows.
- Recalculate and compare. In desktop Excel, press
Ctrl+Alt+F9for a full calculation, then compare key totals and row counts with the original file.
If Copilot edited or created formulas, test representative cells before using the workbook for reporting. See how to test Copilot-generated Excel formulas.
Common mistakes and safer alternatives
| Issue | Why it happens | Safer approach |
|---|---|---|
| Starting in Edit for an unfamiliar file | The real scope is unknown. | Inspect first, then define the permitted change. |
| Using “standardize” without a rule | The intended spelling, format, or value is unclear. | Provide approved values, target columns, and examples. |
| Removing duplicates immediately | Similar rows may be legitimate separate records. | List candidate duplicates first and decide the deletion rule yourself. |
| Using only a copied sheet as protection | Workbook-level features may still be involved. | Create a separate file copy before Edit mode. |
| Trusting a completion message without checks | A completed task does not prove business correctness. | Audit formulas, validation, formats, totals, and row counts. |
For recurring imports, documented workbook rules are often better than repeating a long prompt. Define permitted columns, allowed values, and checks in advance with these workbook rules for Copilot in Excel.
When classic Excel is the better tool
Use Copilot when you need help inspecting an unfamiliar workbook or turning a business request into a reviewable task. Use standard Excel tools when the rule is exact and repeatable.
For recurring imports, tools such as Data > Remove Duplicates, Power Query, table filters, and formulas provide explicit, repeatable controls. Copilot can help investigate the problem; established Excel tools can enforce a routine you have already defined.