Delete Blank Rows in Excel (8 Handy Methods)

Imagine a case when you have a set of data in an Excel worksheet, and you see some unnecessary blank rows. No doubt, such unexpected empty rows annoy everyone, cause disturbance in working, and obstruct working pace. So, before working with such a dataset in Excel, we would like to delete these useless blank rows. Microsoft Excel has numerous techniques and methods to perform this task. We will show 8 of them with examples and proper illustrations.


Download Practice Workbook

We recommend downloading the following practice workbook and practicing along with it.


8 Effective Methods to Delete Blank Rows in Excel

Let’s consider the following dataset which describes the Item name, Sales amount, and Bonus. Given that, this dataset has blank rows in Row 6, 9, 11, and 13, we want to remove these unnecessary rows.

Sample Workbook: Delete Blank Rows in Excel

So, let’s start.


1. Delete a Couple of Blank Rows Manually

When we have a dataset that is not so big and has only a small number of blank rows, we can remove the rows manually. It will be quicker than implementing other methods that have Excel commands, functions, etc. in such a case. This technique consists of just two simple steps.  Let’s see. 👇

Steps:

  • Press & hold the Ctrl key and thus select the blank rows.

Get Rid of a Couple of Blank Rows Manually

  • Right-click > Go to the context menu > Click on the Delete command.
The Keyboard Shortcut for the Delete Command is: Ctrl + –

Get Rid of a Couple of Blank Rows Manually: the Context menu

That’s it! We have cleared the useless empty rows easily. 👇

Final Result: Get Rid of a Couple of Blank Rows Manually

💡 Remember:
This manual technique is only applicable where your dataset is of short size and it has only a couple of blank rows. If the dataset is huge, you have to implement one of the following 9 methods.

Read more: How to Remove Empty Rows in Excel


2. Use Excel Sort Command

The Sort command displaces the blank rows to the bottom of the dataset. As a result, the dataset gets rid of pointless empty rows. Let’s see the workflow. 👇

Steps:

  • Go to the Data tab > The Sort and filter group.
  • Click on Sort Smallest to Largest or, Sort Largest to Smallest.

Withdrawal of Blank Rows Quickly by Using the Sort Command

Finally, the blank rows are sorted out to the bottom. The following picture shows the result. 👇

Final Result: Withdrawal of Blank Rows Quickly by Using the Sort Command

💡 Remember:
If the dataset has a column for serial numbers, we have to select the Sort Smallest to Largest option so that the serial numbers don’t alter.

Read more: How to Delete Rows in Excel


3. Use Go To Special Command

This command selects blank cells. After that, we can delete the blank rows using the keyboard shortcut Ctrl + – or, the Delete command in the context menu. So, let’s see this method step by step. 👇

Steps:

  • Select any column or the whole dataset.
To select a column/ the whole dataset, select the first cell, then hold the Shift key and then select the last cell.
  • Go to the Home tab > The Editing group.
  • Go to the Find & Select drop-down menu > The Go To Special command.

Delete Blank Rows Using the Go To Special Command

The Go To Special dialog box will open up.

Shortcut: Press Ctrl + G > Go To dialog box will open up > Press Special.
  • Select the Blanks radio button > Press OK.

Go To Special dialog box

We can see from the following screenshot that the expected blank rows along with the blank cells are selected as well.

Now, let’s move forward to delete the selected rows.

  • Press Ctrl + –.
    The Delete dialog box will open up.

  • Select the Entire row radio button > Press OK.

You can also perform this deletion by using the Delete option in the context menu as described in the first method.

Final Result: Delete Blank Rows Using the Go To Special Command

That’s it. We have removed the unnecessary blank rows. We have shown the resultant dataset in the above screenshot. 👆


4. Utilize Excel Find Command

This method is very similar to the previous method. The difference is in the way we select the blank rows. Let’s move forward. 👇

Steps: 

  • Go to the Home tab > The Editing group.
  • The Find & Select drop-down > The Find Command.

Delete Blank Rows Using the Find Command

 

A dialog box named Find and Replace will appear.

We can also get to Find and Replace by pressing Ctrl + H on the keyboard.

Now, perform the following steps one by one.

  • Go to the Find part of the box.
  • Keep the Find what box blank.
  • Search Within the Sheet.
  • Search By Rows.
  • Look in the Values.
  • Mark the Match entire cell contents checkbox.
  • Press Find All.

As we can see, all 4 blank rows are being shown in the pop-up box. 👇

  • Select them all by pressing Ctrl + A.
  • Press Close.

  • Using a suitable method described in the above sections, delete them all.

The output will be as shown in the picture below. 👇

Final Result: Delete Blank Rows Using the Find Command


5. Use Excel AutoFilter Feature

We can also delete blank rows using the Filter option in excel. Here are the steps. 👇

Steps:

  • Select the entire range of data including the headers, B4:E14.
  • Go to the Data tab > The Sort & Filter group > Turn on the Filter option by clicking on it.

The keyboard shortcut for turning the Filter option on is: Ctrl+Shift+L

