Data Cleaning Steps in Excel: A Practical Guide

To clean data in Excel, first make a copy of the original, then inspect the columns, standardize formats and text, correct blanks and errors, check for duplicates, and validate the results before using the data. For a repeatable process, use Power Query; for a one-time cleanup, Excel’s built-in tools and formulas are often sufficient.

Why clean data before analyzing it?

Small inconsistencies can change totals, split one category into several, or make records impossible to match. For example, a report may treat “North,” “north,” and “North ” as three separate labels. A date stored as text may not sort chronologically, and a number stored as text may be excluded from a calculation. Cleaning is the work of finding and resolving these inconsistencies while preserving the meaning of the original records.

There is no single correct cleanup rule for every dataset. A blank in a customer email column might mean “not provided,” while a blank in an order quantity column could indicate a missing value that needs investigation. Decide what each field should contain before changing it. Keep a record of important choices, especially if someone else will rely on the results.

1. Preserve the source and inspect the data

Before making changes, save a separate copy of the workbook or duplicate the source worksheet. Give the cleaned output a clear name and retain the untouched data. This makes it possible to compare results, recover a value, or repeat the cleanup if an assumption changes.

Look over the column headings, a sample of records, and the bottom and right edges of the dataset. Check whether each column has one purpose, such as customer ID, date, or amount. Mixed-purpose columns—such as a single field containing both city and postal code—are harder to sort and analyze. Also look for title rows, subtotals, notes, merged cells, and completely empty rows inside the data. These can interfere with sorting, filtering, PivotTables, or imports.

Convert a straightforward range into an Excel table with Insert > Table, or press Ctrl+T on Windows. Confirm whether the range has headers. On Mac, use Insert > Table; the shortcut can vary by keyboard and Excel version.
Turn on filters with Data > Filter if the range is not already a table. Filtering makes it easier to inspect individual categories, blanks, and unusual values.
Check that one row represents one record and that the header row contains distinct, useful field names.

2. Find blanks and decide what they mean

Use the filter arrow on a column and select (Blanks) to inspect missing entries. In a numeric column, a blank is not necessarily the same as zero: zero is a recorded value, while a blank may mean unknown, not applicable, or not collected. Do not fill all blanks with zero simply to make a calculation work. First determine what the field means and whether a reliable source can supply the missing value.

For a small range, Find & Select > Go To Special > Blanks selects empty cells in the selected area. Be careful before typing a value after selecting multiple blanks: pressing Ctrl+Enter fills the selected cells with the entry, which may be inappropriate if the blanks have different meanings. In a table, filters are often safer because they show the surrounding records for review.

Remove fully empty rows only after confirming that they contain no meaningful information and are inside the dataset. Do not mistake a row with one missing field for an entirely empty record. If missing values must be excluded from a particular analysis, document that decision rather than silently deleting the records.

3. Remove extra spaces and invisible characters

Inconsistent spaces are a common reason text comparisons fail. Home > Find & Select > Replace can replace a known unwanted character or repeated text, but ordinary spaces are not the only issue. Data copied from websites or other systems may contain nonbreaking spaces or control characters that are not obvious on screen.

With the text to clean in A2, this formula replaces nonbreaking spaces with ordinary spaces, removes many nonprinting control characters, and trims leading and trailing spaces while reducing repeated ordinary spaces to one. For example, if A2 contains a leading space, “Maya,” a nonbreaking space, and “Chen” followed by a trailing space, the result is “Maya Chen.” Enter it in another column and fill it down:

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

TRIM and CLEAN have limits: CLEAN removes certain nonprinting characters, not every invisible Unicode character, and TRIM does not remove every kind of whitespace by itself. Inspect unusual records if the result still does not match. When the cleaned text is correct, copy the results and use Home > Paste > Paste Values to replace formulas with their current results, if that is what the workflow requires. Keep the original column until you have checked the output.

4. Standardize spelling, capitalization, and categories

Decide on a consistent format for names, regions, product labels, and status fields. A category such as “In progress” should not also appear as “In Progress,” “in progress,” and “Progress” unless those values genuinely mean different things. Filters can reveal variations, and Data > Sort can group similar entries for review. Replace values only when you know they are equivalent; similar-looking labels may represent distinct products or locations.

For a list with a small number of known variations, Find & Select > Replace can make a controlled correction. For example, replacing “N. Region” with “North Region” is sensible only if the abbreviation has that meaning throughout the relevant field. For longer lists, create a separate mapping table that pairs each source label with its approved label, then apply the mapping in a documented step. That is easier to review than many unrecorded manual edits.

Capitalization is mainly a display and consistency choice, not a substitute for checking identity. Excel’s PROPER function can capitalize words, but it may produce unwanted results for acronyms, brand names, or names with unusual capitalization. Review transformations such as this against the source instead of applying them blindly.

5. Correct numbers and dates stored in the wrong format

Excel distinguishes a value’s underlying content from how the cell displays it. A date may be a valid date serial shown in an unexpected format, or it may be text that only looks like a date. Similarly, an amount may be text because it includes an imported symbol or a separator that Excel did not recognize. Check alignment, number formats, and whether calculations behave as expected, but do not rely on appearance alone.

For text that follows a consistent pattern, select the affected column and use Data > Text to Columns. Choose the appropriate delimiter or fixed-width option, and, for date text, select the matching date order in the final step. This can split combined fields or convert certain text values. Test the process on a copy or a few sample rows first; choosing the wrong date order can turn a day-month-year value into a different date or leave it incorrectly interpreted.

