Finding duplicate or missing rows in your spreadsheets can be a big problem. Whether due to data entry errors or import problems, errors do happen. We will show you how to correct these problems in the tables of its Excel sheet .
It is possible to find duplicates and highlight blanks in an excel sheet using conditional formatting. However, when working with a table, handling these issues rather than just highlighting them can be a bit easier.
Delete duplicate rows in an Excel table
If you prefer highlight your duplicate data In order to correct them, you will want to use the conditional formatting described above. But if you just want to remove all the duplicate rows in your table, you only need a few clicks.
Select a cell in your table. Then go to the Table Layout tab that is displayed and click “Remove Duplicates” in the Tools section of the ribbon.
You will see the Remove Duplicates window open. If your table has headings, check the box for that option. These headings will show up as your column selection options below. If you don’t have headers, you will only see Column A, Column B, etc.
Then choose the columns that have the duplicate data that you want to remove. By default, all columns in the table are checked. You can check specific columns by clicking “Deselect all” and then checking the individual columns. Or you can click “Select All” if you change your mind.
When you are ready, click “OK”. You won’t see duplicates highlighted or have any indication of what has been removed. But you will view the number of duplicates that were found and removed.
For a simple example using the screenshot below, you can see the duplicates in our table and then the final result after removing them.
Delete blank rows in an Excel table
For delete rows on White in your excel table, will use filter function . Since tables may already have filter buttons in the headers, you do not need to take an additional step to enable filters.
Suggestion: If you don’t see the filter buttons, go to the Table Design tab and check the Filter button box.
Click the filter button in one of your column headings. At the bottom of the pop-up window (under Search), uncheck the Select All check box. Then scroll to the bottom of the items, check the blank spaces box, and click “OK.”
Note: If you don’t see an option for Blanks in the filter settings, then you don’t have one in that column of the table.
You will then see that the table adjusts to show only blank rows with the remaining data hidden from view. You can then remove the blank rows. You’ll notice that the row headings are highlighted in blue, making them easier to see and select.
Select a blank row, right click and choose “Delete row”. You can do this for each blank row.
Warning: do not drag blank rows to select and delete them. If you do this, that selection can include hidden rows that are not blank.
Once you’ve removed the blank rows from your table, you can clear the filter. Click the filter button next to the column heading again and select ‘Clear filter from’.
You will then see your table back to its normal, unfiltered view with its blank rows missing.
For advanced data analysis in Excel, consider create a pivot table .