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

Our dataset often has blank cells. We can remove blank cells in Excel by using more than one formula.
We have a dataset of the employees of a company. It has three columns: ID, Name, and Department. We will be removing the blank cells using the formula.

Remove Blank Cells Excel Formula


Download Practice Workbook


7 Ways to Remove Blank Cells in Excel Using Formula

1. Using INDEX, SMALL, and IF Functions to Remove Blank Cells in Excel

Here, by using the INDEX function, SMALL function, ROWS function, and IF function we will remove blank cells in Excel.

For the first method, we’ve taken the ID column. We’ll remove cell B7.

Steps:

  • Firstly, type the following formula in cell D5.
=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))))

Remove Blank Cells in Excel Using Form

  • Secondly, press CTRL + SHIFT + ENTER as this is an array formula.

This is an Array formula. Hence, we need to press that. This will show us 1. The first non-blank cell in our selected range.

  • Finally, use the Fill Handle to AutoFill the other rows.

We’ve removed the blank cell from our data. Notice that, we’ve #Num error. We can ignore that, we’ll be seeing that in the next method.

Remove Blank Cells in Excel Using Form

Formula Breakdown

  • ROWS(D$5:D5)
  • COUNTA(B:B)
  • 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.

Read More: How to Remove Blank Cells in Excel (10 Easy Ways)


2. Using Excel Formula with IF, COUNTIF, INDEX, SMALL Functions to Remove Blank Cells

This time, we’ll be using the IF, INDEX, COUNTIF, ROW, ROWS, and SMALL functions to remove blank cells. Now, we’ve taken the Name column. We’ll be removing the blank cell from this using an Excel formula.

Steps:

  • Firstly, type the below formula in cell D5.
=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))))

Remove Blank Cells in Excel Using Form

  • After that, press CTRL + SHIFT + ENTER.

We’ve removed the blank cell. Notice there is no #NUM error this time around.

Remove Blank Cells in Excel Using Form

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.

Related Content: Find, Count and Apply Formula If a Cell is Not Blank (With Examples)


3. Applying Combined Functions to Remove Blank Rows in Excel Ignoring Error

In this method, we’ll be using IF, INDEX, MATCH, SMALL, ROW, and ISERROR functions to remove blank cells from our data. We’ve taken the Department column. We’ll remove cell B10.

Steps:

  • At first, select cell range D5:D12.
  • After that, type the formula taken from below.
=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)))

Remove Blank Cells in Excel Using Form

  • Then, press CTRL + SHIFT + ENTER.

We’ve removed cell B10.

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.

Read More: How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas


4. Utilizing ADDRESS, INDIRECT, COUNTBLANK, SMALL, and IF Functions to Remove Blank Cells

The IF, ADDRESS, INDIRECT, SMALL, ROW, ROWS, COLUMN, and COUNTBLANK functions will be used here to delete blank cells. We’ll be removing cell B9 from our Name column.

Remove Blank Cells in Excel Using Form

Steps:

  • Firstly, type the following formula in cell D5.
=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)))

  • Secondly, press CTRL + SHIFT + ENTER.

We’ll see the first non-blank value of cell B5 in cell D5.

  • Finally, use the Fill Handle to AutoFill the formula into other cells.

Remove Blank Cells in Excel Using Form

We can see that the blank cell is not there anymore. Therefore, our formula worked to remove blank in Excel.

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.

Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)


Similar Readings:


5. Remove Horizontal Blank Cells in Excel Using Formula Consisting of Combined Functions

Up to this, we’ve only demonstrated vertical removal of blank cells. We’ll use the INDEX, SMALL, IF, COLUMN, and SUM functions to remove the empty cell. In this method, we’re going to remove the cells horizontally.

Remove Blank Cells in Excel Using Form

Steps:

  • Firstly, type the following formula in cell C8.
=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))),""),"")

  • Secondly, press CTRL + SHIFT + ENTER.
  • Finally, use the Fill Handle to the right side to AutoFill the formula.

Remove Blank Cells in Excel Using Form

We have removed the blank cell using yet another formula in Excel.

Notice, there is #NUM! Error. We can remove that by using the IFERROR function.

  • Moreover, in addition to our previous formula, we can type this formula to remove the error.
=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))),""),"")

Remove Blank Cells in Excel Using Form

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.

Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)


6. Remove Blank Cells in Excel Using the COUNTBLANK Function

Our aim is to remove the blank cells. The COUNTBLANK function will be used here to achieve our goal. Firstly, we’ll count the number of blank cells in our dataset. Then, we’ll use the filter command and manually delete those rows.

Steps:

  • At first, create a helper columnBlanks”.

Remove Blank Cells in Excel Using Form

  • Then, type the following formula in cell E5.
=COUNTBLANK(B5:D5)

This formula will count the blank cells within that range.

There are 0 blanks in the B5:D5 cell range.

  • After that, use Fill Handle to AutoFill the formula down.

Remove Blank Cells in Excel Using Form

We can see the number of blank cells by using that formula.

Now we’ll turn our dataset into a table. To do that:

  • Firstly, select the dataset.
  • Secondly, from Insert tab >>> Table.

Remove Blank Cells in Excel Using Form

A dialog box will appear.

  • Then, select “My Table has headers“.
  • Finally, press OK.

We have our table. Notice, there is a Filter icon shown on each of the columns.

Remove Blank Cells in Excel Using Form

  • Then, select the Filter icon of the Blank column.
  • After that, select only 1.
  • Finally, press OK.

We’ll see only the filtered rows. We will remove these cells in the next step.

Remove Blank Cells in Excel Using Form

  • Firstly, select the cell range.
  • Then, Right-Click to bring up the Context Menu.
  • After that, Go to Delete >>> Entire Sheet Row.

We’ve removed the rows.

Remove Blank Cells in Excel Using Form

We can bring back the table by clearing the filter.

  • Firstly, select the filter icon.
  • Then select Clear Filter From “Blanks”.

Our dataset will look like this.

Remove Blank Cells in Excel Using Form

Further, we can remove the blank column to make our dataset smaller.

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


7. Applying the FILTER Function to Remove Blank Cells in Excel

We’ll be using the FILTER function to remove blank cells from our full dataset.

Remove Blank Cells in Excel Using Form

Steps:

  • Firstly, type the following formula in cell D5.
=FILTER(B5:D12,(B5:B12<>"")*(C5:C12<>"")*(D5:D12<>""))

We’re checking whether the columns are blank or not. Then we’re multiplying it with the other columns’ values. In our case, if one value is zero, then that row is ignored.

  • Then press ENTER.

Then, the data will be AutoFilled. After that, we’ll be getting a dataset without the blank cells.

Remove Blank Cells in Excel Using Form

However, if there was some text on the AutoFill range, then we would have gotten #SPILL! error. Therefore, we need to delete that in order to make our formula to remove blank cells in Excel.

Related Content: How to Find Blank Cells Using VBA in Excel (6 Methods)


Things to Remember

  • Firstly, the FILTER function is available only on Excel 365 and Office 2021
  • Secondly, we need to press CTRL + SHIFT + ENTER for the array formulas.
  • Thirdly, when invalid numeric values appear, #NUM! error will be shown. In this case, we can use the IFERROR function to ignore this problem.
  • Finally, #SPILL! Error: If there is any text in the AutoFill area, then this error will pop up. In order to get rid of that error, we need to remove that text. Moreover, you can read about that by clicking the error.

Practice Section

We’ve added a practice dataset for every method in the Excel file. So that, you can practice from there.

Remove Blank Cells Excel Formula


Conclusion

We’ve shown you 7 methods to remove blank cells in Excel using formula. However, If you have any problem understanding any of the methods, you can comment below for assistance.


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo