Clean Excel Rows: Remove Blanks, Duplicates, and Errors

How-to basics · Updated November 18, 2026

To clean Excel rows, first make a copy of the workbook, then standardize inconsistent values, identify genuinely blank records, remove duplicates using the right columns, and check the result before using it. In Microsoft Excel for Microsoft 365 on Windows, the main built-in tool for duplicates is Data > Remove Duplicates; blank cells and messy text need a little more care because deleting the wrong row can discard valid information.

What “cleaning rows” means

A row usually represents one record: a person, order, product, survey response, or other item. Cleaning rows means making those records consistent and usable without changing their meaning. That can involve deleting completely empty rows, correcting inconsistent text, removing repeated records, fixing values stored in the wrong format, and investigating errors or missing fields.

Cleaning is not the same as making a worksheet look tidy. Changing colors, column widths, or number formats may improve presentation, but it does not necessarily fix the underlying values. For example, two cells may both display a date while one is a real Excel date and the other is text that merely looks like a date. Likewise, “West” and “West ” look alike on screen but may be treated as different text because one has a trailing space.

Before removing anything, decide what counts as a valid record and what makes two rows duplicates. A repeated customer name might represent two separate purchases; a repeated order number might indicate a duplicate transaction. The right cleaning rule depends on what each row means.

Inspect the data before changing it

Start by identifying the header row, the columns that define a record, and any required fields. Check whether the range has a single header row, one record per row, and no subtotals or notes mixed into the data. Those structural details affect how Excel’s filters and duplicate-removal tool behave.

For a straightforward list, click inside the data and press Ctrl+T to format it as an Excel Table. Confirm that “My table has headers” is selected if the first row contains column names. Tables add filter buttons, expand as data is added, and make it easier to inspect a particular column. On Mac, the equivalent shortcut is Control+T in current Excel versions; if that shortcut is assigned differently on your Mac, use Insert > Table.

Keep an untouched copy of the source data. A practical approach is to duplicate the worksheet, name one tab “Original” and the other “Cleaned,” and work only on the latter. Save the workbook before a large edit as well. Excel’s Undo can reverse many recent actions, but it is not a substitute for a separate copy when multiple changes or imports are involved.

Find and remove blank rows safely

A row with no values anywhere in the dataset is different from a record that has a blank value in one field. If an address is optional, for example, a blank address does not make the customer row empty. Decide which columns are required before treating blanks as records to remove.

For a small list, use the Table filter on a required column, such as a record ID, and inspect rows where that field is blank. If the rows are truly empty records, select their row numbers and delete the rows. If some fields are optional, filter on a required field rather than on every blank-looking cell. Afterward, clear the filter and confirm that valid records remain.

Excel also has Home > Find & Select > Go To Special > Blanks. This selects blank cells in the selected range; it does not identify only completely empty rows. Deleting entire worksheet rows based on that selection can remove records that have a blank in just one column. Use this feature only when you understand the selection and have checked the affected rows. A safer general method is to filter a required field, review the results, and delete only confirmed empty records.

Standardize inconsistent text

Extra spaces, line breaks, and nonprinting characters can interfere with matching and sorting. In particular, spaces at the beginning or end of a value can make two apparently identical entries behave differently. For a small number of cells, edit the values directly. For a large range, use a helper column so the original values remain available for comparison.

With the original text in A2, enter this in B2 and fill the formula down:

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

The formula replaces a nonbreaking space (often brought in when copying from a web page) with an ordinary space, removes certain nonprinting characters, and trims extra spaces at the ends while reducing repeated ordinary spaces between words. For example, a value containing leading spaces and “North  Shore” with a nonbreaking space can become “North Shore.” Review the results: CLEAN does not remove every possible Unicode character, and TRIM can change repeated spaces that may be meaningful in unusual data.

When the cleaned values look right, copy the helper column and use Home > Paste > Paste Values to replace formulas with fixed text, or keep the helper column as a separate cleaned version. Do not overwrite the source column until you have checked a sample and confirmed that the transformations preserve the information you need. For inconsistent capitalization, decide on a convention such as title case or uppercase IDs, but avoid changing names or codes automatically if their exact capitalization matters.

Remove duplicate records with the right fields

Click inside the dataset and choose Data > Remove Duplicates. In the dialog, confirm that the header option reflects whether the first row contains column names, then select the columns Excel should compare. If all columns are selected, Excel removes rows that match across every selected column. If only an ID column is selected, it keeps one row for each repeated ID, even if other fields differ.

That distinction matters. Two rows for the same customer may be separate transactions, while two rows with the same unique transaction ID may be a true duplicate. If repeated IDs have different dates or amounts, investigate the conflict before deleting one. Excel keeps the first occurrence it encounters and removes later matching rows; it does not decide which record is more accurate or combine the information in duplicates.

