Clean an Excel Spreadsheet: Steps for Cleaner Data

How-to basics · Updated November 22, 2026

To clean an Excel spreadsheet, make a copy, standardize text and data types, resolve blanks and duplicates, and then check the results against the source. In Microsoft Excel for Microsoft 365 on Windows, a reliable process combines built-in tools such as Remove Duplicates and Text to Columns with formulas and careful review.

What spreadsheet cleaning involves

Cleaning means making a dataset consistent and usable without changing what its records actually say. A list of customer names might contain extra spaces, several spellings of the same region, dates stored as text, or repeated rows. Those issues can cause filters, sorting, lookups, and summaries to give confusing or incomplete results.

Cleaning is not the same as deleting every unusual value. A blank cell may mean “not provided,” while a zero may be a real measurement. Two rows with the same person’s name may represent separate orders rather than a duplicate. Decide what a field means and what counts as an error before changing it. Keep an untouched source copy so you can verify ambiguous cases.

Make a safe copy and inspect the data

Save a separate working copy before making bulk changes. If the workbook is shared, confirm that you are editing the intended file and that other users are not changing the same data. Look at the column headings, row count, filters, formulas, and a sample of records from the top, middle, and bottom. Check whether the data contains multiple tables or notes mixed into the range.

For a simple rectangular dataset, click a cell inside it and choose Insert > Table, confirm the range, and select My table has headers if the first row contains field names. A table expands as you add rows and makes filtering easier. Avoid including titles, blank separator rows, or totals in the table range unless they are genuinely part of the records.

Before editing, note useful checks such as the number of records, a total that should remain unchanged, or the number of distinct categories expected. Those checks help reveal accidental deletions or unwanted conversions later. If the workbook contains formulas, identify them before replacing cells; a cleanup operation should not overwrite calculations or supporting notes.

Standardize text and remove hidden spaces

Inconsistent text is a frequent source of mismatches. For example, North, north, and North followed by an invisible space may appear equivalent to a person but behave as different values in a lookup or PivotTable. Decide on a standard spelling and capitalization for categories, then apply it consistently. Do not force names or product codes into a different case if capitalization carries meaning.

TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words to one. CLEAN removes many nonprinting control characters. Some copied data also contains a nonbreaking space, which TRIM alone does not remove. With source text in A2, use this formula in a helper column:

With the original text in A2, the formula below replaces nonbreaking spaces with ordinary spaces, removes nonprinting characters, and trims extra spaces. For example, a cell containing padded text for North Shore returns North Shore.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Fill the formula down, inspect the cleaned results, and only then copy the helper column and use Home > Paste > Paste Values if you want to replace the original text. Keep the original column until you have checked the outcome. CLEAN does not remove every possible Unicode character, so unusual symbols or invisible characters from imported systems may require identifying the character and replacing it explicitly.

For a small number of known variations, Find and Replace can be quicker: choose Home > Find & Select > Replace, enter the old text and its replacement, and review the scope before selecting Replace All. Use this for precise corrections, such as changing a known misspelling, not for broad replacements that might alter legitimate words or codes.

Correct dates, numbers, and imported values

A cell can look like a date or number while Excel stores it as text. Sorting text dates can put them in an unexpected order, and formulas may ignore text-formatted numbers. Select a few cells and inspect the formula bar and number format, but remember that changing the display format alone does not always convert text into a real number or date.

If values are separated consistently, select the relevant column and choose Data > Text to Columns. Follow the wizard to choose the delimiter or fixed-width layout, and set the destination and data format carefully. For dates, select the correct date order when the source is ambiguous; for instance, 03/04/2025 could mean March 4 or April 3 depending on its origin. Check several converted cells before applying the method to the whole dataset.

For numbers imported with separators or decimal conventions from another region, check the source format before converting. A comma might represent a thousands separator in one source and a decimal mark in another. Avoid using a global replace on commas or periods until you know whether those characters are part of the value or separate fields. If Excel displays a green triangle or a warning icon, use the cell’s error-checking options only after confirming what the value should be.

Handle blanks and duplicate records carefully

Filter a column to inspect blanks, but do not automatically fill them with zero or delete their rows. A missing shipping date, a not-applicable field, and an accidental omission are different situations. If a blank should have a known value, use a documented rule and retain a way to distinguish estimated or filled-in values from original entries.

To find possible repeated values, add a helper formula. With IDs in A2:A100 and the current record’s ID in A2, this formula counts how often that ID appears in the full range; a result of 2 means the ID occurs twice, including the current row:

=COUNTIF($A$2:$A$100,A2)

