Data Cleaning in Excel: Practical Techniques That Work

Clean data in Excel by first making a copy of the source, inspecting the columns, and then using tools such as Remove Duplicates, Text to Columns, Find and Replace, and formulas like TRIM to correct specific problems. For recurring imports, Power Query can turn those steps into a refreshable process instead of requiring you to repeat them manually.

Start with a safe, structured workflow

Cleaning is not simply making a worksheet look tidy. It means making values consistent and reliable enough for sorting, calculations, charts, and analysis. A date stored as text, for example, may look correct on screen but fail to group or calculate like a real Excel date. Two customer names that differ only by an extra space can also be treated as separate categories.

Before changing anything, save a separate copy of the workbook or duplicate the raw-data sheet. Keep one column per field, such as customer, date, and amount, and one record per row. Give each column a clear header, and avoid merged cells or subtotal rows inside the data table; they make filtering and repeatable processing harder.

Identify the intended meaning and format of each column.
Check for missing values, unexpected formats, duplicates, and spelling variations.
Apply changes to a copy or a clearly marked cleaning column.
Compare the cleaned result with the original and verify row counts and totals.
Document rules that affect meaning, such as which duplicate record to retain.

For Microsoft Excel for Microsoft 365 on Windows, most of the ribbon paths below are available directly. Excel for Mac and Excel for the web have many of the same functions, but ribbon placement, keyboard shortcuts, and some Power Query capabilities can differ by version. If a command is not visible, check the equivalent tab or the application’s search box.

Inspect the data before editing

Begin by turning the range into an Excel table: select a cell in the data and choose Insert > Table, then confirm whether the first row contains headers. Tables add filter controls, expand as data is added, and make it easier to see whether a column contains unexpected values. Filters can expose blank entries, unusual categories, or values that do not belong in a field.

Sort and filter with care. Sorting only one column can detach values from their related records if Excel does not expand the selection. Select the whole table or use its filter controls so each row remains together. You can also use Home > Find & Select > Go To Special to inspect blanks or formulas, but first consider whether blank cells are genuinely errors: in some datasets, a blank means unknown, not applicable, or not yet recorded.

Look for issues that are easy to miss visually:

Numbers or dates aligned as text, or a mixture of text and numeric values in one column.
Leading or trailing spaces, line breaks, nonbreaking spaces, and inconsistent capitalization.
Repeated records, including records that match on some fields but not others.
Different labels for the same thing, such as “NY,” “N.Y.,” and “New York.”
Unexpected outliers, zeros, negative amounts, or values outside the field’s allowed range.

Conditional formatting can make repeated values easier to review. With the values to check in A2:A100, select that range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values, but it does not decide which row is correct or remove anything. Treat it as an inspection aid, not a substitute for a retention rule.

Remove duplicates without losing valid records

Excel’s Remove Duplicates command permanently removes rows from the selected data range, so work on a copy first. Select a cell in the table and choose Data > Remove Duplicates. In the dialog, choose the columns that define a duplicate. If every column is selected, Excel removes rows that match across all those columns; if only an ID column is selected, rows with the same ID are treated as duplicates even if other fields differ.

That distinction matters. Two purchases by the same customer are not duplicates if the records represent separate transactions. Conversely, two customer records with the same email address may be duplicates even if their capitalization or spacing differs. Decide what makes a record unique before running the command, and inspect the rows Excel reports as removed. The command keeps one instance of a duplicate record; it is not a tool for choosing the newest, most complete, or otherwise preferred record automatically.

To flag repeated values without removing them, use conditional formatting or a helper formula. With names in A2:A100, enter this formula in B2 and fill it down:

With the names to check in A2:A100, this formula returns TRUE for any name that appears more than once in the range:

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

For a name appearing twice, both matching rows return TRUE. This is useful for review, but it treats capitalization as equivalent and may treat values with different hidden spaces as distinct. Normalize the text first if those differences should not matter. Remove Duplicates and formulas do not resolve near-matches such as “Jon Smith” and “John Smith”; those require a defined business rule or a more specialized matching process.

