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.

How to Skip Blank Rows Using Formula in Excel: 8 Suitable Ways

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, SMALL, ROW, ROWS, INDEX, and COUNT functions in Excel. Here’s an overview of the dataset for today’s task.

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.

• Hence, a Go To window will appear in front of you. From that window, type the cell reference B5:F14 in the Reference box. 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.

Step 2:

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

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

Read More: How to Skip Cells in Excel Formula

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

• 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

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

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

Data → Sort & Filter → Filter

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

Read More: How to Skip Lines in Excel

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.

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

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

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

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

Read More: How to Skip Columns in Excel Formula

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,

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

Read More: Excel Formula to Skip Rows Based on Value

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.

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

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.

Read More: How to Skip a Column When Selecting in Excel

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.

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

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

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:

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

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

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

• 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 Rows module, write down the below VBA code.

Sub Skip_Blank_Rows()
Range("B5:F14").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

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

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.

Download Practice Workbook

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

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

• How to Skip Cells When Dragging in Excel
• How to Skip Hidden Cells When Pasting in Excel
• How to Skip Every Other Column Using Excel Formula
• Skip to Next Result with VLOOKUP If Blank Cell Is Present
• How to Skip to Next Cell If a Cell Is Blank in Excel

<< Go Back to Skip Cells  | Excel Cells | Learn Excel