Masum Mahdy
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 the 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.
How to Delete Blank Rows in Excel: 8 Effective Methods
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 Delete Rows Using Excel Shortcuts
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 Multiple Rows in Excel at Once
Similar Readings:
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Every Other Row in Excel
- Delete Row If Cell Contains Specific Values 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 Selected Rows in Excel
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. 👇
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel without Affecting Formulas
- How to Delete Rows in Excel That Go on Forever
- How to Delete Infinite Rows in Excel
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
- 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!
Related Content: How to Delete Rows in Excel with Specific Text
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!
Related Content: How to Remove Highlighted Rows in Excel
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
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-leftmost 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.
So the following picture shows that we have successfully removed all the blank rows and gave the dataset the desired clean look.
🔎 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.
- 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-looking dataset. 👆
7.3 Combine INDEX, SMALL, ROW, and ROWS Functions
In this 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 them 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. 👇
🔎 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}
Related Content: How to Delete Hidden Rows in Excel
8. Use the 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 the “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.
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
- How to Delete Rows in a Range with VBA in Excel
Download Practice Workbook
We recommend downloading the following practice workbook and practicing along with it.
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.
Related Articles
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use VBA to Delete Empty Rows in Excel
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA
