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