Windows & Other OS

How to remove duplicate or blank rows from a table in Microsoft Excel

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.

On the Table Design tab, click Remove Duplicates

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.

Check if your table has headers

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.

Select the columns that contain duplicates

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.

An Excel message 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 final result after removing them.

Duplicates found and deleted in an Excel table

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

Uncheck Select All and check Whites

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.

Filtered blank rows in an Excel table

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

Right click and choose Delete 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’.

Clean the filter

You will then see your table back to its normal, unfiltered view with its blank rows missing.

Blank rows found and deleted in an Excel table

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

RELATED:   How to add a password to your Kindle

Related posts

How to check your PC specifications in Windows 11

vaibhav

How to add multicolored text in PowerPoint

vaibhav

How to shut down a Windows 11 PC

vaibhav

Leave a Comment