Remove Empty Rows Using the Filter Option

  • Click on any of the showing all icons of the headers of the dataset.
  • Unselect all > Select only Blanks.
  • Press OK.

All the rows having content have disappeared. Only the blank rows are visible now.

  • Delete the blank rows using any of the techniques described in method 1.

Though we have deleted the blank rows successfully, we also see the dataset as if we deleted all the rows with data. We have to recover the rows with data and convert the dataset to an unfiltered form song with that.

  • Click on any of the showing all icons of the headers of the dataset.
  • Select All > Press OK.

We have got back our original dataset which is now without any blank rows. The next task is to convert it to an unfiltered form.

  • Click on a random cell in the dataset and go to the Data tab.
  • Go to the Sort & Filter group > Click on the Filter command.

The filtered form is gone and the dataset is in its desired normal look. 👇

Final Result: Remove Empty Rows Using the Filter Option in Excel

An Alternative Way to Use the Filter Option:

We may like to try an alternative way of using the Filter option. This time we cannot delete the blank rows from the dataset, but we can remove them from our vision. In some cases, this method can be useful. So, let’s see! 👇

Steps:

  • Apply the Filter command on the dataset as stated earlier.
  • Click on any of the showing all icons of the headers of the dataset.

  • Unmark the (Blanks) checkbox > Press OK.

We have made the blank rows disappear from the dataset! We have to keep the Filter option ON. 👇

Result: Remove Empty Rows Using the Filter Option

💡 Remember:
It should be noted that if we turn off the Filter option, the blank rows will appear again!


Similar Readings:


6. Use Excel Advanced Filter Command

The Advanced Filter option is another useful tool in Microsoft Excel to withdraw the useless blank rows from sight. Let’s see the following steps. 👇

Steps:

First of all, we need to set up a filter criteria range. For that,

  • Create a new data column in Cell G4 with a header named Sales Person.
  • Type >"" in Cell G5.

Withdraw Blank Rows Using the Advanced Filter Option

  • Go to the Data tab > Go to the Sort & Filter group > Click on the Advanced option.

The advanced filter dialog box will open up.

  • Click on the “Filter the list, in-place” radio button.
  • Next, select the “List range” by selecting the entire dataset B4:E14.

  • Select the “Criteria range” by selecting the range G4:G5.

After completing steps 3 & 4, the Advanced Filter dialog box will look like the following picture.

  • Press OK.

The following screenshot shows that we have successfully withdrawn the blank rows from the dataset. 👇

Final Result: Withdraw Blank Rows Using the Advanced Filter Option

But the blue & non-sequential row numbers 5,7,8,10,12 and 14 indicate that the blank rows are still there though out of sight. If you want them back then you just double click between the blue row numbers and they will appear again!


7. Use Several Excel Formulas to Delete Blank Rows

7.1 Use Excel FILTER Function

In this method, we are going to use the FILTER function which is a dynamic array function available only in Excel 365.
The specialty here is you need to enter the formula only once in the upper-left most cell. The results will spill into the remaining cells of the specified range. Moreover, if we add more rows to our dataset, the function will also apply to the new rows automatically.

Let’s see how to use it. 👇

Steps:

  • Copy the header names and paste them in a new location (here, in Cell G4) with formatting.
  • Type the following formula using the FILTER function in Cell G5:
=FILTER(B5:E14,(B5:B14<>"")*(C5:C14<>"")*(D5:D14<>"")*(E5:E14<>""))
  • Press Enter.

Delete All Blank Rows Using the FILTER Function

So the following picture shows that we have successfully removed all the blank rows and gave the dataset the desired clean look.

Final Result: Delete All Blank Rows Using the Excel FILTER Function

🔎 How Does the Formula Work?

As we are looking for blank rows to delete, each of the blank rows’ cells will be blank. So we have designed criteria to find the blank cells first. Then using Boolean logic, we have deleted the blank cells, in other words, the blank rows.

E5:E14<>””
The NOT operator with an empty string “” means Not Empty. In each cell in the range E5:E14, the result will be an array as follows:
Output: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

 Similarly, for D5:D14<>””, C5:C14<>”” and B5:B14<>””, the results will be:
D5:D14<>””= {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
C5:C14<>””= {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
B5:B14<>””= {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

(B5:B14<>””)*(C5:C14<>””)*(D5:D14<>””)*(E5:E14<>””)
Following the rules of Boolean logic, this returns the following array.
Output: {1;0;1;1;0;1;0;1;0;1}

 FILTER(B5:E14,(B5:B14<>””)*(C5:C14<>””)*(D5:D14<>””)*(E5:E14<>””))
Finally, the FILTER function returns the output from the array B5:B14, which matches the criteria.=
Output: {“Matt”,”Meat”,200,10;”Lee”,”Seafood”,450,22.5;”Adam”,”Clothing”,1000,50;”Hopkins”,”Baby Toys”,780,39;”Nick”,”Clothing”,890,44.5;”Chris”,”Cosmetics”,2550,127.5}


7.2 Use COUNTBLANK Function

The COUNTBLANK function returns the number of blank cells in a specified range. Though it deals with blank cells, we can utilize the function for our cause too. Let’s see then. 👇

Steps:

  • Add a column named “Blanks” to the right side of the dataset.
  • Type the formula ⏩ =COUNTBLANK(B5:E5) ➤ in Cell F5.

Delete Blank Rows Using Excel COUNTBLANK Function

  • Drag the Fill handle icon over the range F6:F14.

  • Go to the Data tab > Go to the Sort & Filter group.
  • Turn on the Filter option.
Keyboard shortcut for Filter option: Ctrl+Shift+L

Apply Filter: Delete Blank Rows Using Excel COUNTBLANK Function

  • Click on any of the showing all icons at the headers of the dataset.
  • Unselect all > Select only 4.
  • Press OK.

  • Delete the existing rows by using any technique described in method 1.
  • Now go to the Data tab and click on the Filter option and turn it off.

Undo Filter: Delete Blank Rows Using Excel COUNTBLANK Function

After turning the Filter option off, the dataset will look like the following picture.

  • Delete column F by selecting the column and selecting the Delete command from the context menu.

Final Result: Delete Blank Rows Using Excel COUNTBLANK Function

Hence we have perfectly deleted the blank rows and generated our new fresh looking dataset. 👆


7.3 Combine INDEX, SMALL, ROW, and ROWS Functions

In the second last method, we have come up with an Excel formula. This method works in just two steps. Let’s see below. 👇

Steps:

  • Just copy the headers of the dataset and paste it to a suitable location, here in Cell G4.
  • Type the following formula in Cell G5 and press Enter.
=IFERROR(INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))), "")

📌 If you don’t have MS Excel 365, then press Ctrl+Shift+Enter.

  • Drag the fill handle icon to the right and bottom end of the dataset.

That’s it. See the following picture. 👇

Remove Blank Rows Using Excel Formula

🔎 How Does the Formula Work?

ROWS(B$5:B5)
ROWS function returns the number of rows in the range B$5:B5.
Output: 1.

⮞ ROW(B$5:B$14)
The ROW function returns the row number of the range B$5:B$14.
Output: {5;6;7;8;9;10;11;12;13;14}

⮞ B$5:B$14<>””
Output: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

⮞ IF(B$5:B$14<>””, ROW(B$5:B$14))
The IF function checks the range B$5:B$14 whether it satisfies the condition, and returns the following.
Output: {5;FALSE;7;8;FALSE;10;FALSE;12;FALSE;14}

SMALL(IF(B$5:B$14<>””, ROW(B$5:B$14)),ROWS(B$5:B5))
The SMALL function determines the smallest value of the above array.
Output: {5}

IFERROR(INDEX(B:B,SMALL(IF(B$5:B$14<>””, ROW(B$5:B$14)),ROWS(B$5:B5))), “”)
Finally, the INDEX function returns the value from the B:B range and 5th row, as called by the SMALL function. The IFERROR function is just to keep the output fresh from Excel error values.
Output: {Matt}


8. Use Excel Power Query Tool to Delete All Blank Rows

The Power Query is an awesome Excel tool, and you can use it for multiple purposes. Here we are going to use this tool for our cause, deleting the blank rows. Just follow the steps below. 👇

Steps:

  • Go to the Data tab > “Get & Transform Data” group > Select “From Table/Range” option.
    A “Create Table” dialog box will open up.
  • Select the entire dataset B4:E14.
  • Press OK.

Use of the Power Query Tool to Delete Blank Rows

The “Power Query Editor” window has appeared.

Power Query Editor: Use of the Power Query Tool to Delete Blank Rows

  • Go to the Home tab > Reduce Rows drop-down menu
  • Remove Rows drop-down > Remove Blank Rows.

The blank rows are deleted. See the following picture.

Output: Use of the Power Query Tool to Delete Blank Rows

  • Go to File > Select Coles & Load To option.

Load Output to Sheet: Use of the Power Query Tool to Delete Blank Rows

The Import Data dialog box will appear.

  • Choose the Table radio button.
  • Choose the Existing worksheet radio button
  • Select your desired placement of the output, Cell B16 > Press OK.

That’s it. The output dataset is ready with no blank rows in it.

Now, if you want to convert the Table form to the Range form you have to follow some more steps.

Converting the Dataset to Range Form:

Steps:

  • Go to the Table Design tab > the Tools group > Select Convert to Range.
  • Press OK.

We have successfully converted the dataset into a range form.

The Sales and Bonus column data are in the General number type. You can easily change the number type. Just follow these two steps.

1. Select the two columns.

2. Go to the Home tab >  Number group > Select Accounting Number Format.

That’s it. See the following picture.

Final Result: Use of the Power Query Tool to Delete Blank Rows


Concluding Words

So, we have discussed 8 ways to delete blank rows in Excel. Hope you will find all these methods instrumental. Moreover, the workbook is there for you to download and practice yourself.  If you have any questions, comments, or any kind of feedback, please let me know in the comment box. And please visit our website ExcelDemy to explore more.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo