Excel Clean Data Button: Real Results

Key insights

• Clean Data overview:
  Excel’s new Clean Data uses Copilot AI to scan sheets and suggest fixes for capitalization, punctuation, number formats, and spacing.
  The tool shows a sidebar where you can accept or reject each suggested change.
• How it performs in tests:
  It handles simple text cleaning and trimming spaces well, works reasonably on numbers, but shows mixed results with date cleaning and complex mixed errors.
  Results can be inconsistent when you ask it to fix many issues at once.
• Manual alternatives and building blocks:
  Use Power Query for repeatable, auditable transforms and Excel functions like TRIM, ISNUMBER, and UNIQUE when you need precise control.
  These methods remain important when AI suggestions miss edge cases.
• Key benefits:
  The AI speeds up repetitive tasks and helps spot issues many users miss, improving overall data quality and efficiency.
  It also integrates with Excel’s workflow so you can keep AutoSave and other controls active.
• Important limitations:
  The feature can produce inconsistent results, may not detect all errors, and does not reliably remove duplicates or fix every date format problem.
  Expect to review and validate changes rather than applying them blindly.
• Practical tips for use:
  Review each suggestion carefully, combine AI cleaning with manual checks, and disable automatic conversions when working with scientific or alphanumeric data to protect data integrity.
  Use AI to speed routine cleanup, but keep Power Query and formulas for complex or repeatable tasks.

Video at a glance

Kenji Farré (Kenji Explains) [MVP] published a hands-on video testing Excel’s new Clean Data button, which uses Copilot-driven AI to automate common data-cleaning tasks. First, he walks viewers through several typical spreadsheet problems, and then he shows how the new button addresses them automatically. The video aims to compare the AI approach with familiar manual methods and to highlight where automation helps and where it still falls short.

Importantly, Kenji structures his tests around real-world scenarios like messy text, numeric formats, stray spaces, and date errors. He also runs a final test asking the tool to fix all problems at once, which reveals how the feature behaves when multiple error types coexist. Along the way, he demonstrates manual alternatives using built-in formulas and tools so viewers can judge tradeoffs themselves.

How Kenji tested the feature

Kenji sets up clear examples to test the new Clean Data button in the desktop Win32 version of Excel, focusing on practical issues that professionals face daily. He first tests text normalization such as capitalization and punctuation, then moves to numbers and spacing, and finally shows how the AI handles dates that are inconsistent or misparsed. By isolating each scenario, the video makes it easier to see both successful corrections and missed issues.

After individual tests, Kenji runs a comprehensive pass where the tool must correct all error types in a single go, allowing viewers to see the interaction effects. He also shows manual methods using functions like TRIM, ISNUMBER, and UNIQUE as a baseline for reliability and predictability. This comparison highlights how automation can save time while also raising questions about consistency when multiple fixes are applied together.

Strengths: speed and accessibility

One clear advantage Kenji highlights is speed: the Clean Data button can identify and suggest fixes for many common issues in a few clicks, which is valuable when cleaning large datasets. Moreover, it lowers the barrier for users who lack expertise with Power Query or complex formulas, because the AI suggests fixes in a guided sidebar rather than requiring manual steps. As a result, less-experienced users can achieve cleaner data without building queries or writing nested formulas.

In addition, the feature preserves user control by presenting suggestions that can be accepted or ignored, which helps prevent unwanted automatic changes. Kenji notes that this balance between automation and review makes the tool practical for routine use. Consequently, teams can speed up data preparation while maintaining oversight over each correction.

Limitations and tradeoffs

However, Kenji’s tests also reveal important limits. For example, the tool sometimes misses partial errors, does not consistently detect duplicate rows, and struggles with certain date formats, which means automation alone can introduce gaps in data quality. Furthermore, the AI’s results can be inconsistent from one run to the next, a problem Kenji compares to other AI features in Excel such as Agent Mode that also show variability.

These observations highlight a broader tradeoff: automation speeds work but can sacrifice predictability and fine-grained control. While manual methods like TRIM and ISNUMBER are slower, they are deterministic and easier to audit. Therefore, users must balance the convenience of AI-powered fixes against the need for reproducible, verifiable transformations—especially when working with sensitive or scientific data.

Practical takeaways for Excel users

Kenji’s practical advice is to treat the Clean Data button as a productivity booster rather than a full replacement for traditional cleaning workflows. In routine cases, it can eliminate many small issues quickly, but when data quality is critical, users should validate results or combine AI suggestions with proven steps such as Power Query steps or explicit formulas. This hybrid approach helps keep work efficient while ensuring accuracy.

Finally, Kenji recommends documenting changes and keeping source copies before applying bulk corrections, because the AI can behave differently on similar datasets. Although Microsoft continues improving the feature, his video urges caution and verification: use AI to speed repetitive tasks, but rely on manual checks and established tools when you need consistent, auditable results. Overall, the video gives a measured view that helps users weigh speed, control, and reliability when deciding how to adopt this new Excel feature.

Keywords

Excel Clean Data button, Clean Data Excel feature, Excel data cleanup tutorial, Excel data cleansing tool, How to use Clean Data in Excel, Excel Clean Data review, Microsoft 365 Clean Data button, Automate data cleaning Excel