Delete Blank Rows in Excel (8 Suitable 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.

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.

• Right-click > Go to the context menu > Click on the Delete command.

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

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

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

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

• Go to the Home tab > The Editing group.
• Go to the Find & Select drop-down menu > The Go To Special command.

The Go To Special dialog box will open up.

• Select the Blanks radio button > Press OK.

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.

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

Read More: How to Delete Rows in Excel That Go on Forever (4 Easy Ways)

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.

A dialog box named Find and Replace will appear.

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

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.

• 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. 👇

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

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

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)

Similar Readings

• How to Delete a Row If a Cell is Blank in Excel (4 Methods)
• Formula to Remove Blank Rows in Excel (5 Examples)
• Delete Multiple Rows in Excel (3 Methods)
• How to Delete Selected Rows in Excel(8 Approaches)
• How to Delete All Rows Below a Certain Row in Excel (6 Ways)

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.

• 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. 👇

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!

Read More: How to Delete Filtered Rows in Excel (5 Methods)

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:

• Press Enter.

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

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.

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

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

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.

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.

📌 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. 👇

🔎 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}

Read More: How to Delete Blank Rows in Excel (6 Ways)

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.

The “Power Query Editor” window has appeared.

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

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

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.

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)

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

• Delete Every nth Row in Excel (Easiest 6 Ways)
• Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
• How to Use VBA to Delete Empty Rows in Excel
• Delete Infinite Rows in Excel (5 Easy Ways)
• How to Delete Hidden Rows in Excel (3 Methods)
• Excel VBA to Delete Rows with Specific Data (9 Examples)