Microsoft Excel Logo

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.

Mark if your table has headers

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.

Select the columns containing duplicates

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.

An excel prompt with the number of duplicates found

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

Duplicates found and removed in an Excel table

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.

Tip: 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.”

Uncheck Select All and check Blanks

Note: 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.

Filtered blank rows in an Excel table

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

Warning: 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.”

Clear the filter

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

Blank rows found and removed in an Excel table

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

RELATED: How to Check If a Cell Is Blank With ISBLANK in Excel

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »