A Step-by-Step Guide: Clearing Out Empty and Repeated Data Rows on Microsoft Excel Sheets

Quick Links

Running into duplicate or missing rows in your spreadsheets can be a big problem. Whether it’s because of data entry errors or import problems, mistakes happen. We’ll show you how to correct these issues in your Excel sheet tables .

It’s possible to find duplicates and highlight blanks in an Excel sheet using conditional formatting. However, when you’re working with a table, handling these issues instead of just highlighting them can actually be a bit easier.

Remove Duplicate Rows in an Excel Table

If you would rather highlight your duplicate data so that you can correct it, you’ll want to use the conditional formatting described earlier. But if you want to simply delete all duplicate rows in your table, it takes just a few clicks.

Select a cell in your table. Then, head to the Table Design tab that displays and click “Remove Duplicates” in the Tools section of the ribbon.

You’ll see the Remove Duplicates window open. If your table has headers, check the box for that option. These headers will display as your column selection options below. If you don’t have headers, you’ll just see Column A, Column B, and so on.

Then, choose the columns that have the duplicate data you want to remove. By default, all table columns are marked. You can check specific columns by clicking “Unselect All” and then marking the individual columns. Or you can click “Select All” if you change your mind.

When you’re ready, click “OK.” You will not see the duplicates highlighted nor will you have any indication of what’s been removed. But you will see the number of duplicates that were found and deleted.

For a simple example using the screenshot below, you can see the duplicates in our table and then the end result after removing them.

Remove Blank Rows in an Excel Table

To delete blank rows in your Excel table, you’ll use the filter feature . Since tables can already have filter buttons in the headers, you don’t have to take an extra step to enable filters.

If you don’t see the filter buttons, go to the Table Design tab and check the box for Filter Button.

Click the filter button in one of your column headers. At the bottom of the pop-up window (below Search), uncheck the box for Select All. Then scroll to the bottom of the items, check the box for Blanks, and click “OK.”

If you do not see an option for Blanks in the filter settings, then you do not have any in that table column.

Next, you’ll see the table adjust to display only blank rows with the remaining data hidden from view. You can then delete the blank rows. You’ll notice that the row headers are highlighted in blue, making them easier to see and select.

Select a blank row, right-click, and pick “Delete Row.” You can do this for each blank row.

Do not drag through the blank rows to select and delete them. If you do this, that selection may include hidden rows that are not blank.

Once you’ve removed the blank rows in your table, you can clear the filter. Click the filter button next to the column header again and select “Clear Filter From.”

You’ll then see your table back to its normal view and unfiltered with your blank rows gone.

For advanced data analysis in Excel, consider creating a pivot table .

Win Able

A Step-by-Step Guide: Clearing Out Empty and Repeated Data Rows on Microsoft Excel Sheets