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

If you are looking for ways to skip to the next cell if a cell is blank in Excel, then this article will serve this purpose. So, let’s start with the main article to know more about this task.


Download Practice Workbook


5 Ways to Skip to Next Cell If a Cell Is Blank in Excel

Here, we have the following dataset containing a list of Ids, Products, and their corresponding Sales values. In the Product ID column and Product column, we have some blank cells and using some formulas we will skip these blank cells and retrieve values from the next cells.

excel if blank then skip to next cell

For completing this article, we have used the Microsoft Excel 365 version, but you can use any other version at your convenience.


Method-1: Using IF Function to Skip to Next Cell If a Cell Is Blank in Excel

In this section, we are going to use the IF function to make a list of products for which they don’t have corresponding Product IDs. So, when we don’t have values in a cell of the Product ID column, then we are moving to the adjacent cell and then extract the values in the List column.

using IF function to Skip to Next Cell If a Cell Is Blank in Excel

Steps:

  • Type the following formula in cell E4.
=IF(B4="",C4,"")

Here, B4 is the Product ID, and C4 is the corresponding Product name. If cell B4 is blank, then it returns the product name Apple, otherwise a blank.

Finally, we are having the following results in the List column.

result due to Skip to Next Cell If a Cell Is Blank in Excel

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


Method-2: Using IF and ISBLANK Functions

Here, we will combine the name of the products which don’t have corresponding Product IDs in the List column using the combination of the IF and ISBLANK functions.

Using IF and ISBLANK functions to Skip to Next Cell If a Cell Is Blank in Excel

Steps:

  • Type the following formula in cell E4.
=IF(ISBLANK(B4),C4,"")

Here, B4 is the Product ID, and C4 is the corresponding Product name. If cell B4 is blank, then it returns the product name Apple, otherwise a blank.

  • Press ENTER and drag down the Fill Handle.

In this way, you will have a list of names of products without ids and we have extracted these names by skipping the blank cells of the Product ID column to the next cells of the Product column.

final list due to Skip to Next Cell If a Cell Is Blank in Excel

Read More: How to Skip a Column When Selecting in Excel (4 Easy Methods)


Method-3: Implementing IFS Function to Skip to Next Cell If a Cell Is Blank in Excel

In this section, we will skip the blank cells of the Product ID column and move to the next cell of the Product column to extract the name of the products and then gather them in the List column. For this purpose, here we will be using the IF, ISNA, and IFS functions.

Implementing IFS function to Skip to Next Cell If a Cell Is Blank in Excel

Steps:

  • Apply the following formula in cell E4.
=IF(ISNA(IFS(B4="",C4,C4="","")),"",IFS(B4="",C4,C4="",""))

Here, B4 is the Product ID, and C4 is the corresponding Product name.

Formula Breakdown

  • B4=””→ becomes
    • 123001=”” → if the value 123001 equates with a space, then it will return TRUE otherwise FALSE.
      • Output → FALSE
  • C4=””→ becomes
    • Apple=”” → if the value Apple equates with a space, then it will return TRUE otherwise FALSE.
      • Output → FALSE
  • IFS(B4=””,C4,C4=””,””) → becomes
    • IFS(FALSE, “Apple” ,FALSE,””) → returns #N/A error because of the FALSE argument
      • Output → #N/A
  • ISNA(IFS(B4=””,C4,C4=””,””)) → becomes
    • ISNA(#N/A) → TRUE
  • IF(ISNA(IFS(B4=””,C4,C4=””,””)),””,IFS(B4=””,C4,C4=””,””)) → becomes
    • IF(TRUE,””,#N/A) → returns blank
      • Output → Blank

combined formula to Skip to Next Cell If a Cell Is Blank in Excel

  • Press ENTER and drag down the Fill Handle.

Eventually, you will get a list of products without Product Ids in the List column.

Read More: How to Skip Every Other Column Using Excel Formula (3 Methods)


Method-4: Using a Combination of IFERROR, VLOOKUP, IF Functions

Here, we will search for the sales values in the Sales column, and then using the IFERROR, VLOOKUP, and IF functions we will skip the blank cells of the Product column and then move to the Sales column to extract the corresponding sales values.

Using a Combination of IFERROR, VLOOKUP, IF Functions

In the following datasheet in a new sheet, we will extract the values in the Sales column.

new sheet

Steps:

  • Apply the following formula in cell C4.
=IFERROR(IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&""="",IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&""="","",VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),""),"")

Here, B4 is the Product ID.

Formula Breakdown

  • VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE) → becomes
    • VLOOKUP(123001, Data!$B$3:$D$11,2, FALSE) → VLOOKUP will look for the value 123001 in the $B$3:$D$11 range of the Data sheet, the column index number is 2, and FALSE is for an exact
      • Output → Apple
  • VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=”” → becomes
    • Apple&””=”” → “Apple”=”” → FALSE
  • IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=””,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””) → becomes
    • IF(FALSE,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””) → returns a Blank due to the FALSE
      • Output → “”
  • IFERROR(IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=””,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””),””) → becomes
    • IFERROR(“”,””) → returns a Blank
      • Output → Blank

  • Press ENTER and drag down the Fill Handle.

Finally, we are getting the sales values for the blank products in the Sales column of the new sheet.

final result to Skip to Next Cell If a Cell Is Blank in Excel

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


Method-5: Applying IF and XLOOKUP Functions to Skip to Next Cell If a Cell Is Blank in Excel

In this step, we are going to apply the combination of the IF and XLOOKUP functions to get a product name for a blank cell of the Product column and the returned name of the products will be prior to the blank cell. Finally, we will filter these values with the help of the FILTER function.

Applying IF and XLOOKUP Functions

Steps:

  • First, keep the initial cell of the List column as Blank.

blank initial cell

  • Insert the following formula in cell D5.

=IF(B5=””,IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,-1),””),IF(B5<>””,””,IF(D4=””,””,B5)))

Formula Breakdown

  • B5=”” → becomes
    • “Orange”=”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
      • Output → FALSE
  • IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,-1) → becomes
    • IF(FALSE,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,-1)
      • Output → #N/A
  • B5<>”” → becomes
    • “Orange”<>”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
      • Output → TRUE
  • IF(D4=””,””,B5))) → becomes
    • IF(TRUE,””,B5)))
        • Output → “”
  • IF(B5=””,IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,-1),””),IF(B5<>””,””,IF(D4=””,””,B5))) → becomes
    • IF(FALSE, #N/A,””)
          • Output → Blank

  • Press ENTER and drag down the Fill Handle.

Finally, we are having the product names for blank cells of this column with names precedent of these blanks.

Ultimately, you can use the following formula to put the names together and remove the spaces.

=FILTER(D4:D11,D4:D11<>"")

Here, D4:D11 is the range on which we will be filtering the blanks.

filtering to Skip to Next Cell If a Cell Is Blank in Excel

Read More: How to Skip Columns in Excel Formula (3 Easy Ways)


Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

practice


Conclusion

In this article, we tried to show the ways to skip to the next cell if a cell is blank in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo