How to clean data in Excel, one problem at a time

Save a backup copy of the original data in a separate workbook and clean the copy, as Microsoft recommends. Then check the file for each problem below; every item has its own section on this page.

Fix stray spaces before you look for duplicates or count values. Microsoft notes that extra spaces and nonprinting characters can sometimes cause unexpected results when you sort, filter or search, and can make COUNTIF return an unexpected value.

Excel’s Data › Remove Duplicates deletes repeated rows immediately and keeps the first occurrence, without showing you which rows it removed. To see duplicates before touching them, add a helper column with COUNTIFS across the columns that define a record and filter for counts above one.

The free TidyX tool lists every exact duplicate row first and lets you keep the first or last copy, entirely in your browser, for files up to 10 MB, 50,000 data rows, 200 columns and 500,000 cells. It matches whole rows exactly, and so does the full cleaning run: neither deduplicates by a subset of columns or keeps the most recent record per ID, so those cases stay in Excel.

Go To Special › Blanks selects every empty cell, so deleting those rows also removes rows that were only missing one value. Filter first and check the whole row before deleting; a helper column with COUNTA over all data columns marks the rows that are truly empty when it returns zero.

By default, the free TidyX tool removes only rows where every cell is empty; treating whitespace-only cells as empty is an explicit option. Rows that contain a zero, a false value or text survive, and you can uncheck any candidate before the download.

COUNTBLANK per column and a conditional formatting rule for blank cells show where the gaps are before you change anything. Placeholders such as N/A, null or a dash are not blank to Excel, so they distort counts until you normalize them.

TidyX profiles missing values per column, proposes a rule that converts an explicit list of placeholder tokens into real missing values, and can propose separately reviewed filling rules. Nothing is imputed by default; each rule waits for your approval.

QUARTILE.INC and a 1.5 × IQR fence in a helper column mark the values that fall outside the usual range. Marking is the right first step, because an outlier can be a typo or a real extreme observation.

TidyX flags outliers with a parameterized rule and reports the affected rows. Treatment such as clipping or removal is a separate, high-risk rule that you acknowledge explicitly.

A PivotTable or UNIQUE over the column reveals variants such as Yes and Y; case-only variants such as Yes and yes need a case-sensitive sort or an EXACT helper column. TRIM and UPPER or PROPER remove the surface differences; true synonyms need a small mapping table and a lookup.

TidyX proposes trimming and case rules for named columns and shows explicit mappings for review. Review each mapping yourself: similar labels such as Paid and Unpaid can have different meanings.

Stray spaces make identical-looking values fail to match in lookups and filters. TRIM removes leading and trailing spaces and collapses repeated spaces inside the text, CLEAN removes the first 32 nonprinting characters, and SUBSTITUTE handles the nonbreaking space that arrives from web exports, for example TRIM(CLEAN(SUBSTITUTE(A2, UNICHAR(160), " "))). Use UNICHAR rather than CHAR here: CHAR(160) takes the character from your computer’s own character set, which is different on a Mac.

The local check on the TidyX home page reports how many cells carry leading or trailing spaces, with up to three column names and examples, without uploading the file. A cleaning plan can propose trimming for review.

Left-aligned numbers, a green triangle or a SUM that returns zero point to numbers stored as text. VALUE, Convert to Number, or Paste Special with Multiply by 1 fixes a column; Text to Columns handles stubborn imports.

The local check on the home page flags numeric-looking text that carries currency symbols or separators, with counts, column names and examples, so you know which columns need a number-parsing rule before analysis.

A column that mixes real dates with text dates such as 03/04/2024 or 2024-04-03 can sort and filter wrongly. Confirm first whether 03/04/2024 means day-month or month-day; DATEVALUE follows your regional settings, and Text to Columns lets you choose DMY or MDY explicitly. Agree on one format before the data moves anywhere else.

The local check on the home page flags columns with mixed date formats. In a cleaning plan, date parsing is an explicit rule with a stated format rather than a silent guess.

Opening a CSV by double-click lets Excel guess types, which strips leading zeros from ZIP codes and IDs. Use Data › From Text/CSV › Transform Data, set the identifier columns to Text before loading and replace the automatic type-change step, and pick the delimiter and encoding explicitly.

TidyX profiles comma-delimited CSV files as UTF-8 by default and first reads every column as text; other supported encodings can be selected when decoding fails. The CSV guide covers the remaining import and export pitfalls.

Unmerge the cells; where a label was merged vertically across a group of rows, fill it down once you have confirmed that is what it means. Keep a single header row and convert the range to a Table with Ctrl+T. For TidyX, the sheet needs one header row and one record per row.

TidyX profiles one worksheet that you select from the workbook, and it cannot read a workbook while any of its worksheets still contains merged cells. It does not combine sheets with different layouts, so prepare each sheet as a table before uploading it.

In Excel, save a copy before cleaning and document the transformations as you go; Undo and version history have limits.

TidyX never overwrites the uploaded file. Each run writes a new result and a report with the applied rules, row and column changes, warnings, input and output hashes and the engine version.

Power Query refreshes and macros can replay Excel steps, but they may fail or need adjustment after a column is renamed or moved. A saved TidyX recipe is versioned and is checked against the new file before it runs.

Compatibility changes that need confirmation stop reuse and ask for a new review instead of applying old rules to the wrong columns.

For a one-off question, such as which formula to use or how to phrase a mapping, a chat model can be useful. It can also draft a cleaning approach from a description of the columns.

When the result has to be applied to the whole file and defended later, execution needs a contract: fixed operations, a review step and a record of what ran. The ChatGPT guide walks through cleaning a file with ChatGPT step by step and shows where each approach fits.

For XLSX uploads, TidyX reads the workbook structure and asks you to select one sheet. The selected sheet is profiled with the same deterministic process as a CSV: row and column counts, inferred types, missing values, duplicate rows and warnings.

The AI proposes rules only from a fixed, validated set, including text normalization, date and number parsing, type casts, renames, removal of empty rows or columns, exact-duplicate removal and outlier flags. You review all suggestions together or step by step, and high-risk operations stay blocked until you acknowledge them.

Execution writes a new result with a PDF report and a JSON audit; the original workbook is never overwritten. New accounts start with 60 one-time signup credits, no card required.