Excel Cleanup Guide: How to Clean and Organize Data

How-to basics · Updated November 19, 2026

To clean an Excel workbook, first save a copy, inspect the data, then standardize text and formats, check for duplicates and blanks, and verify that dates and numbers are stored correctly. In Microsoft Excel for Microsoft 365 on Windows, use built-in tools such as Remove Duplicates, filters, and Power Query, and keep a record of changes when the data matters.

Start with a safe cleanup plan

Before changing anything, make a copy of the workbook with File > Save As. Cleaning can remove rows, alter values, or change how Excel interprets dates, so a separate original gives you a way to recover information that was deleted or transformed. If the workbook is shared or used for reporting, tell collaborators when you are making changes and avoid editing a live source file without agreement.

Identify what each row and column is supposed to represent. A customer list might have one row per customer and columns for customer ID, name, email, and region. A sales table might have one row per transaction, where the same customer can appear many times. This distinction matters: repeated customer names may be expected in a transaction table, while repeated transaction IDs may indicate a problem.

  • Keep a copy of the source data and note where it came from.
  • Check for headers, the expected number of columns, and any notes or totals mixed into the data.
  • Decide what counts as a duplicate, a missing value, and a valid format before removing or changing anything.
  • Clean a small sample first if the workbook is large or the rules are uncertain.

For a reversible editing history, use Ctrl+Z on Windows or Command+Z on Mac immediately after an unintended action. Undo is not a substitute for a backup: it may not restore changes after closing the workbook, and it cannot undo a macro.

Inspect the data before editing

Look for common warning signs: blank rows inside a table, multiple header rows, trailing spaces, inconsistent spelling, numbers aligned as text, dates in mixed formats, and totals included among the records. Filters make inspection easier. Select a cell in the dataset, then choose Data > Filter. Each header receives a dropdown that can show particular values, blanks, or errors.

For a larger dataset, turn the range into an Excel table with Insert > Table, confirm that the selected range is right, and check My table has headers if appropriate. Tables provide filter buttons and expand when new rows are added. Do not include a report title or a totals row in the table range unless it is genuinely part of the data.

Conditional formatting can help reveal patterns without changing cell contents. To highlight repeated values in a selected range, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is a visual flag, not a decision about which record to keep. Review the highlighted rows in context before deleting anything.

Standardize text and remove unwanted characters

Names and categories that look alike may not match exactly. For example, “West” and “West ” contain different text if one has an extra trailing space. A pasted value can also contain a nonbreaking space, which is visually similar to an ordinary space. Inconsistent capitalization, abbreviations, and punctuation can create extra categories in a PivotTable or when matching records.

For ordinary spaces and many nonprinting characters, a helper column lets you clean a value while preserving the original. With the uncleaned text in A2, enter this in B2 and fill down:

With the source text in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) replaces nonbreaking spaces with regular spaces, removes many nonprinting characters, and trims extra ordinary spaces. If A2 contains a value with leading and trailing spaces around “North Ridge,” the result is “North Ridge.”

TRIM does not remove every kind of Unicode whitespace, and CLEAN is not a general-purpose remover of all unusual characters. Inspect suspicious values after applying a formula. When the cleaned column looks right, copy it and use Home > Paste > Paste Values to replace formulas with their results, or keep the original and cleaned fields side by side for traceability.

Standardizing spelling requires a rule, not just a formula. Decide whether “N.Y.,” “NY,” and “New York” should become one label, then use Find and Replace or a small mapping table to make the change consistently. Avoid automatically changing names or addresses based only on capitalization; title case can alter legitimate forms such as acronyms and brand names.

Find duplicates without deleting valid records

First define the fields that make a record unique. An order table may use an order ID; a contact list might use an email address, or a combination of name and postal code if email is not reliable. Two rows with the same person’s name are not necessarily duplicates, and two rows with the same ID may still need investigation if other fields differ.

To remove exact duplicates from a copy of the data, select a cell in the range and choose Data > Remove Duplicates. Confirm that My data has headers is selected when the first row contains field names. Select the columns Excel should compare: selecting every column finds rows that match across all selected fields, while selecting only an ID treats matching IDs as duplicates even if other fields differ. Excel keeps one matching record and removes the others, so check the result and retain the source copy.

When uncertain, filter or highlight candidates and compare them before removal. A duplicate ID paired with different dates or amounts can indicate a data-entry issue rather than a redundant row. Removing duplicates is not the same as finding near-duplicates: “Jon Smith” and “John Smith” will not be treated as identical by the standard command.

Handle blanks, numbers, and dates carefully

A blank cell can mean “unknown,” “not applicable,” or “not entered yet.” Those meanings are not interchangeable. Filter a column for blanks and decide what each blank means before filling or deleting it. Do not replace all blanks with zero: in a quantity or revenue field, zero is a real value and can affect totals and averages.