Standardize spaces, characters, and text

TRIM is a common first step for inconsistent spacing. It removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. It does not remove every invisible character, and it does not change capitalization. With a source name in A2, enter this in B2:

With the source text in A2, the formula returns “Ana Lee” when A2 contains “ Ana Lee ”:

=TRIM(A2)

Data copied from websites or other systems may contain nonbreaking spaces, which TRIM does not handle as ordinary spaces. If A2 contains “North” followed by a nonbreaking space, use this formula to replace that character with a normal space before trimming:

With the imported text in A2, the formula returns “North” when the only extra character is a nonbreaking space at the end:

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

For nonprinting characters such as some imported line breaks, CLEAN can help. With text in A2, this formula removes characters that CLEAN recognizes and then trims ordinary spaces:

=TRIM(CLEAN(A2))

CLEAN is not a universal Unicode scrubber; it will not remove every unusual character from every source. Inspect suspicious values if the result still does not match. For capitalization, PROPER changes the first letter of each word to uppercase and the rest to lowercase, while UPPER and LOWER standardize all letters. These transformations can damage names, acronyms, product codes, and intentional mixed-case labels, so use them only when the target convention is appropriate.

For consistent labels, Find and Replace is often safer than broad formula changes. Choose Home > Find & Select > Replace, enter the exact old and new labels, and review the scope before choosing Replace All. Use filters to find spelling variants first; a replacement that is too broad can alter valid text, such as replacing a short abbreviation that appears inside longer words.

Fix dates and numbers stored in the wrong form

A value that looks like a date may be text rather than an Excel date serial. One clue is that changing its number format has no effect; another is that date arithmetic or chronological sorting behaves unexpectedly. First consider the source format and regional settings. Text such as 03/04/2025 is ambiguous: it can mean March 4 or April 3, depending on the convention used by the source. Do not convert ambiguous dates until you know which interpretation is correct.

For consistently structured text dates, select the column and choose Data > Text to Columns. Proceed through the wizard, choose the appropriate column data format on the final step, and select the date order that matches the source, such as MDY or DMY. Check a sample before converting the full column. When dates use a consistent delimiter, Text to Columns can also split a combined field into separate columns; make sure the destination cells are empty so existing data is not overwritten.

Numbers imported as text may contain currency symbols, thousands separators, or spaces. Excel’s conversion behavior depends on the characters and regional settings, so test a small sample. Text to Columns can convert some text-number values without splitting them if you choose Finish with the default delimiter settings, but inspect results afterward. Avoid multiplying a whole column by 1 or using broad replacements until you know whether the text includes meaningful symbols or codes. Preserve leading zeros in identifiers such as postal codes and account numbers; those are labels, not quantities to calculate.

Handle blanks, errors, and inconsistent categories

Blank cells need a policy, not an automatic fill. A missing quantity might need investigation, while a blank middle-name field may be acceptable. Filtering a table by blanks helps locate omissions. If a numeric field should never be empty, compare the missing rows with the original source rather than filling them with zero: zero is a real value and can change totals and averages.

With values in A2:A100, this formula counts truly empty cells in that range:

=COUNTBLANK(A2:A100)

The result is the number of blanks, including cells containing formulas that return an empty string. That behavior is useful to know when a sheet uses formulas to display blank-looking results. Excel errors such as #N/A or #VALUE! should also be interpreted rather than hidden. Find the cause where possible; for example, #N/A may indicate a missing lookup match, while #VALUE! can indicate an incompatible input.

Categories often need a controlled vocabulary. Filtering a column and reviewing its unique-looking entries can reveal inconsistent abbreviations, punctuation, or spelling. Make a small mapping list that states which source labels become which approved labels, then apply replacements deliberately. For ongoing data entry, use Data > Data Validation to restrict entries to a list or a suitable range. Validation reduces future inconsistencies, but it does not repair old records and can sometimes be bypassed by pasting data.

Use Power Query for repeatable cleaning

When the same cleanup is required every week or month, Power Query is usually more dependable than repeating a long chain of manual edits. In Microsoft 365 for Windows, select a cell in a well-formed range and choose Data > From Table/Range. In the Power Query Editor, steps can include removing columns or rows, filtering, splitting columns, replacing values, changing data types, and trimming or cleaning text. The exact controls depend on the selected column and the Excel build.

Power Query records transformations as steps. When the next source file follows the same general structure, refresh the query to apply those steps again. This makes the process auditable: if a result is wrong, review or edit the step rather than trying to remember a series of manual actions. Choose Home > Close & Load to return the result to Excel. Keep the source data and query output distinct so you can compare them and avoid accidentally editing the generated results as though they were the source.

Queries can fail or produce unexpected output when source column names change, a file moves, data types differ, or a new format appears. Refresh after changes and check row counts, column types, and totals. Power Query availability and supported sources differ across Excel for Mac and Excel for the web; Microsoft 365 Windows offers the broadest set of desktop controls described here. If a feature is missing on another platform, check the current version’s Data tab and Microsoft’s documentation rather than assuming every Windows option is present.

Choose the right technique and verify the result

Use a built-in command for a one-time, well-defined change; use a formula when you want a visible, reviewable transformation beside the source; and use Power Query when the process must be repeated on similar inputs. For example, Remove Duplicates is quick after you decide which columns define a duplicate. TRIM in a helper column makes text changes easy to inspect before replacing the original values. Power Query is a better fit when several imports need the same sequence of cleanup steps.

After cleaning, check more than appearance. Compare the number of records before and after, confirm that important IDs remain present, and reconcile totals for numeric fields. Spot-check records from the beginning, middle, and end of the data. Sort dates chronologically and verify that values behave as dates; test representative formulas; and search for known problem strings. If removing duplicates reduces the row count, record how many were removed and why the chosen key was appropriate.

Keep the original data when the cleaning decisions could be questioned later. A reproducible transformation with documented rules is especially important for financial, health, compliance, or operational records. Excel can help detect and standardize data, but it cannot decide whether two similar records refer to the same real-world entity or whether a missing value should be inferred. Those decisions require context from the data owner.

Frequently Asked Questions

What is the fastest way to clean data in Excel?
For a small one-time dataset, convert it to a table, inspect it with filters, then use targeted tools such as Remove Duplicates, Text to Columns, and Find and Replace. There is no safe universal one-click cleanup because the right treatment depends on what each column means.

Does TRIM remove all extra spaces in Excel?
TRIM handles ordinary spaces at the beginning and end of text and reduces repeated ordinary spaces between words. It does not remove every special or nonbreaking space. SUBSTITUTE with CHAR(160) can address a common nonbreaking-space case, while CLEAN can remove some nonprinting characters.

How do I remove duplicates but keep the original data?
Copy the worksheet or source range first, then use Data > Remove Duplicates on the copy. Alternatively, use conditional formatting or a COUNTIF helper formula to flag duplicates for review without deleting rows.

Why does Excel not recognize my dates?
The cells may contain text, or the date order may conflict with regional settings. Confirm the source convention before converting, especially for ambiguous values such as 03/04/2025. Text to Columns can interpret a consistent text-date column when you select the correct date order.

Should I replace blank cells with zero?
Only if zero is the correct meaning for that field. A blank can mean unknown, not applicable, or not yet entered; converting it to zero changes the data and can distort calculations. Check the data definition or source before filling blanks.

Is Power Query better than formulas for cleaning?
It depends on the task. Formulas are transparent and convenient for a few columns or one-off checks. Power Query is useful for a repeatable sequence of transformations and refreshes, especially when new data arrives in a similar structure.

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.