Clean Up an Excel Spreadsheet: A Practical Guide

How-to basics · Updated November 22, 2026

To clean up an Excel spreadsheet, first save a separate copy, then inspect the data, correct inconsistent values and formats, remove only confirmed duplicates or unwanted rows, and check the result. In Excel for Microsoft 365 on Windows, a reliable cleanup is a sequence of small, verifiable changes—not a single command that automatically knows which data is wrong.

Make a safe copy and define the goal

Before changing a workbook, use File > Save As to create a working copy. This matters because deleting rows, replacing values, or removing duplicates can be difficult to reverse after more edits, and Ctrl+Z does not undo every kind of change indefinitely. If the workbook is shared, coordinate with other editors and avoid changing the original while someone else is using it.

Decide what clean means for this particular file. A contact list might need one row per person and consistent phone numbers; a sales table might need valid dates, numeric amounts, and a reliable product identifier. Do not assume that blank cells, repeated names, or unusual values are errors. A blank in an optional field can be legitimate, and two customers can share a name.

  • Identify the intended unit of each row, such as one order, one customer, or one inventory item.
  • Choose a key field, if available, such as an order number or customer ID, to help identify duplicates.
  • Keep a note of important rules, such as whether leading zeros in an ID must be preserved.

Inspect the data before editing

Start by checking the column headings and the shape of the data. Each column should describe one kind of information, such as order date or quantity, and each row should represent one record. Look for repeated header rows inside the data, totals mixed in with records, merged cells, and notes placed in the middle of the table. These can interfere with sorting, filtering, formulas, and later analysis.

Click inside the dataset and press Ctrl+T to format it as an Excel table; confirm whether the range has headers. On Mac, Command+T opens the Create Table dialog in Excel, and Insert > Table is another route. A table adds filter buttons, expands as data is added, and helps keep a dataset together. Before sorting, confirm that the whole range is included. Sorting only one column can separate names from their associated addresses or amounts.

Use the filter arrows in the table headings to scan for blanks, uncommon categories, and suspicious values. Sorting a column can bring unusual entries together, but review the full row before correcting anything. A value such as 0, N/A, or Unknown may have a specific meaning in the source system rather than being a typo.

Standardize text and remove stray characters

Inconsistent spaces and spelling can make the same category appear as several values. For example, a department might be listed as Sales, sales, and Sales followed by a space. First determine the approved spelling and capitalization. Find and Replace can correct a known, exact variation: use Ctrl+H on Windows, or Control+H on Mac, enter the old value and replacement, and review the scope and options before choosing Replace All. Replacing a short fragment globally can alter unrelated text, so use a selected range or a more specific search when appropriate.

For extra spaces and some nonprinting characters, make a helper column. With the original text in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2, then fill down. TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words; CLEAN removes certain nonprinting characters; SUBSTITUTE replaces nonbreaking spaces, which can arrive in copied web data. Compare the results with the original. When they are correct, copy the cleaned column and use Paste Special > Values to replace the original or keep the cleaned values as a separate field. TRIM does not fix spelling or every Unicode character.

Do not remove punctuation automatically from fields where it matters. Hyphens in product codes, apostrophes in names, and leading zeros in postal codes or IDs may be meaningful. Treat identifiers as text when their appearance must be preserved; a value such as 00124 can lose its leading zeros if Excel interprets it as a number.

Find and remove duplicates carefully

Repeated records are not always duplicates. Two transactions can have the same customer and date but different order numbers, while a single customer can appear in several rows because the table records separate purchases. Decide which columns define a duplicate before deleting anything. A unique order ID is usually a stronger test than a matching name alone.

To remove confirmed duplicates, select a cell in the table and choose Data > Remove Duplicates. In the dialog, select the columns that together identify a repeated record. If the table has headings, verify that My data has headers is selected. Excel keeps one occurrence and removes matching rows from the selected range; this changes the data, so make the backup first and inspect the remaining records. Selecting only one column means rows with the same value in that column can be treated as duplicates even if their other fields differ.

If you want to review repeated IDs before deleting them, use a helper formula. With identifiers in A2:A100, enter =COUNTIF($A$2:A2,A2)>1 beside the first record and fill down. The first occurrence returns FALSE and a later occurrence of the same value returns TRUE. Filter the helper column for TRUE and check the complete rows. COUNTIF treats text matches without regard to capitalization, so records that differ only by case may still be flagged.

Handle blanks and missing values without guessing

Use a table filter to show blank cells in a column and decide what each blank means. It might mean not applicable, not provided, or a data-entry omission. These meanings are different. Filling every blank with zero can distort totals, while replacing missing information with a guess creates false data. If a consistent label is useful, choose one that users understand and document the convention.

To locate blank cells, select the relevant range and choose Home > Find & Select > Go To Special > Blanks. Check the selection before typing or deleting: selecting blanks across a multi-column range can affect many records. Deleting entire rows is appropriate only when the rows are genuinely empty or out of scope. A partially blank record may still contain valuable information, and deleting it could remove that information too.

