A practical data-cleaning example in Excel is to standardize names and email addresses, check dates and missing values, then identify duplicate records before using the data. In Microsoft Excel for Microsoft 365 on Windows, you can do this with a preserved copy of the raw data, a few formulas, and Excel’s built-in duplicate-removal tools.
The example: a small customer order list
Suppose a worksheet contains five columns: Order ID in column A, Customer in B, Email in C, Order date in D, and Amount in E. The records include entries such as a customer name with extra spaces, an email with inconsistent capitalization, a blank email, and two rows with the same order ID and otherwise identical details. These are common issues in exported reports and manually maintained lists.
Start by keeping the original data intact. Add a new worksheet named Raw, or make a copy of the workbook with File > Save As. On a separate working sheet, copy the records and add columns for cleaned name and email. This lets you compare each result with its source and recover the original value if a correction is wrong. If the range has headers, select it and choose Insert > Table; confirm that My table has headers is selected. A table expands as rows are added and makes filters and column references easier to manage.
Step 1: standardize spaces and text
Leading spaces, trailing spaces, repeated ordinary spaces, and nonbreaking spaces can make text look identical while Excel treats it as different. A nonbreaking space often arrives when data is copied from a web page. CLEAN removes many nonprinting characters, while SUBSTITUTE replaces the nonbreaking space represented by CHAR(160) with an ordinary space. TRIM then removes leading and trailing spaces and reduces repeated ordinary spaces to one.
Assuming customer names are in B2:B6 and the cleaned-name output starts in F2, enter this in F2 and fill it down: =PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))). For an input such as a name with extra spaces and lowercase letters, the result is Maria Lopez. PROPER capitalizes the first letter of each word, but it is not suitable for every name or organization: it may change intended capitalization in names such as McDonald, O’NEIL, or a brand acronym. Inspect those cases and correct them deliberately rather than treating automatic capitalization as authoritative.
Assuming emails are in C2:C6 and the cleaned-email output starts in G2, enter this in G2 and fill it down: =LOWER(TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160)," ")))). An address such as M.LOPEZ@EXAMPLE.COM with surrounding spaces becomes m.lopez@example.com. Lowercasing is useful for consistent comparison, although email-address rules can technically distinguish case in some contexts; follow the conventions of the system that will receive the cleaned file. These formulas clean text, but they cannot determine whether a name or address is factually correct.
Step 2: check dates and numbers
Dates can be stored as real Excel date values or as text that merely looks like a date. Select a date cell and check the formula bar or change the display format temporarily: a real date generally responds to date formatting, while text may not. A date such as 03/04/2026 is ambiguous because it can mean March 4 or April 3 depending on the source’s regional convention. Do not guess based on appearance when the distinction matters.
For a small dataset, inspect questionable values against the source report. If a whole column uses a consistent text format, select the column and use Data > Text to Columns, choose the appropriate delimiter settings, and set the column data format to Date with the correct order where needed. Confirm the preview and resulting dates before proceeding. Excel’s interpretation can depend on Windows regional settings, so verify a few known examples. For repeatable imports or larger files, Power Query can be useful: select a cell in the table and choose Data > From Table/Range, then set the date column’s data type in the editor and check the result before loading it.
For amounts, check whether values are numeric rather than text. Applying a currency format changes how numbers display; it does not turn text into numbers by itself. Sort or filter the amount column and look for unexpected blanks, symbols embedded in the value, or entries that do not match the source. Keep a clear distinction between a genuinely missing amount and a value of zero because they mean different things in most analyses.
Step 3: decide how to handle blanks
A blank is not automatically an error. An empty email field may be acceptable, while a missing order ID could prevent a reliable match. Define which columns are required for the task, then filter those columns for blanks and review the affected rows. On Windows, select the header row or table and choose Data > Filter, then use the column’s filter menu to show blank values. Do not delete every row with any blank: doing so can discard valid records whose optional fields were never collected.
For each missing value, use a documented rule. You might recover it from the original system, leave it blank, or label it as unknown if that convention is accepted by the receiving system. Avoid filling blanks with zero, a guessed date, or a repeated value simply to make the sheet look complete. If you insert a placeholder such as Not provided, check that downstream formulas, imports, or reports expect text rather than an empty cell. Keep a note of how many records were changed and why.
Step 4: find duplicates without deleting valid records
Two rows that share a customer name are not necessarily duplicate orders. In the example, Maria Lopez can have two different order IDs and both records should remain. Decide what makes a row a duplicate before removing anything. If Order ID uniquely identifies one order, check duplicates in column A; if a record is defined by several fields, compare those fields together and investigate exact matches against the source.
Assuming order IDs are in A2:A6 and a duplicate-check column starts in H2, enter this in H2 and fill down: =COUNTIF($A$2:$A$6,A2)>1. It returns TRUE for every row whose ID appears more than once in that range, including both occurrences of a repeated ID. In this five-row example, if 1003 appears twice and the other IDs appear once, the two rows with 1003 return TRUE and the others return FALSE. A TRUE result flags a review; it does not prove which row should be deleted.
You can also select the ID cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight repeated IDs visually. To remove confirmed duplicates, first save a copy. Select the full table, then choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate. Selecting only Order ID is appropriate only if IDs must be unique; selecting every column removes only rows identical across all selected fields. Excel keeps one occurrence and removes the others, so verify the row count and sample records afterward.
Step 5: validate the cleaned result
Cleaning is not complete just because the worksheet looks neater. Compare the cleaned table with the source and confirm that the changes fit the rules you chose. Useful checks include reviewing row counts before and after duplicate removal, checking that required IDs are present, confirming that dates fall within the expected period, and comparing a few amounts to the original report. Filter the cleaned columns for blanks and unusual values again.
Keep a short change log, even for a small workbook. Record the number of rows imported, which fields were standardized, the duplicate rule used, how many rows were removed, and any records that need follow-up. This helps someone else understand why the cleaned data differs from the source. If you are preparing a recurring report, save the rules and steps with the workbook or document them beside the data rather than relying on memory.
Common mistakes and practical limits
- Overwriting the source: Cleaning in place removes the easiest way to compare values. Keep a raw copy and use separate output columns until the result is verified.
- Deleting by appearance: Similar names or matching dates do not establish that two transactions are duplicates. Choose a key based on the meaning of the records.
- Trusting automatic fixes: PROPER, date conversion, and trimming can standardize formatting, but they cannot verify identity, resolve ambiguous dates, or know the intended spelling.
- Ignoring formulas and downstream use: A cleaned value may differ from a value used by an existing formula or linked report. Check formulas, filters, charts, and any import requirements before replacing the original fields.
- Assuming a display format changes data: Formatting a text date or amount does not necessarily convert it to a real date or number. Confirm the stored value and test calculations or sorting.
Excel is effective for a manageable list and a transparent set of rules, but it does not decide what counts as correct data. For repeated, larger imports, Power Query can record transformation steps so they can be refreshed. For a one-off list, formulas and careful review may be simpler. In either case, preserve the raw input and validate the output before using it for decisions or sharing it.
Frequently Asked Questions
What is a simple data-cleaning example in Excel?
Copy a customer list to a working sheet, use TRIM and CLEAN to standardize text, check required fields, review duplicate IDs, and verify the resulting rows against the source. Keep the original data unchanged until the checks pass.
How do I remove extra spaces in Excel?
For ordinary spaces, TRIM removes leading and trailing spaces and reduces repeated spaces between words. If copied text may contain nonbreaking spaces, replace CHAR(160) with an ordinary space using SUBSTITUTE before applying TRIM, as in the example above.
Does Remove Duplicates delete every matching row?
No. Excel keeps one occurrence of each duplicate according to the columns selected in the Remove Duplicates dialog and deletes the other occurrences. The selected columns define what counts as a match, so select the full record or a unique key as appropriate and save a copy first.
How can I tell whether an Excel date is text?
Change the cell’s number format or use it in a simple date-based sort or calculation, then compare the result with a known source value. If conversion is needed, use the correct regional date order and verify the outcome; ambiguous values should be checked against their source rather than guessed.
Can Excel clean data automatically?
Excel can apply repeatable transformations through formulas and Power Query, but it cannot reliably decide whether a misspelled name, missing value, or similar-looking transaction is correct. Automate consistent formatting rules, then review exceptions and validate the output.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set Out of Office in Outlook on Your DevicesTo set an out-of-office message in Outlook, open Automatic Replies, switch them on, write your message, and—if you…
- Can You Group Rows in Excel? Steps and TipsYes. In Excel for Microsoft 365 on Windows, select the detail rows you want to fold away, then…
- How to Create and Share a Calendar in Microsoft OutlookTo create a calendar you can share, add a separate calendar in Outlook, then use its sharing settings…
- Excel Date Origin: 1900 vs. 1904 SystemsExcel’s default date origin on Windows is the 1900 date system: January 1, 1900 is serial number 1,…
- Conditional Formatting Based on Value in Excel: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
- Export Phone Contacts to Outlook on iPhone and AndroidTo export phone contacts to Outlook, save them from your iPhone or Android phone as a contact file,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.