Exceljet

Quick, clean, and to the point

How to quickly remove rows that have empty values

Sometimes you need to remove rows from a list or a table that are missing values. You could delete the rows one by one, but that will take a long time if you have a big list.

In a previous tip, we showed you how to delete blank rows.  In today's ExcelJet tip, we'll show you a cool way to delete rows that are missing values in one step, even when your list contains hundreds or thousands of rows.

Let's take a look.

Here's our big list. You can see that this is a list of users, and that one of the columns contains a last login date. We want to remove all of the rows that don't have a last login date.

We could just work our way through the list, deleting those empty rows one by one. The problem is, this is a really big list, with over 11,000 rows, and deleting the rows manually will take way too long.

So let's look at a really fast way to do it using Excel's GoTo Special command.

To start off, select the entire "Last login" column. Then make sure you are on the Home tab of the ribbon. Now click "Find & Select" and choose Go To Special. Select "Blanks" and click OK. Excel has now selected all of the blank cells in the column.

Now carefully right-mouse click on one of the empty calls, and choose Delete... from the menu. Then select Entire row, and click the OK button. Now we have a clean list where all rows contain a value for Last login. If we hop down to the bottom of the list, there are a little more than 8,000 rows, which means we just deleted almost 3000 rows with no value for Last login.

We hope this tip is useful to you. Before you try it, make sure you don't have important information above or below the list you're working with. Otherwise, you could accidentally delete it.

See you next time

 

Course 
Core Excel

Related shortcuts

Author 
Dave Bruns