How to Skip Blank Rows Using Formula in Excel (8 Methods)

A Microsoft Excel spreadsheet with blank rows is a frequent occurrence. The majority of them are superfluous, and they take up extra space on the worksheet. This is unnecessary. As a result, you might wish to skip those blank rows from your spreadsheet. Fortunately, Microsoft Excel offers several options for skipping those blank rows from your spreadsheet. We can, however, apply formulae to skip them altogether. Today, in this article, we’ll learn eight quick and suitable ways to skip blank rows in Excel using formula effectively.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Suitable Ways to Skip Blank Rows Using Formula in Excel

Let’s say, we have a dataset that contains information about several Students. The securing marks in Electrical and Electronics Engineering(EEE), Mechanical Engineering(ME), and Civil Engineering(CE) are given in columns  D, E, and F respectively. Our dataset has some blank rows. Hence, we will skip those blank rows by using the keyboard shortcuts and the COUNTBLANK, FILTER, IF, AND, ISBLANK, INDEX, ROW, ROWS, SMALL, and COUNT functions in Excel. Here’s an overview of the dataset for today’s task.

skip blank rows in excel using formula


1. Use the Keyboard Shortcuts to Skip Blank Rows in Excel

From our dataset, we will skip rows that are blank. Undoubtedly, using keyboard shortcuts to skip blank rows is an easy task. Let’s follow the steps below to skip blank rows.

Step 1:

  • First of all, select cells array B5 to F14, and press the F5 key on your keyboard.

Use the Keyboard Shortcuts to Skip Blank Rows in Excel

  • Hence, a Go To window will appear in front of you. From that window, type the cell reference B5:F14 in the Reference Then, press on the Special option.

  • After that, the Go To Special dialog box pops up. From the Go To Special dialog box, firstly, select Blank from the Select option and then press OK.

Use the Keyboard Shortcuts to Skip Blank Rows in Excel

Step 2:

  • After completing the above process, you will be able to select the blank rows.

Use the Keyboard Shortcuts to Skip Blank Rows in Excel

  • Hence, press the Ctrl + minus(-) key simultaneously on your keyboard, and instantly a Delete window pops up. Now, select the entire row from the Delete option and at last, press OK.

  • When you will complete the above steps, you will be able to skip blank rows which have been given in the below screenshot.

Use the Keyboard Shortcuts to Skip Blank Rows in Excel

Read More: How to Find Blank Cells in Excel (8 Easy Ways)


2. Perform the Delete Command to Skip Blank Rows in Excel

The easiest and time-saving way to skip blank rows is to perform the Delete command in Excel. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select cells array B4 to F14. Then, from your Data tab, go to,

Data → Sort & Filter → Filter

Perform the Delete Command to Skip Blank Rows in Excel

  • Hence, a Filter will enable each header cell in the first row of our selected data. After that, click on the Filter sign and a window pops up, from that window select (Blank) and then press OK.

Step 2:

  • Further, from your Home tab, go to,

Home → Cells → Delete → Delete Sheet Rows

Perform the Delete Command to Skip Blank Rows in Excel

  • After clicking on the Delete Sheet Rows option, you will be able to delete the blank rows.

Perform the Delete Command to Skip Blank Rows in Excel

  • Now, we will remove the Filter sign from the header cell. To do that, go to,

Data → Sort & Filter → Filter

Perform the Delete Command to Skip Blank Rows in Excel

  • After completing the above process, you will be able to skip blank rows which have been given in the below screenshot.

Related Content: How to Delete Empty Cells in Excel (6 Methods)


3. Apply the COUNTBLANK Function to Skip Blank Rows in Excel

We use the COUNTBLANK Function to count the number of blank cells in each of the rows. Based on this count value we can filter out the rows having blank cells in them. To do that follow the steps below.

Step 1:

  • First of all, type the following COUNTBLANK formula in cell G5.
=COUNTBLANK(B5:F5)

Apply the COUNTBLANK Function to Skip Blank Rows in Excel

  • Then simply press the ENTER button on your keyboard, and you will get 0 as the return of the COUNTBLANK function.

Apply the COUNTBLANK Function to Skip Blank Rows in Excel

  • After that, drag the autoFill Handle downward to the entire column.

Step 2:

  • After all these steps, we will see the blank cell count for each of the rows. Skip blank rows in Excel using the COUNTBLANK Hence, select the whole data table and then press CTRL + SHIFT + L.
  • This command will enable the Filter feature on the data table like the following picture.

Apply the COUNTBLANK Function to Skip Blank Rows in Excel

  • After that, click on the drop-down icon of the Blank Then select 0, and finally, press the OK command.

Apply the COUNTBLANK Function to Skip Blank Rows in Excel

  • After completing the above process, you will be able to skip blank rows which have been given in the below screenshot.

Read More: Find, Count and Apply Formula If a Cell is Not Blank (With Examples)


4. Perform the FILTER Function to Skip Blank Rows in Excel

We can use the FILTER function to simply filter out all the blank rows from an Excel worksheet. This function is a dynamic array function. That means you only execute this function in a single cell, and then this function will automatically cover all the corresponding cells where the formula result should be. Now follow the steps below to do that.

Steps:

  • First, select cell G5, and write down the FILTER function in that cell. The FILTER function is,
=FILTER(B5:E14,(B5:B14<>"")*(C5:C14<>"")*(D5:D14<>"")*(E5:E14<>""))
Formula Breakdown:

(B5:B14<>”” ) ▶ Checks for blank cells in the B column.

(C5:C14<>””) ▶ Look for blank cells in the C column.

(D5:D14<>””) ▶ Finds blank cell within column D.

(E5:E14<>””) ▶ Checks out column E for the blank cells.

