Blank lines in a Microsoft Excel spreadsheet are rather common phenomena. Most of them are redundant, and they take up additional space on the disorganized spreadsheet. As a result, users may want to skip those lines from Excel. Fortunately, Microsoft Excel provides a number of alternatives for removing blank rows from a spreadsheet. We may, however, use formulas to avoid them entirely. Today, in this post, we’ll discover practical strategies to skip blank lines/rows in Excel using formulas and other tools.

**Table of Contents**Expand

## How to Skip Lines in Excel: 4 Suitable Ways

You can use Excel functions, Excel tools, and suitable Excel formulas to skip blank rows or lines. Let’s move forward to learn all the methods suitable for different situations.

### 1. Excel Formula to Skip Lines

Assume we have a list of several **Students **and their **IDs **in the accompanying rows. We wish to segregate them into two columns. As a result, we will skip such rows utilizing various methods.

In this part of the article, we will see the following cases.

#### 1.1 Skip Every Other Row

We will use a formula that is a combination of Excel **INDEX **and **ROWS **functions. Apply the following simple steps.

**Steps:**

- First and foremost, we’ve added two new columns called
**Names**and**IDs**. We intend to divide the List into those two columns.

- In
**cell D5**, enter the following formula:

`=INDEX($B$5:$B$16,ROWS($E$5:E5)*2-1)`

- We’ll now drag the
**Fill handle**down to fill the following cells with the same formula.

- In
**cell E5**, enter the following formula:

`=INDEX($B$5:$B$16,ROWS($E$5:E5)*2)`

- For this time, we’ll drag the fill handle down to fill the following cells with the same formula.

- So, this is how the final range will appear.

**🔎**** How Does the Formula Work?**

**$B$5:$B$16:**array argument**ROWS($E$5:E5)*2:**row number argument**ROWS($E$5:E5)**= count number of rows, like:**$E$5**to**E5**returns 1;

**$E$5**to**E6**returns 2 and so on.**ROWS($E$5:E5)*2**=2 which means the cells under**Taylor.**

**An Alternative Way to Skip Every Other Row:**

You can also use **MOD **and **ROW** functions, then apply the **Filter** command to Skip Even or Odd Rows.

**Steps:**

- Enter the formula
in Cell`=MOD(ROW(B5),2)`

**D5**in a blank cell. Take a look at the image below:

- Now drag down the fill handle.
- Select the
**D4**cell and press the Filter icon under the**Data tab**.

- In Cell
**D4,**click the**Arrow,**uncheck**(Select All)**, and select**1**in the filter box. Look at the image below:

- Here is the final image with the names only. We can copy those names for further purposes.

#### 1.2 Skip Every N-th Row

Assume we have a list of many **IDs **in the rows below. But we want to divide them into two columns to distinguish **Every 3rd ID** and **Every 5th ID**. As a result, we will skip such rows using the methods listed below.

Now, execute the following steps to perform our desired task.

**Steps:**

- In
**cell D5**, enter the following formula:

`=INDEX($B$5:$B$16,ROWS($E$5:E5)*3)`

- This time, we’ll drag the fill handle down to fill the subsequent cells with the same formula.

- In
**cell E5**, enter the following formula:

`=INDEX($B$5:$B$16,ROWS($E$5:E5)*5)`

- Now, drag the fill handle icon to fill the next cells with the formula.

- So, this is how the final range will appear.

#### 1.3 Skip Lines Based on Value

Imagine we have a long list of names,** IDs**, and their **Presence**. However, we need the names of present pupils in a separate column. As a result, we will use the methods given below to skip such rows that include** “No”**.

**Steps:**

- In cell
**D5**, enter the following formula:

`=FILTER(B5:B10,D5:D10="Yes")`

- Now, we’ll drag the fill handle down to fill the following cells with the same formula.

- As a consequence, this is how the final range will be.

**Read More:** Excel Formula to Skip Rows Based on Value

### 2. Skip Every Other Line While Copying Formula

Assume we have a list of numbers 1 and 2. However, we require the product of Numbers 1 and 2 in odd rows. As a consequence, we will skip even rows using the methods listed below.

**Steps:**

- In
**cell E5**, enter the following formula:

`=INDEX($B$5:$B$16,1+2*(ROWS($E$5:E5)-1))*INDEX($C$5:$C$16,1+2*(ROWS($E$5:E5)-1))`

- Now, we’ll drag the fill handle down to fill the following cells with the same formula and get the product of odd rows.

**Note:**

We have used the product operation as an easy example. But you may have a more complex formula to use. Remember that, **INDEX($B$5:$B$16,1+2*(ROWS($E$5:E5)-1)) **will act as the input value with a formula. So, use this part accordingly matching with your case. If you fail to do so, leave us a comment with details. We will try to help.

**Read More:** How to Skip Cells in Excel Formula

### 3. Skip Blank Rows with FILTER Function

To easily filter out all the blank rows from an Excel spreadsheet, we will utilize **the FILTER function**. This function uses a dynamic array. That is, if you simply run this function in one cell, it will automatically cover all the associated cells where the formula result should be. Now, follow the procedures below to do this.

**Steps:**

- In cell H5, enter the following formula:

`=FILTER(B5:F13,(B5:B13<>"")*(C5:C13<>"")*(D5:D13<>"")*(E5:E13<>""))`

**🔎**** How Does the Formula Work?**

**(B5:B14<>””)**= Verifies the B column for empty cells.**(C5:C14<>””)**=Search for blank cells in the C column.**(D5:D14<>””)**= Identifies a blank cell in column D.**(E5:E14<>””)**= Looks in column E for any blank cells.- Lastly, the formula removes all blank cells by maintaining just the rows that do not have any blank cells.

- So, this is how the final range will be.

**Read More:** Skip to Next Result with VLOOKUP If Blank Cell Is Present

### 4. Skip Lines with Fill Handle Tool

Whenever dealing with data in Excel, users may see duplicate or undesired rows after each important row. There are several methods for copying every other row to a separate region while duplicating only the essential data.

**Steps:**

- Type a formula
**=B5**in**H5**that refers to the first cell in the range to be copied in a blank cell to the right of the rows to duplicate.

- All the information from the first row of the range is displayed after dragging the fill handle across the columns.

- As seen in the illustration, highlight the first row as well as the blank row just beneath it. Drag down with the mouse on the fill handle to autofill the range.

- So, this is the final range that we required.

**Read More: **How to Skip to Next Cell If a Cell Is Blank in Excel

**Download Practice Workbook**

You can download the practice workbook from the following download button.

## Conclusion

From time to time, users may need to eliminate empty rows from spreadsheets. Fortunately, Microsoft Excel gives a variety of choices, as seen in the preceding approaches. Feel free to follow those instructions and download the workbook to use for your own practice.

## Related Articles

- How to Skip Hidden Cells When Pasting in Excel
- Skip Cells When Dragging in Excel
- How to Skip Columns in Excel Formula
- Skip Every Other Column Using Excel Formula
- How to Skip a Column When Selecting in Excel

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