For a row that is completely empty within a structured list, filter or inspect the row first, then delete the table row using the table’s row controls or the Home > Delete menu. Avoid selecting blank cells in one column and deleting entire worksheet rows without checking the rest of the data; blank fields may be normal in other records.

Correct dates, numbers, and data types

Excel stores recognized dates as serial numbers and displays them according to a number format. A cell that looks like a date may instead contain text, especially when data was imported or when day and month order is ambiguous. Changing the display format does not convert text into a real date. Test a few values by changing the number format or using them in a calculation, and compare the interpretation with the source system before converting a whole column.

For text values that follow a consistent delimiter pattern, Data > Text to Columns can split or convert them. Use the preview carefully and select the appropriate date order when converting dates; choosing the wrong order can silently reverse month and day. For example, 04/05/2026 can mean April 5 or May 4 depending on the source convention. Import settings and regional formats affect interpretation, so confirm a sample rather than relying on appearance alone.

Check numeric columns for numbers stored as text, currency symbols embedded in values, and inconsistent decimal or thousands separators. A green error indicator may offer a conversion option, but do not convert identifiers that need leading zeros. Also check negative amounts and units: changing formatting cannot establish whether a value is correct or whether a figure represents dollars, cents, kilograms, or another measure.

Make formatting consistent and readable

Formatting should clarify the data without changing its meaning. Apply one clear header style, consistent date and number formats within each column, and a readable font. For a range of values, use an appropriate number format from Home > Number Format rather than typing symbols into every cell. For example, currency formatting displays a currency symbol while preserving the underlying numeric value for calculations.

Remove decorative formatting that obscures patterns, such as several different fills for the same kind of field, but keep meaningful cues and notes. Conditional formatting can highlight exceptions: select a range and use Home > Conditional Formatting to create a rule. Review existing rules if colors seem inconsistent, because old rules may apply to the wrong range or have overlapping conditions. Avoid relying on color alone to communicate a status; include a text label when readers need to interpret the result.

Adjust column widths so headings and values can be read, and freeze headings if the list is long using View > Freeze Panes. Do not merge cells inside a data table: merged cells make it harder to sort, filter, and use the range in formulas. A clean worksheet can still have a presentation area, but keep the underlying records in a simple rectangular table.

Validate the cleaned workbook

After edits, check that the record count and key totals make sense. Compare the number of rows before and after duplicate removal, and verify that removed records were truly redundant. Recheck the source for several corrected entries, especially date conversions and identifier fields. If the data feeds a report, refresh or recalculate it and confirm that expected categories and totals remain present.

Look for errors such as #VALUE! or #N/A in formulas, and check whether formulas still cover the intended rows. Filters can hide records, so clear them before judging the full dataset. Save the result under a clear filename and keep the original copy until the cleaned version has been reviewed. If the workbook contains sensitive information, share only the necessary fields and use your organization’s approved storage and access controls.

When Power Query is a better choice

If the same cleanup must be repeated whenever a new file arrives, Power Query can record steps such as removing columns, changing data types, trimming text, and filtering rows. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. Make changes in the Power Query Editor, then use Home > Close & Load to return the result to Excel. The next time the source is updated, refresh the query and review the output.

Power Query is useful for repeatable imports, but it does not know the business meaning of a field. A type conversion can misread ambiguous dates, and a filter can exclude valid records if its rule is too broad. Keep the source unchanged, review each transformation step, and validate the loaded table. Excel for Mac has Power Query capabilities, but available connectors and features can differ by version; Excel for the web also does not provide the same full desktop editing experience.

Frequently Asked Questions

How do I clean up an Excel spreadsheet quickly? Save a copy, turn the data into a table, inspect columns with filters, correct clear inconsistencies, and verify duplicates before removing them. Check dates, numbers, and row counts afterward. A quick pass should not mean deleting records based only on appearance.

How do I remove extra spaces in Excel? Use TRIM on a helper column to remove leading and trailing ordinary spaces and reduce repeated spaces. If copied data may contain nonbreaking spaces or nonprinting characters, a formula such as the one in the text-cleaning section can handle some of them. Review the output before replacing the source values.

How can I remove duplicates without deleting the wrong rows? Define what makes a record unique, such as an order ID or a combination of fields, then use Data > Remove Duplicates with only those columns selected. For a safer review, flag repeated identifiers in a helper column first and inspect each full record.

Should I replace blank cells with zero? Only when zero is the true value and that convention is appropriate for the field. A blank may mean unknown, not applicable, or not entered; replacing it with zero can change averages, totals, and interpretations.

Why does Excel not recognize my dates? The cells may contain text, or the date order may conflict with the workbook’s regional settings. Confirm the source convention and test a small sample before converting the column. Applying a date display format alone does not convert text into date values.

Related

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.