Finally, the formula filters out all the blank cells by keeping only the rows without those blank cells.

Perform the FILTER Function to Skip Blank Rows in Excel

  • After that, press the ENTER button on your keyboard, and instantly you will be able to skip the blank rows.

Perform the FILTER Function to Skip Blank Rows in Excel

Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)


Similar Readings:


5. Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

In this method, we will use the IF, AND, and ISBLANK  functions to specify which row is blank and which row is not. Using this formula, we will mark the rows having blank cells as Blank and rows without a single blank cell as Not-Blank. After that, based on this Blank/Not-Blank specification, we will filter out the blank rows from our Excel worksheet. Hence, follow the steps below to do that.

Step 1:

  • Type the following formula in cell F5.
=IF(AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),"Blank", "Not-Blank")
Formula Breakdown:

ISBLANK(B5) ▶ It finds the blank cell.

AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)  ▶ Indicates that the formula works from column B to column E of the 5th row of the worksheet.

The whole formula then finds the row is blank or not.

Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

  • After typing these functions in that cell, press the ENTER button on your keyboard, and you will get Not-Blank as the output of the array functions.

  • Then drag the autoFill Handle downward, and you will get the output of the IF, AND, and ISBLANK Functions in column F.

Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

Step 2:

  • After that, select the whole data table and press CTRL + SHIFT + L to apply the Filter feature on the data table.
  • Click on the drop-down icon at the corner of the Blanks table header.
  • Further, uncheck the Blank option from the list. Finally, press the OK command.

  • After pressing the OK option, you will be able to skip blank rows from your dataset.

Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

Related Content: Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)


6. Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

We will make an array formula using the IFERROR, INDEX, SMALL, IF, ROW, and ROWS functions altogether. This array function will skip all the blank rows out of the data table in Excel.

Using this method, we will keep the original data table aside. We will copy only the table headers from the original data table having blank rows in it. Under the copied table header we will use the formula to get all the rows excluding the blank ones. To do that, follow the steps below.

Step 1:

  • First, write down the following array formula in cell G5.
=IFERROR(INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)), ROWS(B$5:B5))), "")
Formula Breakdown:

ROW(B$5:B$14) ▶ It gives a start from row 5.

ROWS(B$5:B5) ▶ It returns 1.

ROW(B$5:B$14)),ROWS(B$5:B5) ▶ Indicates that the formula works from the first column of the 5th row of the worksheet.

SMALL(IF(C$5:C$14<>””,ROW(C$5:C$14)),ROWS(C$5:C5)) ▶ Finds the smallest cell address among the 5th row of the datasheet.

Lastly, the formula checks whether there are any blank cells in the 5th row. If not, then it returns the whole row.

Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

 

  • Further, press the ENTER button, and you will get Smith as the output of the array functions.

Step 2:

  • Now drag the Fill Handle icon up to cell J5.

Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

  • Then drag it again up to cell J11. This will exclude all the rows having blank cells in them as in the picture below.

Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

Related Content: VBA to Count Blank Cells in Range in Excel (3 Methods)


7. Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

Now we all create another array formula that you can use as an alternative to the previous formula. This time, we will use the IF, COUNT, INDEX, SMALL, ROW, & ROWS functions to formulate the array formula. As we will keep the original data table aside, as we did in the previous method, you’ve to copy the table header to another place first.

Steps:

  • After that, select cell G5 to insert the following formula:
=IF(ROWS(B$5:B5)>COUNTA(B:B),"",INDEX(B:B,SMALL(IF(B$5:B$14<>"", ROW(B$5:B$14)),ROWS(B$5:B5))))
Formula Breakdown:

ROW(B$5:B$14) ▶ Tells that consider rows from the 5th to the thirteenth.

ROWS(B$5:B5) ▶ Indicates the first cell of the whole row.

INDEX(B:B,SMALL(IF(B$5:B$14<>””,ROW(B$5:B$14)),ROWS(B$5:B5))) ▶ Checks the 5th row for any blank cells. As there are no blank cells in the 5th row, it returns the first element of the 5th row which is Smith.

Finally, the formula checks for the blank rows within the entire table and returns rows where there’s no blank cell.

Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

  • Hence, press the ENTER button to execute the array functions, and you will get Smith as the output of the array functions.

  • After that, draw to autoFill Handle icon to column J first.

Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

  • After that, drag it again to the end of the CE This will keep only the solid rows by removing out all the rows having blank cells in them. That’s how you can skip blank rows using formula in excel.

Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

Related Content: If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)


8. Run a VBA Code to Skip Blank Rows in Excel

Last but not the least, we will skip blank rows from our dataset by applying the VBA Code. Applying the VBA code to skip blank rows is time-saving also. Let’s follow the instructions below to learn!

Step 1:

  • First of all, from your Developer tab, go to,

Developer → Visual Basic

Run a VBA Code to Skip Blank Rows in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Skip Blank Rows will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the Skip Blank Rows module pops up. In the Skip Blank Rowsmodule, write down the below VBA
Sub Skip_Blank_Rows()
Range("B5:F14").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Run a VBA Code to Skip Blank Rows in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 3:

  • Further, go back to your active worksheet, and you will be able to Skip Blank Rows in your active worksheet that has been given below screenshot.

Run a VBA Code to Skip Blank Rows in Excel

Related Content: Excel VBA: Check If Multiple Cells Are Empty (9 Examples)


Things to Remember

👉 #NAME error happens while typing incorrectly the range name.

👉 The #REF! error occurs when a cell reference is not valid.

👉 The FILTER function is available, only in Excel 365 version.


Conclusion

I hope all of the suitable methods mentioned above to skip blank rows using formula in excel will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo