How to Delete Blank Rows in Excel - 5 Easy Ways

Mohd Sohail

29 Jan 2021 — 4 min read

Delete blank rows in excel. We use blank rows to make our data look clean. However, they can be irritative when exporting your data in some database management software such as Microsoft Access. There are so many ways to delete blank rows but my target is to teach you the 5 easiest ways. Let’s begin.

Delete Blank Rows in Excel Using Find Function

The first easy way to delete blank rows is by using the Find function. Follow the steps below and you will be able to delete blank rows from Excel Sheet.

First of all, select all data by pressing Control + Shift + End key. When you press the shortcut key, make sure you are at the first cell of the data. If the data starts at A1 just like the screenshot below. Then A1 should be selected and then press the shortcut key.

Press Control + F and you will see the Find and Replace window.

Click the Option Button on the Find and Replace Window.

In the Look in field select Values.

Leave the Find What field blank and press the Find All button.

As soon as you press the Find All button, you will see first blank cell is selected. All the blank cells information will show up inside Find and Replace window.

Click any Cell inside the Find and Replace window and Press Control + A to select all the blank cells.

Go to the Home tab and press the spin button under Delete Option. You will find Delete Sheet Rows Option. Press it and all the blank rows will be deleted.

Deleting Blank Rows in Excel Using Go To Function

Just like we did with Find Function, we used it to select the blank cells, we can achieve the same results with Go to Function. Let us see how. Follow the Steps Below.

Press Control + G and you will see Go To window on your screen. Press the Special button on Go To window and you will see a new Go-To window.

On the new Window, you will see a radio button that can select Blank cells. Select this radio button and then press Okay. You will see all the blank cells are selected.

Use the same Delete Sheet Rows option as in Find function above and delete all the blank rows.

Removing Blank Rows in Excel Using Filter

We can also make use of filters to select all the blank spaces. For those who do not know what filter is. This is a function that you can use to select a particular type of data from the entire sheet. Let us use it.

Select all the data by pressing Control + Shift + End.
Press the filter shortcut key that is Control + Shift + L.

You will see, in the header of your data table, some spin buttons appear. These spins buttons are the indicator of the drop-down list.
You need to click on any of these spin buttons and then unselect all the value types. Only select the blank option and then press the Okay button.

All the blank rows cells are selected now. Use the same Delete Sheet Rows option to delete all the blank rows from the sheet.

Remove Blank Rows in Excel Using Sorting

The sorting option has always been very useful. We can even use it to sort the data and corner the blank rows. Once the blank rows are cornered we can delete them. Let us do it.

Select all the data using the old trick. Control + Shift + End Key.
Click Sort and Filter button under Home Tab.

You will have two options to choose from. Smallest to Largest, Largest to Smallest. You can choose either of these options and the blank cells will slide down to the bottom.

You can use the same delete option under Home Tab or just press the shortcut key, Control + – (minus). All the blank rows will be deleted.

Delete Blank Rows in Excel Using Sorting

We can do a lot of things using Power Query in Excel. We can also use a power query to remove the blank rows. Let us do it.

Select all the data by pressing Control + Shift + End.
Click on From Table/Range under Data Tab.

Power Query Editor will open. You can see the Remove Rows option.
Click on this Remove Rows Option and then select the Remove Blank Rows option.

Close Power Query Editor and it will ask you to keep the processed data or discard it. Press Keep and you will see the cleaned data is now available in the form of a table.

I hope the article was helpful. There are so many other ways to remove blank spaces. I will keep on updating this article. See you next time guys.

Take Care!

Article Suggestion – How To Add Data Bars In Excel?