To inspect blank cells, use the filter dropdown on the relevant column and select (Blanks). For wholly empty rows, check that every field in the row is empty before deleting it. A row with a missing email but a valid customer ID is incomplete, not an empty row. Filling blanks with labels such as “Unknown” may help readability, but it changes the data and can affect analysis; document that choice.

Numbers imported as text can sort incorrectly or fail to calculate. A green error indicator may offer Convert to Number; review the selection before accepting. You can also use a helper formula. With a text-form number such as 125 in B2, enter =VALUE(B2) in C2; the result is the numeric value 125. This conversion assumes the text uses number separators recognized by your Excel regional settings. Check currency symbols, decimal marks, and thousands separators before converting a whole column.

Dates deserve the same caution because a displayed date can be either a true Excel date value or text that only looks like one. Select a suspicious cell and inspect the formula bar and number format. If a column contains text dates, Data > Text to Columns can convert them: choose Delimited, proceed through the wizard without selecting a delimiter, then choose Date and the source order, such as MDY or DMY, before finishing. Choose the order that matches the source; interpreting 04/05/2025 as April 5 rather than May 4 can change the meaning. Check a few converted dates and sort the column to confirm that it behaves chronologically.

Make formatting and structure consistent

Formatting should make the data readable without hiding its meaning. Apply one date format to a date column, an appropriate number format to amounts, and consistent labels to headers. Number formatting changes how a value is displayed, not necessarily the underlying value. For example, changing a cell to Currency does not convert text such as “$25” into a number by itself.

Keep one type of information per column. If a cell contains “Seattle, WA,” split it into city and state only if later sorting or analysis requires separate fields. Avoid merged cells in the data area: they interfere with sorting, filtering, and many analysis tools. Keep notes, subtotals, and explanatory text outside the rectangular data range, or store them in clearly identified columns.

After sorting, verify that the entire table moved together. Sorting just one column can detach values from their related records. Select a cell inside the table and use the sort controls on the Data tab so Excel can recognize the data range, then confirm the prompt to expand the selection if it appears.

Use Power Query for repeatable cleanup

If you receive the same kind of file repeatedly, Power Query can make cleanup steps repeatable. In Excel for Microsoft 365 on Windows, select a cell in the source range or table and choose Data > From Table/Range. In the Power Query Editor, common transformations include removing blank rows, trimming or cleaning text, changing data types, filtering rows, and removing duplicates. Choose Home > Close & Load to return the transformed results to Excel.

Power Query records transformations as steps, so an updated source can often be processed again with Data > Refresh All. It is useful for recurring imports, but it does not make judgment calls for you: choose the right duplicate key, date locale, and treatment of missing values. Keep the original source and inspect the query output, especially after the source file changes. Power Query features and menu placement can differ by Excel platform and update; Mac availability is more limited than Windows in some builds, so check the commands present in your version.

Validate the cleaned workbook and prevent repeat issues

Do a final check before using the workbook for decisions or sharing it. Compare row counts before and after cleanup, confirm that expected IDs remain, and review totals such as the sum of a sales column. Check a few records against the source file, then sort and filter the cleaned table to see whether values behave as expected. If rows were removed, keep a note of the criteria and number removed.

Prevention is easier than repeated repair. Use a consistent template with clear headers, one record per row, and one value per field. Where appropriate, use Data > Data Validation to limit entries to a list or an allowed range. Set a column’s format before importing or entering data, and ask data suppliers to provide dates and numeric fields consistently. For recurring exports, keep a separate raw-data sheet and use a documented cleanup process rather than editing the incoming records without a trace.

Frequently Asked Questions

How do I clean an Excel spreadsheet quickly?
Save a copy, turn on filters, inspect blanks and inconsistent values, standardize formats, and check for duplicates using the correct key. Verify row counts and important totals before relying on the result.

Does Remove Duplicates delete the whole row?
Yes. It removes duplicate records from the selected range based on the columns you choose, retaining one instance. Work on a copy and review which columns define a duplicate before running it.

How do I remove extra spaces in Excel?
For ordinary extra spaces, use TRIM in a helper column and fill the formula down. Pasted nonbreaking spaces may need to be replaced first; the SUBSTITUTE and CHAR approach above handles that common case.

Why are my Excel dates sorting incorrectly?
The cells may contain text rather than Excel date values, or the dates may have been interpreted using the wrong day-month order. Confirm the source convention, convert carefully, and test the sort against known dates.

Can I clean data without changing the original?
Yes. Save a separate workbook copy, keep a raw-data sheet, or use Power Query to load cleaned results separately. Those approaches make it easier to compare the cleaned output with 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.