Filter the helper column for results greater than 1 and review the corresponding records. An ID may be expected to repeat if each row represents a line item, so define the record’s unique key first. A transaction might be uniquely identified by both order number and line number, while a customer list might use a customer ID.

When removal is justified, select the dataset or table and choose Data > Remove Duplicates. In the dialog, select the columns that define an identical record. Selecting every column removes only rows that match across all selected fields; selecting just an ID removes records sharing that ID even if other details differ. Excel removes later matching rows and keeps the first occurrence, so sort or review the data first if the retained row matters. Make a copy before removal because the operation can be difficult to reverse after saving or making further edits.

Check consistency, errors, and totals

After standardizing values, inspect each column for impossible or unexpected entries. A quantity might need to be a positive whole number; a status column might permit only Open, Closed, or Pending. Apply filters to find outliers, and compare categories against an authoritative list when one exists. Conditional formatting can make suspicious values easier to spot, but highlighting is a review aid, not proof that a value is wrong.

Check formulas and summary results after cleaning. Compare record counts and totals with the values you noted before editing. If a total changed, find out whether the change is expected—for example, because a confirmed duplicate was removed—or whether a row or number was converted incorrectly. Inspect representative records after sorting and filtering, since a clean-looking summary can still conceal errors in individual rows.

Excel tables can also help keep filters and headers attached to a dataset. If you create a PivotTable for a quality check, compare its counts or totals with your expected results rather than treating the PivotTable as an independent validation. For sensitive or operational data, document what was changed, why it was changed, and which source or rule supported the decision.

Make the cleanup repeatable

For a one-time list, helper columns and built-in commands are usually sufficient. For recurring imports, Power Query can record transformation steps, such as removing rows, splitting columns, changing data types, and trimming text. In Excel for Microsoft 365 on Windows, select a cell in a source table and choose Data > From Table/Range to open the Power Query Editor. Review the applied steps and refresh the query when the source is updated.

Power Query is useful because it can apply the same documented sequence to later versions of a source, rather than requiring the same manual edits each time. It does not remove the need to check meaning: a transformation that removes blank rows may be wrong if blank rows separate meaningful sections, and a conversion can misread dates if the locale is incorrect. Power Query availability and connector features vary by Excel platform; on Mac, check the Data tab in the installed version for the available import and query tools.

For a team workflow, keep the original import, the cleaned output, and a short description of the rules separate. Record decisions such as whether leading zeros in postal codes must be preserved. That field should generally be treated as text, not a number, because arithmetic on it is not useful and converting it may remove the zeros.

Common cleaning mistakes to avoid

  • Deleting rows just because they look unusual: investigate the source and business meaning first.
  • Removing duplicates using the wrong columns: choose a key that identifies one real record, not simply a familiar name.
  • Changing formats without converting values: a date display format does not guarantee the underlying cell is a date.
  • Overwriting source data too early: use helper columns, review results, and preserve a recoverable copy.
  • Assuming blanks and zeros mean the same thing: they represent different information and affect calculations differently.

Ctrl+Z on Windows, or Command+Z on Mac, can undo many recent actions, but it is not a substitute for a backup. Some actions become difficult to undo after closing the workbook or performing later operations. Menu names and feature availability can differ between Windows, Mac, and Excel for the web, so check the interface in the version you are using before following platform-specific instructions.

Frequently Asked Questions

How do I clean an Excel spreadsheet quickly?

Make a copy, convert a well-formed data range to a table, standardize obvious text issues, check date and number types, review blanks and duplicates, and compare totals with the original. Review the results before replacing source values or deleting records.

What is the formula to remove extra spaces in Excel?

TRIM removes ordinary extra spaces from text. If copied data may contain nonbreaking spaces, use the SUBSTITUTE, CHAR, CLEAN, and TRIM combination shown above, then inspect the output because some uncommon invisible characters need separate handling.

Does Remove Duplicates delete every copy?

No. Excel keeps the first occurrence among the selected columns and removes later matching rows. The columns selected in the dialog determine what counts as a duplicate, so check the selection and keep a backup before using the command.

How can I tell whether a date is stored as text?

Check how Excel sorts and calculates with the value, inspect its alignment and number format, and test a small sample by converting it in a helper column or with Text to Columns. Appearance alone is not conclusive; confirm the date order from the source before converting ambiguous dates.

Can Excel clean a spreadsheet automatically?

Excel provides tools such as Text to Columns, Remove Duplicates, and Power Query to automate specific transformations. Automation is safest when the cleanup rules are explicit and repeatable, and the resulting rows, dates, and totals are still checked against the source.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.