To clean raw data in Excel, first save an untouched copy, then inspect the columns, standardize text and dates, handle blanks and duplicates deliberately, and check the results before using the data. This workflow helps make a worksheet consistent and usable for sorting, filtering, formulas, and reports without accidentally changing meaningful values.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The steps use the standard ribbon; some Power Query options and menu labels can differ on Mac, and Excel for the web may not offer every transformation.
- A copy of the workbook or a duplicate sheet to preserve the original data.
- A clear idea of what each column should contain, including accepted date formats, valid categories, and whether repeated records are allowed.
Step-by-Step
- Make a safe working copy. Use File > Save As to save a separate copy before making changes. If you are working in a workbook that must retain its original data, duplicate the raw-data sheet: right-click its sheet tab, choose Move or Copy, select Create a copy, and click OK. Rename the original sheet something like Raw – do not edit and work on the duplicate. This gives you a reliable reference if a cleanup step removes or changes the wrong values. Keep a note of any assumptions you make, such as treating blank status cells as “Unknown.”
- Check the layout and identify the rules. Look for a single header row, one record per row, and one type of information per column. Remove only completely empty rows or columns that are clearly outside the dataset; do not delete blank cells inside the data just to make the sheet look compact. Check for merged cells, title rows above the headers, totals mixed in with records, and notes below the table. Decide what a valid value looks like in each field. For example, a postal code should be treated as an identifier, not a number, and a customer table may legitimately include several purchases for the same person.
- Turn the data into a table and inspect it. Click inside the data and choose Insert > Table, confirm the range, and select My table has headers if the first row contains field names. A table makes filtering and sorting safer because the headers stay attached to their records, and newly added rows are included automatically. Use each header’s filter arrow to scan for blanks, unusual spellings, and unexpected values. Sort one column at a time and check that entire rows move together. If Excel guesses the wrong range, cancel and select the intended range before creating the table.
- Standardize text carefully. Look for leading or trailing spaces, nonbreaking spaces copied from websites, line breaks, inconsistent capitalization, and alternate spellings such as “NY,” “N.Y.,” and “New York.” For a small number of consistent errors, use Home > Find & Select > Replace and review the replacement before choosing Replace All. For messy spaces or hidden characters, use a helper column rather than overwriting the source immediately. With the text to clean in A2, this formula removes common nonprinting characters and converts nonbreaking spaces before trimming extra spaces:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). If A2 contains extra spaces around “North Bay” and a nonbreaking space between the words, the cleaned result is North Bay. Fill the formula down, inspect the results, then copy the cleaned column and use Paste Special > Values if you want to replace the original text. - Check numbers, identifiers, and dates. A value that looks numeric may be stored as text, while identifiers such as ZIP codes, product codes, and account numbers may need to stay as text to preserve leading zeros. Do not convert identifiers simply because Excel offers to. To convert text-formatted numbers, select the column and use the warning icon if Excel displays one, or use Data > Text to Columns and finish the wizard with the appropriate settings. For dates, confirm the day/month order before converting; a value such as 03/04/2025 is ambiguous across regions. Text to Columns can convert dates when you choose the correct date order in the wizard. Check several converted values against the source and use a consistent display format from Home > Number before proceeding.
- Decide what to do with blanks. Filter each important column for blank cells and determine whether the missing value is an error, an unavailable answer, or a legitimate absence. Do not fill every blank with zero: zero means something different from “not recorded.” If the correct value can be verified from a trusted source, fill it and record that decision. If it is unknown, leave it blank or use an agreed label such as “Not provided,” as appropriate for the dataset. If a row is entirely empty, it can usually be removed; if only one field is blank, retain the record unless your data rules say otherwise.
- Find duplicates, then remove only confirmed repeats. First decide what makes a record unique. A repeated email address may be an error in a contact list, but repeated customer IDs may be valid in a transactions table. Use Data > Remove Duplicates only after selecting the relevant columns in the dialog. Selecting every column removes rows that match across all selected fields; selecting just an ID removes records that share that ID even if other details differ. For a review-first approach, with record IDs in A2:A100 and the formula entered in B2 and copied down, use
=COUNTIF($A$2:A2,A2)>1. If ID 104 first appears in row 4 and again in row 9, the formula returns FALSE for the first occurrence and TRUE for the later one. Filter the helper column for TRUE and review those rows before deleting anything. - Standardize categories and verify allowed values. Make category labels consistent, such as using one spelling and capitalization for each region or product type. Use Find and Replace only when you are certain the old and new values mean the same thing; replacing “CA” globally, for example, could change unrelated text. For ongoing data entry, select the relevant cells and choose Data > Data Validation to restrict entries to a list or an appropriate number or date range. Validation helps prevent new inconsistencies, but it does not automatically repair invalid values already in the sheet. Review existing entries with filters before applying a rule.
- Run a final quality check and preserve the cleaned result. Check that the record count is plausible, headers are still correct, key columns have no unexpected blanks, and dates and numbers behave properly in sorting or calculations. Compare a few cleaned records with the untouched source. Look for accidental changes to leading zeros, negative signs, decimal separators, or date order. Keep the original sheet, label the cleaned sheet clearly, and save the workbook. If you will repeat this process with new files, consider Data > From Table/Range to clean the data in Power Query; retain the original query source and verify each transformation before loading the result.
Common Mistakes
Removing duplicates without deciding which columns define a duplicate is one of the most damaging errors. A repeated name does not necessarily mean a repeated person, and a repeated customer ID may be expected across order records. Review the data model before using Remove Duplicates.
Another frequent problem is converting identifiers to numbers. Excel may drop leading zeros from codes, or display long numeric identifiers in scientific notation. Keep those fields as text when the digits identify something rather than represent an amount. Also avoid sorting a single column without selecting or including the full table, since that can detach values from their original records.
Finally, do not use Replace All broadly without checking the scope. A short string may occur inside a longer, unrelated word. Do not fill missing values with a convenient default unless the data owner confirms that it is valid. A clean-looking sheet can still be inaccurate if the transformations have changed the meaning of its records.
Tips
- Apply one type of cleanup at a time, then inspect a sample before moving on. This makes mistakes easier to trace and undo.
- Use filters to find blanks and rare values, and sort only when the whole table remains together. Table filters are usually safer than manually selecting a column.
- Keep helper columns until you have checked their output. Paste cleaned values over source data only after comparing representative rows.
- For regular imports, use Power Query to record repeatable steps such as trimming text, changing data types, and removing rows. On Mac, Power Query support and available connectors depend on the Excel build; Excel for the web has a more limited set of editing features.
- Save a new version after major cleanup stages. A dated filename or clearly named Raw and Clean sheets can make the process easier to audit later.
Troubleshooting
Dates display as serial numbers or convert to the wrong day. Excel stores dates as numbers and applies a display format. Select the cells and use Home > Number to choose a date format. If the actual day and month are reversed, undo the conversion and re-import or convert with the correct date order; changing the display format alone will not fix a misread date.
Find and Replace misses some apparently identical text. The values may contain nonbreaking spaces, line breaks, or different punctuation. Try the helper formula in the text-cleaning step, or inspect a few cells in the formula bar. Check the replacement scope so you do not alter other columns unintentionally.
Remove Duplicates deletes more or fewer rows than expected. Undo immediately with Ctrl+Z on Windows, then reopen the command and review the selected columns. If your rows are valid repeated transactions, do not deduplicate on customer ID alone. If you are on Mac, use Command+Z to undo; the ribbon commands are similar, though their placement can vary by build.
A formula appears as text or does not calculate. Check that the cell format is not Text, that the formula starts with an equals sign, and that calculation is enabled. Change the format to General, then edit the cell and press Enter. If a formula uses commas but your regional settings require another list separator, Excel may need the separator used by that installation.
Power Query is missing or a command is unavailable. Confirm that you are using the desktop Excel application and check for updates. On Mac, available Power Query features vary by version and connector; in Excel for the web, some transformations may not be editable. For a one-off cleanup, use the worksheet tools described above or open the workbook in desktop Excel.
Frequently Asked Questions
What is the safest first step when cleaning raw data?
Save a separate copy and keep the untouched source. That gives you a way to compare results and recover from an unwanted change.
Should I remove every blank row?
Remove rows that are truly empty and outside the dataset, but do not delete records just because one field is blank. Decide whether the missing value affects the record’s usefulness.
Does Remove Duplicates keep the first copy?
Excel keeps the first occurrence in the selected range and removes later matching rows based on the columns you selected. Sort or review the records first if you need to keep a particular version.
How can I clean data without changing the original column?
Use a helper column with a cleanup formula or transform a copy in Power Query. Review the output before copying values back or loading the transformed data.
Can Excel clean new files the same way each time?
Yes. Power Query can save a sequence of transformations and apply it to refreshed data, provided the incoming files have a compatible structure. Check the refreshed output because changed headers or formats can affect the steps.
Related
- How to Center a Word in Excel
- How to Center a Number in an Excel Cell: Quick Steps
- Center a Number in Excel: Steps, Options, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Dropdown: Remove Duplicate Options EasilyTo remove duplicate choices from an Excel dropdown, clean the list that feeds it: select that source list,…
- SUMIF with OR in Google Sheets: Formulas That WorkTo use SUMIF with OR logic in Google Sheets, add the results of separate SUMIF formulas, or use…
- Can You Hide a Column in a Power BI Table Visual?Not with a dedicated hide-column switch in a Power BI table visual. To make a column disappear, remove…
- What Does an Excel Heat Map Look Like?An Excel heat map looks like a range of spreadsheet cells filled with different colors to show how…
- How to Clear Cache Files in Outlook SafelyTo clear Outlook’s local cache on Windows, close classic Outlook, rename the account’s .ost file, then reopen Outlook…
- Excel Format by Number Range with Conditional FormattingTo format Excel cells by number range, select the cells and choose Home > Conditional Formatting > Highlight…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.