For an extra check before deletion, create a helper column to flag repeated IDs. With IDs in A2:A100, enter this in the first data row and fill down:

=COUNTIF($A$2:A2,A2)>1

The formula returns FALSE for the first occurrence of an ID and TRUE for each later occurrence in the list. For example, if A2 is “Q17,” A3 is “Q18,” and A4 is “Q17,” the result in row 4 is TRUE. This is a flag, not a complete definition of a duplicate: confirm that the ID is supposed to be unique and review any flagged records before removing them.

Check data types, missing values, and errors

Excel stores dates and numbers differently from text strings that resemble dates and numbers. A date imported as text may sort unexpectedly or fail in date calculations. Select a few suspicious cells and inspect the formula bar and number format; changing the display format alone does not always convert text into a real date or number. Use a deliberate conversion method, then verify the result rather than assuming the appearance proves the data type.

Missing values also need context. A blank quantity may mean “not recorded,” while zero means the quantity is known to be none. Replacing every blank with zero can therefore create false information. Consider documenting missing values, using a clearly defined placeholder only when appropriate, or keeping a separate status column that explains why a value is absent.

Look for Excel error values such as #N/A, #VALUE!, or #REF! in relevant columns. These may indicate a broken formula, a failed lookup, or a deleted reference; they are not ordinary text errors to erase without investigation. If the worksheet is an imported values-only file, the error may have been included in the source. Find out what it represents before replacing or removing the row.

Validate the cleaned result

After editing, check that the row count changed as expected and that the header row and column alignment are intact. Compare key totals or counts before and after cleaning where possible. For example, if you remove duplicate order IDs, count the remaining IDs and inspect a few records that were flagged. If the cleaned list feeds a report, refresh or recalculate it and confirm that the output still makes sense.

Useful final checks include:

  • Filter required fields for blanks and review any remaining records.
  • Sort important text and date columns to spot unexpected values or formats.
  • Check unique identifiers for repeats if they should be unique.
  • Compare a few cleaned values with the original sheet or source file.
  • Keep a note of the rules used, especially if the cleaning will be repeated.

For recurring imports, consider turning the cleaning steps into a repeatable process rather than editing each file by hand. Power Query, available through Data > Get Data in Microsoft 365, can import data and apply recorded transformation steps, such as removing blank rows or trimming text. Review the transformed preview and the resulting data carefully; automated steps follow instructions and cannot tell whether two similar records are genuinely duplicates.

Common mistakes to avoid

The most damaging mistake is deleting rows based on appearance alone. A row that looks blank may contain a formula returning an empty string, and a row with one empty cell may still be a complete record. Another frequent error is selecting only one column before removing duplicates, which can remove records that share that value but differ in other important fields.

Do not sort a single column independently from the rest of a dataset. That disconnects values from their original records. Sort the whole Table or the complete data range so each row stays together. Also avoid using Find and Replace to make broad changes without checking the scope: replacing a space or punctuation mark throughout a workbook can alter legitimate names, addresses, or identifiers.

Finally, remember that cleaning choices can affect later analysis. Removing repeated customer names, for instance, is not equivalent to removing duplicate orders. Write down what defines a record, what counts as a duplicate, and how missing values are handled. That small bit of documentation helps another person understand why the cleaned sheet differs from the source.

Frequently Asked Questions

How do I remove blank rows in Excel without deleting valid data?
Filter a required column, such as a record ID, for blanks, inspect the visible rows, and delete only records confirmed to be empty or invalid. Avoid deleting entire rows just because Go To Special selected a blank cell in one field.

How do I remove duplicate rows in Excel?
Select the dataset, choose Data > Remove Duplicates, and select the columns that define a duplicate. Selecting all columns compares complete rows; selecting a unique ID compares only that field. Review conflicting records first because Excel keeps the first match it finds.

Does Remove Duplicates delete every copy?
No. Excel keeps the first occurrence and removes later rows that match the selected columns. If you need to decide which copy to retain, sort or review the records first, or use a helper flag to inspect repeats before deleting.

Why does Excel treat two identical-looking values as different?
They may contain leading or trailing spaces, nonbreaking spaces, hidden characters, or different underlying data types. Compare the raw values, clean text in a helper column, and confirm that converting the values will not change their meaning.

Can I clean rows in Excel without changing the original data?
Yes. Duplicate the worksheet or workbook and perform the cleanup on the copy. For text corrections, use a helper column and keep the original values until you have reviewed the results. Power Query is another option for repeatable transformations that leave the imported source unchanged.

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.