How to Skip Lines in Excel (4 Suitable Ways)

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.


Download Practice Workbook

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


4 Ways to Skip Lines in Excel

You can use Excel functions, suitable Excel formulas, and other Excel tools to skip lines or rows. 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)

Skip Every Other Lines Using Excel Formula with INDEX & ROWS Functions

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

drag the Fill handle down

  • In cell E5, enter the following formula:
=INDEX($B$5:$B$16,ROWS($E$5:E5)*2)

Skip Every Other Lines Using Excel Formula

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

drag the fill handle down to fill the following cells

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

Created two lists with alternating lines skipped

🔎 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=MOD(ROW(B5),2)in Cell D5 in a blank cell. Take a look at the image below:

Enter the MOD-ROW formula in Excel to assign numbers to lines that we will skip

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

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:

select 1 in the filter box in Excel

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

the final image with the names only


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.

a list of many IDs

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)

Enter INDEX and ROWS functions to skip N-th lines in Excel

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

drag the fill handle down

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

drag the fill handle icon to fill the next cells

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

the final range


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

a sample dataset

Steps:

  • In cell D5, enter the following formula:
=FILTER(B5:B10,D5:D10="Yes")

Skip Lines in Excel Based on Value

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

the final range

Read More: Excel Formula to Skip Rows Based on Value (7 Examples)


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))

Skip Every Other Lines While Copying Formula

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

drag the fill handle down

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 (8 Easy Methods)


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.

Skip Blank Lines with Excel FILTER Function

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

the final range

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.

referring to the first cell

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

first row of the range is displayed after dragging the fill handle

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

select the first row and next row and drag the fill handle down to skip lines in between in Excel

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

the final range

Read More: How to Skip to Next Cell If a Cell Is Blank in Excel (5 Easy Ways)


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. Please visit ExcelDemy and post any questions, problems, or recommendations in the comments box.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo