Use Copilot to propose and apply a defined cleaning rule—not to guess what ambiguous values mean. The safest workflow keeps the source data, writes candidate values separately, and checks exceptions before replacing anything.
Current Copilot in Excel can make direct workbook edits, so an old “nothing changes until you click Apply” assumption is unsafe. Official product guidance checked October 14, 2026.
Define the correction and preserve the source
Make a working copy or verify version history before starting. Give the source clear headers and identify the exact sheet, column, and rows. Record the row count and any control totals. Do not change the only copy of an imported dataset while experimenting.
Choose one problem at a time: unwanted spaces, a documented code mapping, a date display, or a numeric-text conversion. Tables are useful for structure, but current Copilot is not universally limited to Ctrl+T tables. Check your account’s available Excel modes; use planning or chat before editing when available.
Write down exceptions. Product codes may need leading zeros; names may use intentional capitalization; “NY” may mean a state rather than New York City. Without a trusted mapping, those are review items, not automatic replacements.
Clean into a candidate column, then compare
- Open Copilot from the icon available in your Excel version.
- State the range and output explicitly: “In Sheet1, inspect B2:B200. Propose a new CleanedCity column without changing B, deleting rows, or merging records. First list the transformations and any ambiguous values.”
- Review the proposed logic. Ask for an exact formula or mapping and examine several ordinary and exceptional rows.
- If the rule is appropriate, allow it to create the candidate column. Check the actual formulas and results in the workbook, not only the chat summary.
- Compare the row count, identifiers, exceptions, and totals. Keep the original column until the cleaned result is approved.
Spaces and capitalization
For a text value in B2, =TRIM(B2) removes surrounding ordinary spaces and collapses repeated ordinary spaces inside the text. It does not remove every Unicode space. Microsoft’s TRIM documentation specifically notes the nonbreaking-space limitation.
Use UPPER, LOWER, or PROPER only when that rule is appropriate for the field. Do not title-case people’s names or case-sensitive identifiers simply to make them look consistent. Ask Copilot to preserve the original and flag uncertain rows.
Dates
If a cell already contains a real Excel date, changing its number format to yyyy-mm-dd changes the display without converting the date to text. TEXT produces text, which is a different result; see Microsoft’s TEXT guidance.
If the input is text such as 03/04/2026, first establish whether the source means March 4 or April 3. Do not let a prompt silently choose the locale. Preserve unresolvable entries for review and verify that downstream formulas still receive dates of the intended type.
Numbers, codes, and duplicate-looking labels
Specify the source decimal and thousands separators before converting numeric text. Keep account numbers, postal codes, and long identifiers as text when they are labels, not quantities. For name variants, supply an approved exact mapping and leave unmatched entries unchanged.
Standardizing a label is not deduplicating records. Two rows with the same cleaned name may still represent different transactions. Require a separate, documented duplicate key before considering row removal.
Validate the result and recover safely
Compare the before and after row counts, nonblank identifiers, totals, and a set of known edge cases. Inspect formulas for overbroad replacements and errors hidden by IFERROR. For a recurring import, keep the approved transformation in a repeatable process rather than relying on a fresh natural-language guess each month.
Unexpected changes appeared immediately
Stop Copilot and review the changed cells. Editing mode can change the workbook directly. Use Undo or a verified earlier version when appropriate, and coordinate with collaborators before restoring a shared file.
Closing the file did not restore the original
AutoSave may already have saved changes. “Close without saving” is not a dependable recovery procedure. Use the copy or version history you checked before the work; Microsoft’s Copilot in Excel tips discusses direct edits and recovery options.
The candidate mapping remains uncertain
Leave those rows unchanged and ask the data owner. A smaller set of verified corrections is preferable to a clean-looking column that silently merges different entities. Do not claim that Copilot is inherently faster or more accurate than manual cleaning without measuring the actual task.