Data cleanup in Excel is quite boring. Someone types “electronics” in lowercase, someone else uses all caps, and suddenly your VLOOKUP returns errors for no obvious reason. These small inconsistencies compound over time, and for years, fixing them meant relying on data-cleanup functions like TRIM and CLEAN — functional, but tedious for something that shouldn’t require manual effort.
Excel’s new Clean Data feature, under the Data tab, finally addresses this. It uses Copilot to scan your table for spacing issues, mixed capitalization, and numbers stored as text, then suggests one-click fixes. It’s not a full cleanup suite, but for everyday data entry headaches, it’s long overdue.
Clean Data fixes inconsistencies without a formula
Here’s how to access and use the feature
Clean Data lives in the Data tab on the ribbon. But before you click it, it’s a good idea to format your data as an Excel table — the feature won’t work as well on raw cell ranges. If you’ve been dumping data into plain rows without converting them, now’s a good reason to start.
Once your data is in a table, the process is straightforward:
- Go to the Data tab and click Clean Data.
- Copilot scans your table and displays suggestion cards for each issue it finds.
- Review each card, which shows the problem and previews the fix.
- Click Apply to accept the fix or Ignore to skip it.
Copilot in Excel is actually brilliant, as each suggestion is specific. I appreciate that it doesn’t change your data behind the scenes. You see exactly what it wants to fix before anything changes.
If Clean Data doesn’t appear in your ribbon, there are a couple of things to check. The feature requires a Microsoft 365 subscription with Copilot enabled — it’s not available in standalone versions of Excel, such as Office 2021. Make sure your app is up to date by selecting File > Account > Update Options. If you’re on a work or school account, your IT administrator may need to enable Copilot access for your organization before the button shows up.
Clean Data performs best when your table has clear column headers and stays within 100 columns and 50,000 rows. If you go beyond that, results get less reliable. For most everyday spreadsheets, though, those limits are more than generous.
These are the types of issues Clean Data catches
Clean Data targets four specific categories of inconsistencies, and each one is the kind of thing that breaks your spreadsheet without you realizing it.
Extra and inconsistent spacing is the most common offender. In a sales dataset like mine — with columns for Region, Product Category, and Salesperson — it’s easy for entries like “John Smith” and “John Smith” (with a double space) to coexist in the same column. These look identical at a glance, but Excel sees them as different values. A COUNTIF on “John Smith” would miss the double-spaced version, and your sales report would undercount his entries. Clean Data flags these spacing issues, including leading spaces, trailing spaces, and extra gaps between words, and offers to standardize them.
Inconsistent capitalization is another one. If the Product Category column has “Electronics,” “electronics,” and “ELECTRONICS” scattered across rows, you end up with three separate entries in your PivotTable instead of one. Clean Data detects these variations and suggests unifying them. This is especially useful when data comes from multiple people, and someone always types in all caps.
Number and text format mismatches are the sneakiest issue. This happens when some cells in a column, like Sales Amount, contain actual numbers, while others store numbers as text. The values look the same on screen, but Excel treats them differently. A SUM formula will skip text-formatted numbers without any warning, and your totals will be quietly wrong. Clean Data identifies these mixed-format columns and offers to convert everything to the correct type.
Text inconsistencies round out the list. These include differences in punctuation, diacritics, or minor variations like “Home & Garden” and “Home and Garden” in the same column. Copilot picks up on these patterns and suggests standardizing the entries to a single version.
The important distinction here is that Clean Data doesn’t just point out problems. Each suggestion card shows the before and after, so you see exactly what changes before you commit. If Copilot suggests the wrong standardization, say it wants to capitalize something you’d rather keep lowercase, you can ignore that specific card and move on.
Clean Data still has a few limitations worth knowing
It’s a solid starting point, not a complete cleanup tool
Clean Data won’t remove duplicate rows, fill in missing values, or split combined columns. For those, you’d still need Power Query cleanup commands or dedicated functions. It also won’t catch everything. In my testing, it sometimes missed inconsistencies involving abbreviations while catching others that impressed me. There’s also the subscription factor, since Clean Data requires Microsoft 365 with Copilot enabled, so standalone Excel versions don’t have access.
That said, Clean Data works well as a first pass before you dig into deeper analysis. Run it after importing data from an external source, or before building a PivotTable on a shared workbook — those are the moments where hidden inconsistencies tend to surface. If it catches even two or three spacing or formatting issues you would’ve missed, that’s a broken formula you avoided without thinking about it. For anything beyond that, Power Query and Python in Excel can pick up the slack.
