How to Remove Blank Cells Using Formula in Excel (7 Methods)

Formula Breakdown

• ROWS(D$5:D5)
  • Output: 1
  • Explanation: The ROWS function returns us the row number of a cell range.
• COUNTA(B:B)
  • Output: 9
  • Explanation: The COUNTA function counts non-blank cells.
• ROWS(D$5:D5)>COUNTA(B:B)
  • Output: 1>9, which is FALSE.
  • Explanation: Our IF formula was set to show blank, for TRUE We’ve gotten FALSE so it will execute the second part of the code. It will execute the part INDEX(B:B,SMALL(IF(B$5:B$12<>””,ROW(B$5:B$12)),ROWS(D$5:D5))) of the code.
• ROW(B$5:B$12)
  • Output: {1;2;0;4;5;6;7;8}
• B$5:B$12<>””
  • Output: {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}
  • Explanation: This part is comparing the cells if it is not blank. The third value is showing FALSE. That means it is blank.
• IF(B$5:B$12<>””,ROW(B$5:B$12))
  • Output: {5;6;FALSE;8;9;10;11;12}
• SMALL(IF(B$5:B$12<>””,ROW(B$5:B$12)),ROWS(D$5:D5)) -> becomes,
  • SMALL({5;6;FALSE;8;9;10;11;12},1)
  • Output: 5
  • Explanation: The SMALL function shows us the smallest number of an array. Our k value is 1. This will mean we want the smallest number. That is 5.
• INDEX(B:B,5)
  • Output: B5
  • Explanation: The INDEX function returns a value from a range. We’ve defined the range as B:B. From that range, row 5 will be displayed.

Our formula becomes this:

• IF(FALSE,””,1)
  • Output: 1
  • If TRUE then blank was to be shown. But we got FALSE. Hence, the value 1 was shown.

• To summarize: our formula was
  • IF(ROWS(D$5:D5)>COUNTA(B:B),””,INDEX(B:B,SMALL(IF(B$5:B$12<>””,ROW(B$5:B$12)),ROWS(D$5:D5))))

We reduced this to

• IF(FALSE,””,1)

Finally, we got our value as 1.

Formula Breakdown

We’ve two main parts in our formula. The first part is COUNTIF($B$5:$B$12,”?*”)<ROW(B5)-4,””. The second one is INDEX(B:B,SMALL(IF(B$5:B$12<>””,ROW(B$5:B$12)),ROWS(B$5:B5))).

Our formula is similar to our formula from method 1.

We’ve made slight changes in two areas. The first one is on:

• COUNTIF($B$5:$B$12,”?*”)
  • Output: 7.
  • Explanation: The COUNTIF function counts cells that satisfy predefined criteria. Here, the range is $B$5:$B$12. The dollar sign ($) is given to make it an absolute cell The criteria is set as “?*”. This is a wildcard character. Basically, we’re counting the rows using non-blank text. If we count, we can see that we’ve 7 Names in our data.

The second portion that is slightly changed is:

• ROW(B5)-4
  • Output: 1
  • Explanation: The ROW function returns the row number of a cell. Our empty cell is at position 5 from cell B5. We are subtracting 4 because we want it to be less than that.
• Our main formula was: IF(COUNTIF($B$5:$B$12,”?*”)<ROW(B5)-4,””,INDEX(B:B,SMALL(IF(B$5:B$12<>””,ROW(B$5:B$12)),ROWS(B$5:B5))))

Then we reduced it to simple form as: IF({FALSE},””,INDEX(B:B,5))

Finally, we got the value from cell B5.

Formula Breakdown

Our formula has many parts. Therefore, we’re going to break it down for you.

• ISERROR(SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4))
• IF(B5:B12<>””,ROW(B5:B12)-4) is the array of our SMALL
  • Output: {1;2;3;4;5;FALSE;7;8}
  • Explanation: Our IF function is checking whether the cells are not equal to blank. If it is TRUE, then the ROW function part executes. It returns the row number of a cell. We’re subtracting 4 to match our first cell B1 in the data.
  • ROW(B5:B12)-4
    • Output: {5;6;7;8;9;10;11;12}-4
  • Now, SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4) becomes:
  • SMALL({1;2;3;4;5;FALSE;7;8},{1;2;3;4;5;6;7;8})
    • Output: {1;2;3;4;5;7;8;#NUM!}
    • Explanation: The SMALL function returns the kth smallest value. Here, the value of k is 1 for cell B5, 2 for cell B6 and so on. Hence, we’ll get the smallest value.
  • ISERROR(SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4)) will show this output:
    • Output: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
  • Now, our second part of the formula: INDEX(B5:B12,MATCH(SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4),IF(B5:B12<>””,ROW(B5:B12)-4),0))
  • The INDEX function returns the value of a certain array or cell We’ve used the MATCH function to define our row number.
  • SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4) this formula becomes:
  • SMALL({1;2;3;4;5;FALSE;7;8},{1;2;3;4;5;6;7;8})
    • Output: {1;2;3;4;5;7;8;#NUM!}
    • Explanation: The SMALL function returns the kth smallest value. Here, the value of k is 1 for cell B5, 2 for cell B6 and so on.
  • Lookup array for the MATCH function is: IF(B5:B12<>””,ROW(B5:B12)-4)
    • Output: {1;2;3;4;5;FALSE;7;8}
    • Explanation: We’ve used the IF and ROW Our IF function is checking whether the cells are not equal to blank. If it is TRUE, then the ROW function part executes. It returns the row number of a cell. We’re subtracting 4 to match our first cell B1 in the data.

Now, our INDEX formula will reduce to:

• INDEX(B5:B12,{1;2;3;4;5;7;8;#NUM!}) -> becomes,
• INDEX({“Accounting”;”Engineering”;”Marketing”;”Support”;”Marketing”;0;”Engineering”;”Support”},{1;2;3;4;5;7;8;#NUM!})
  • Output: “Accounting”
  • Explanation: The INDEX function has returned the first value for our first cell.
• Now our initial formula was: IF(ISERROR(SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4)),””,INDEX(B5:B12,MATCH(SMALL(IF(B5:B12<>””,ROW(B5:B12)-4),ROW(B5:B12)-4),IF(B5:B12<>””,ROW(B5:B12)-4),0)))
• Then it reduced to: IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},””,”Accounting”)
• Our formula will work up to 7th Then the ISERROR function will dismiss the #NUM! error.

Formula Breakdown

Firstly, we’ve got this logical test in our IF function.

• ROW()-ROW($B$5:$B$12)+1>ROWS($B$5:$B$12)-COUNTBLANK($B$5:$B$12)
• ROW ()
  • Output: {5}
  • Explanation: Returns the current row
• ROW($B$5:$B$12)+1
  • Output: {6;7;8;9;10;11;12;13}
  • Explanation: We’re adding 1 with all the row values in our range.
• ROWS($B$5:$B$12)
  • Output: 8
  • Explanation: Returns the total number of rows in the cell
• COUNTBLANK($B$5:$B$12)
  • Output: 1
  • Explanation: This function counts the number of blanks in the cell

After that, the logical test portion will show this:

• Output: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. We’ll go to the value_if_false portion of our IF
• The second part is: INDIRECT(ADDRESS(SMALL((IF($B$5:$B$12<>””,ROW($B$5:$B$12),ROW()+ROWS($B$5:$B$12))),ROW()-ROW($D$5:$D$12)+1),COLUMN($B$5:$B$12),4))

We can break it into SMALL, ADDRESS, and INDIRECT functions.

• SMALL((IF($B$5:$B$12<>””,ROW($B$5:$B$12),ROW()+ROWS($B$5:$B$12))),ROW()-ROW($D$5:$D$12)+1) -> becomes,
• SMALL((IF({TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE},{5;6;7;8;9;10;11;12},{13})),ROW()-ROW($D$5:$D$12)+1) -> becomes,
• SMALL({5;6;7;8;13;10;11;12},ROW()-ROW($D$5:$D$12)+1) -> becomes,
• SMALL({5;6;7;8;13;10;11;12},{1;0;-1;-2;-3;-4;-5;-6})
  • Output: {5;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
  • Explanation: The SMALL function returns the kth smallest value. Here, the value of k is 1 for cell B5.. Hence, we’ll get the smallest value. There is no 0th or -1th smallest number, hence, we’re getting the #NUM!

Now our formula reduce to:

• ADDRESS({5;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!},COLUMN($B$5:$B$12),4) -> becomes,
• ADDRESS({5;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!},{2},4)
  • Output:{“B5”;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
  • Explanation: The ADDRESS function returns the cell reference number of a cell. Here 4 is used to return relative cell We’ve gotten the cell reference B5 from this function.

Now we’ll use the INDIRECT function. Our formula is reduced to:

• INDIRECT({“B5”;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
  • Output: {“Lucretia Muckle”;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
  • Explanation: The INDIRECT function is used to return the reference predefined by a text. We’ve returned the reference of cell B5.

Let’s summarize our formula:

• First we got this: IF(ROW()-ROW($B$5:$B$12)+1>ROWS($B$5:$B$12)-COUNTBLANK($B$5:$B$12),””,INDIRECT(ADDRESS(SMALL((IF($B$5:$B$12<>””,ROW($B$5:$B$12),ROW()+ROWS($B$5:$B$12))),ROW()-ROW($D$5:$D$12)+1),COLUMN($B$5:$B$12),4)))
• Then, IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},””,{“Lucretia Muckle”;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!})
• Finally, We got our first value as “Lucretia Muckle”.

In conclusion, that’s how this formula works.

Formula Breakdown

We’ve implemented the IFERROR function to get rid of the !NUM error. Our formula is mainly two parts. The first one is logical test:

• COLUMN(B:B)<=SUM(–($B$4:$H$4<>””))+1 -> becomes,
• {2}<=SUM(–{TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,FALSE})+1
  • Output: {2}<=6
  • Explanation: The COLUMN function returns the column number of a cell. The SUM function is adding all the values that are non-blank. It cannot do its operation on texts. So, we’re putting double minus in front of this function. Then we’re adding 1 to it. Ultimately, we’re getting the output TRUE.

In this time, our value_if_true portion of the IF function will execute:

• INDEX($B$4:$H$4,0,SMALL(IF($B$4:$H$4<>””,COLUMN($B$4:$H$4)-1,””),COLUMN(B:B))) -> becomes,
• INDEX($B$4:$H$4,0,SMALL(IF({TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,FALSE},{1,2,3,4,5,6,7},””),COLUMN(B:B))) -> becomes,
• INDEX($B$4:$H$4,0,SMALL({1,2,3,4,””,6,””},{2})) -> becomes,
• INDEX($B$4:$H$4,0,{2})
  • Output: “Accounting“
  • Explanation: The SMALL function returns the kth smallest value. Here, the value of k is 2. Hence, we’ll get the second smallest value, which is {2}. The INDEX function returns the output of a cell We have shown the second value of our range using this formula.

Let’s summarize this for even better understanding:

Our formula was this:

IFERROR(IF(COLUMN(B:B)<=SUM(–($B$4:$H$4<>””))+1,INDEX($B$4:$H$4,0,SMALL(IF($B$4:$H$4<>””,COLUMN($B$4:$H$4)-1,””),COLUMN(B:B))),””),””)

Then we reduced it to, IFERROR(IF({TRUE},”Accounting”,””),””)

Finally, we removed the blank cells using this formula.