Regional settings matter. For instance, 04/05/2025 can mean April 5 or May 4 depending on the source convention. Confirm the source system’s date order before converting. For product codes such as 00127, preserve leading zeros by treating the field as text; converting it to a number changes its meaning and displays 127. If importing a file, set the column’s type appropriately during import where possible.

6. Check and remove duplicates carefully

Duplicates can be exact copies, or they can be records that share an ID while differing in other fields. Decide what counts as a duplicate before deleting anything. A repeated customer name alone may be legitimate when the same person has multiple orders; a repeated unique order ID may signal a problem.

For a one-time removal, select the dataset or click inside its table, then choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate. Selecting every column removes rows only when all selected values match; selecting just an ID treats matching IDs as duplicates even if other fields differ. Excel keeps the first occurrence it finds and removes later matching rows, so sort or review the records first if you need to preserve a particular version. The command changes the data; use the saved original to recover removed records if needed.

After removal, compare the row count before and after and review the records that remain. Do not use Remove Duplicates as a substitute for resolving conflicting values. If two records have the same key but different addresses, for example, decide which value is authoritative rather than letting row order make the decision.

7. Validate values and investigate errors

Data validation can prevent some future entry mistakes. Select the cells and choose Data > Data Validation to set rules such as a whole-number range, a date interval, or a list of permitted values. A drop-down list is useful for controlled categories such as status or department. Validation is not a guarantee that every existing value is correct, and pasted data can require separate review, so check current records as well.

Review formula errors such as #N/A, #VALUE!, and #DIV/0! in the context of the calculation. An error may indicate a lookup that found no match, text where a number was expected, or division by zero. Replacing every error with a blank can hide a real issue. Correct the cause where possible, and use an error-handling formula only when the alternative output has a clear meaning.

For a final check, filter each important column for unexpected values, sort numeric and date columns to look for outliers, and compare summary totals with a trusted source when available. A value that is unusual is not automatically wrong; it is a prompt to verify it.

8. Use Power Query for repeatable cleanups

If the same cleanup must be repeated for monthly files or recurring exports, Power Query can make the steps more consistent. In Excel for Microsoft 365 on Windows, use Data > Get Data to connect to a source, or choose a suitable option under Data > From Text/CSV or From Table/Range. In the Power Query Editor, you can change data types, remove rows, split columns, replace values, trim text, and filter records. Each transformation appears as an applied step that can be inspected and revised.

When the source is updated, refresh the query to apply the recorded transformations again. This is useful for recurring work because it reduces repeated manual editing, but it does not decide whether a business rule is correct. For example, Power Query can standardize a known spelling or convert a column to a date type; a person still needs to confirm the correct date convention and which records should be excluded.

Power Query is available in Excel for Mac with capabilities that depend on the Excel version and data source, and its connectors and editing features can differ from Windows. Excel for the web does not offer the full desktop Power Query authoring experience. If a workbook will be shared across platforms, check that the intended users can refresh its queries and access the source.

9. Verify, document, and protect the cleaned result

Before using cleaned data in a report, confirm that the number of records is plausible, required fields are populated, categories are consistent, and totals or sample records agree with the source. Check formulas after sorting or filtering, and make sure a cleanup did not remove a meaningful leading zero, change a date, or merge distinct categories. If the data feeds a PivotTable or chart, refresh it after the underlying records change.

Keep the original and cleaned versions clearly labeled, and note important assumptions such as date order, duplicate rules, and treatment of blanks. For manual edits, a brief change log can explain what was changed and why. For a recurring process, retain the Power Query steps or other documented procedure. Save the workbook before major operations; Ctrl+Z on Windows or Command+Z on Mac can undo many recent actions, but it is not a replacement for a backup, particularly after closing or refreshing a workflow.

Frequently Asked Questions

What is the first step in cleaning data in Excel?

Save a copy of the original data, then inspect the columns and a sample of records. Understanding what each field should contain helps prevent accidental changes, such as treating a missing value as zero or removing a legitimate repeated customer.

How do I remove duplicate rows in Excel?

Select the data and choose Data > Remove Duplicates. Choose the columns that define a duplicate, because selecting only an ID produces a different result from selecting every column. Keep a backup and review which record Excel retains.

How do I remove extra spaces from cells?

For ordinary text, TRIM can remove leading and trailing spaces and reduce repeated spaces. Imported nonbreaking spaces may need to be replaced first; the formula above uses SUBSTITUTE with CHAR(160) for that common case. Check the results before replacing source values.

How can I tell whether a date is text or a real Excel date?

Try sorting the column and using a date-based calculation or number format on a copy of the value. A real Excel date is stored as a number with a date display format; text may not sort or calculate as a date. Confirm the source’s date order before converting ambiguous entries.

Should blank cells be replaced with zero?

Only when zero is the correct meaning for that field. A blank may mean unknown or not applicable, which is different from a measured zero. Establish the meaning of missing values before filling, deleting, or excluding them.

Is Power Query better than cleaning data manually?

For a one-off, small correction, manual tools may be faster. For recurring imports or many repeatable transformations, Power Query can record and refresh the steps. In either case, verify the rules and output; automation repeats a chosen process but cannot confirm that its assumptions are right.

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.