How to Remove Unwanted Rows in Excel

In this article you will learn how to easily remove of unwanted rows with just a few clicks. All easy and fun (without using macros or VBA). This trick will save a lot of your time during preparing data in tables.

Missing data issue

Blank rows between the data can turn your day into nightmare. They certainly lose a lot of your precious time. If there are only a few rows then you can delete them manually. Worse when there are hundreds of blanks. Then this article brings help. Sometimes it may happen that you want to remove all empty row with just few clicks. How to do it in Excel?

You want to delete blank lines inside your table. An example table is in the image below.

remove rows Example Table

How to remove?

To get rid of blank rows in Excel:

1. Select whole table (with headers)

remove rows Table Highlighted

2. Press the F5 function key. In the window that appears select Special...

You can also go to Ribbon > Home section. Click Find & Select > Go to Special…

remove rows Go To Special

3. In the next window tick Blanks

remove rows Go To Special Blanks

4. Confirm by clicking OK and blank rows will be highlighted

remove rows Blanks Highlighted

5 Click on one of them, right-click and choose Delete...

The same in Ribbon > Home tab > Cells section. Click Delete > Delete Sheet Rows.

remove rows Delete Rows

6. In the next window choose Entire row.

remove rows Delete Entire Row

7. Confirm OK and be happy.

remove rows Blanks Removed

Blank lines are gone! Your table was cleared in a few seconds! This trick just saved a lot of time. Excel is powerful when you know such tricks.

Alternative methods

Filter data

If you have a large amount of data and you only want to remove certain rows, you can use the "Filter" feature.

To do this, go to the Data tab in the ribbon and click the "Sort & Filter" button.

Then, select "Filter" to add drop-down arrows to each column header. You can then use these drop-down menus to select the criteria you want to use to filter the data, and Excel will only display the rows that meet those criteria. To remove the filtered rows, select them and press the "Delete" key.

Use a formula

If you want to remove rows based on specific conditions, such as values in a certain column, you can use a formula. For example, you can use the "IF" function to check if the values in a column meet your criteria, and then delete the rows that don't. Here's an example formula:

=IF(A1<0, "", A1)

This formula checks if the value in cell A1 is less than 0. If it is, the formula returns an empty string (""), and the row will be hidden when you apply a filter. If the value is not less than 0, the formula returns the value, and the row will be displayed.

Remove duplicates

If you have duplicate rows in your data, you can remove them by using the "Remove Duplicates" feature. To do this, go to the Data tab in the ribbon, and click the "Remove Duplicates" button. You can then select which columns you want to use to identify duplicates, and Excel will remove all but one of the duplicate rows.