To clean up an Excel spreadsheet, first save a copy, then make the data consistent: use one header row, remove unwanted spaces and duplicates, correct data types, deal with blanks and errors, and apply clear formatting. In Microsoft Excel for Microsoft 365 on Windows, the safest approach is to check the data before changing it and verify the results afterward.
Plan the cleanup before editing
Spreadsheet cleanup can change or remove information, so start by preserving the original. Use File > Save As to create a working copy, especially if the workbook is shared, used for reporting, or contains information that would be difficult to recreate. If the file is stored in OneDrive or SharePoint, version history may also help restore an earlier version, but a separate copy makes the working version clear.
Decide what the spreadsheet is meant to contain. For example, a sales list might have one row per transaction and columns for transaction ID, date, customer, product, and amount. If a row represents a transaction, avoid putting multiple products or dates into one cell. Define what counts as a duplicate, a missing value, or an invalid entry before removing anything; two rows with the same customer name may be legitimate if they represent different transactions.
- Keep a raw, unchanged copy and do cleanup in a separate worksheet or workbook.
- Identify the key fields that distinguish one record from another, such as an invoice number.
- Note expected formats and valid values, such as dates within a particular period or a fixed list of departments.
- Check whether formulas, charts, PivotTables, or other sheets depend on the data before changing its structure.
Make the data structure consistent
A clean data range is easier to filter, sort, summarize, and import into other tools. Use one row for column names and one row for each record. Keep each field in its own column: put a person’s first and last name together only if that is how the data will be used, and do not mix an address, phone number, and notes in one field. Give every column a clear, unique header, such as Order Date or Product ID, rather than leaving a header blank or using the same label twice.
Remove title lines, subtotals, and explanatory notes from the middle of a dataset. They can be kept above the data or on a separate sheet, but a blank row or a total row inside the range can confuse sorting and analysis. Avoid merged cells in the data area because they make records harder to select and can interfere with filters and sorting. When the range is arranged consistently, click inside it and choose Home > Format as Table, then select a style. Confirm that My table has headers is checked if the first row contains headings. Tables add filter controls and generally expand when new rows are added.
Fix inconsistent text and extra spaces
Text that looks identical may not compare as identical. A customer name can have a leading space, a trailing space, repeated spaces between words, or an invisible character copied from a website. In a spare column, clean ordinary spacing with a formula. With the original text in A2 and the cleaned result in B2, enter =TRIM(CLEAN(A2)). If A2 contains two spaces before and after Northwind, the result is Northwind. Fill the formula down, review the results, and then copy the cleaned column and use Paste Special > Values if you want to replace the original text. Keep the source column until the check is complete.
TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. CLEAN removes many nonprinting characters, but neither function removes every character that may come from external data. A nonbreaking space, for example, may remain. If a cleaned value still appears inconsistent, inspect a few examples and consider replacing the specific unwanted character rather than blindly changing all text. Also check consistent spelling, capitalization, punctuation, and abbreviations: Sales, sales, and Sls may represent one department but will be treated as different text in many summaries.
For predictable transformations, use Find and Replace with care: Home > Find & Select > Replace. Search for a precise value and inspect the scope before choosing Replace All. Replacing a short text fragment globally could alter valid words or formulas as well as the intended labels.
Standardize dates, numbers, and identifiers
Excel stores dates and numbers as values, while the cell format controls how they appear. A date imported as text may look like a date but fail to sort chronologically or work in date calculations. Select a sample cell and check its alignment and the formula bar; alignment alone is not definitive, but it can help identify text values. For a consistent delimited column, Data > Text to Columns can guide conversion. Choose the appropriate delimiter or fixed-width option and, when prompted, select a date order that matches the source, such as MDY or DMY. Check the preview before finishing.
Be especially cautious with ambiguous dates such as 04/05/2025: the meaning depends on whether the source uses month-day-year or day-month-year. Changing the display format does not reliably correct a value that was interpreted in the wrong order. Compare converted dates with the source or a trusted reference. Similarly, numbers containing currency symbols, commas, or parentheses may import as text depending on their origin and regional settings. Confirm that amounts behave as numbers before calculating totals.
Identifiers need different treatment from quantities. A postal code, product code, or account number may include leading zeroes and should often remain text; converting it to a number can remove those zeroes. Phone numbers and long reference codes are usually labels, not values to add. Decide the intended data type field by field rather than applying Number format to the entire sheet.
Find duplicates and handle blank cells
Use Data > Remove Duplicates only after deciding which columns define a duplicate. Excel compares the selected columns for each row. If you select only email address, rows with the same email may be removed even when other details differ; if you select every column, rows that differ in one detail will remain. Make a copy first, and use the dialog’s column selections deliberately. For a visual check before deletion, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values on the relevant column.
A helper formula can flag repeated IDs without deleting records. With IDs in A2:A20 and the first check in B2, enter =COUNTIF($A$2:A2,A2)>1 and fill down through B20. If ID 104 appears first in A2 and again in A8, B2 is FALSE and B8 is TRUE. This marks later occurrences, not necessarily incorrect records; inspect flagged rows and retain legitimate repeats where the ID is not meant to be unique.
Blank cells also need interpretation. A blank might mean unknown, not applicable, or accidentally missing; those meanings should not be treated as interchangeable. To locate blanks, select the data column or range and use Home > Find & Select > Go To Special > Blanks. Review the selected cells before filling or deleting anything. Do not automatically replace every blank with zero: zero is a real value in many datasets. If a record is incomplete, a separate status or note may be more informative than guessing a value.
Review formulas and error values
After changing data, check formula columns for errors such as #N/A, #VALUE!, #REF!, and #DIV/0!. These messages have different causes: for example, #REF! can indicate a broken reference, while #DIV/0! means a formula is dividing by zero or a blank denominator. Do not hide every error immediately. First decide whether the error reveals a bad input, a missing lookup match, or an intentional situation that needs a clearer display.
Compare a few calculated results with independent hand checks or known totals. If a sum appears wrong, confirm that the cells contain numbers rather than text and that the formula covers the intended rows. Check whether formulas were replaced by pasted values during an earlier cleanup. When copying formulas, verify that relative and absolute references behave as intended. Find & Select can locate formulas or constants through Go To Special, which is useful when a column expected to contain formulas has unexpected entries.
Functions such as IFERROR can make a report easier to read, but using them to conceal all errors can make a damaged formula look valid. If an error is expected and has a defined interpretation, handle that specific case and document the meaning. Keep the underlying issue visible during cleanup so that a polished display does not substitute for correct data.
Improve readability without changing meaning
Formatting helps people interpret a spreadsheet, but it should clarify the data rather than disguise it. Use consistent date, percentage, currency, and decimal-place formats within the same field. A value stored as 0.25 can be displayed as 25% when it represents a proportion, but changing the number format does not change the underlying value. Use descriptive headers, readable column widths, and restrained emphasis for headings or important totals. Avoid using color alone to communicate a status; add a label such as Approved or Review needed so the meaning remains clear when printed or viewed by someone with color-vision differences.
For a large range, a table’s banded rows and filters can make scanning easier. Freeze the header row with View > Freeze Panes > Freeze Top Row when users need to scroll through many records. Before sorting, ensure the whole dataset is selected or that the active cell is inside a correctly structured table; sorting just one column can detach values from their associated records. Check print layout separately if the spreadsheet will be printed, since on-screen readability does not guarantee sensible page breaks.
Use Power Query for repeatable cleanup
If the same type of file arrives regularly, Power Query can make cleanup steps repeatable instead of requiring the same manual edits every time. In Excel for Microsoft 365 on Windows, select a cell in a source table and choose Data > From Table/Range to open the Power Query Editor. There you can remove columns, filter rows, split a column by a delimiter, change data types, and remove duplicates. Choose Close & Load when the preview is correct. When the source data is updated, refresh the query to apply the saved transformation steps again.
Power Query is useful when a recurring import needs the same rules, but it is not a reason to skip validation. Review the source, inspect the query preview, and confirm that the loaded row count and important totals make sense. A changed source layout can affect a query, and an incorrect transformation can be repeated consistently. For a one-time, small correction, direct editing may be simpler; for repeated imports, a documented query can reduce manual inconsistencies.
Verify the cleaned workbook
Before treating the cleanup as finished, compare the revised data with the original and check that the changes match the rules you set. Confirm the number of records before and after duplicate removal, inspect a sample from the top, middle, and bottom of the range, and check totals or other known control figures. Verify that filters, formulas, PivotTables, charts, and linked sheets still work. Save the cleaned version under a clear name and keep the raw copy according to your organization’s retention practices.
- Check that headers are present, unique, and descriptive.
- Confirm that dates, numbers, and text fields have the intended types.
- Review flagged duplicates and blanks rather than assuming they are errors.
- Test sorting and filtering on a few fields to make sure each row stays intact.
- Recalculate or refresh dependent summaries and compare key totals.
Frequently Asked Questions
How do I remove extra spaces in Excel?
Use a helper column with =TRIM(CLEAN(A2)) when the source text is in A2. Review the results before copying and pasting them as values. TRIM handles ordinary spaces, but some special spaces copied from external sources may need separate treatment.
How do I remove duplicates without deleting the wrong rows?
Make a copy, determine which fields identify a duplicate, and select only those fields in Data > Remove Duplicates. If you are unsure, highlight duplicate values or use a helper column first, then inspect the flagged records before removing any.
Why does Excel not recognize dates in my spreadsheet?
The cells may contain text rather than date values, or the source’s date order may differ from Excel’s regional interpretation. Convert using the correct order and compare ambiguous dates with the source; applying a date display format alone may not fix text or a wrongly interpreted date.
Should I replace blank cells with zero?
Only when zero is the correct meaning. A blank may mean unknown, not applicable, or missing, and those are not the same as zero. Decide and document how each kind of blank should be handled before filling it.
Can I clean an Excel spreadsheet automatically?
Some actions, such as removing duplicates or using Power Query transformations, can be automated. Automation still needs well-defined rules and a review of its results; otherwise it can repeatedly remove valid records or convert data incorrectly.
Related
- Using ISBLANK in Excel: Check for Truly Empty Cells
- Use Power Query to Merge Excel Files: A Practical Guide
- Update Number Format in Excel: How to Change Formats
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 Undo Archive Emails in OutlookTo undo archiving in Outlook, find the message in the Archive folder, select it, and move it to…
- How to Export an Excel Tab to a New WorkbookIn Excel for Microsoft 365 on Windows, right-click the worksheet tab, choose Move or Copy, select (new book)…
- Freeze Multiple Rows in Excel: Step-by-Step GuideTo freeze multiple rows in Excel, select the cell in column A directly below the last row you…
- How to Track Finances in Google Sheets Step by StepTo track finances in Google Sheets, create a transaction log, record each income or expense on its own…
- How to Choose Double Spacing in WordTo choose double spacing in Word, place the cursor in a paragraph or select the text you want…
- How to Delete a Page in Word on Mac Without Losing TextTo delete a page in Word on Mac, select its contents with Go To, then press Delete; for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.