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.
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))))
- 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.
- 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.
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))))
- After that, press CTRL + SHIFT + ENTER.
We’ve removed the blank cell. Notice there is no #NUM error this time around.
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)))
- 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.
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.
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:
- How to Fill Blank Cells with 0 in Excel (3 Methods)
- How to Make Empty Cells Blank in Excel (3 Methods)
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Find and Replace Blank Cells in Excel (4 Methods)
- Null vs Blank in Excel
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.
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.
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))),""),"")
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 column “Blanks”.
- 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.
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.
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.
- 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.
- 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.
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.
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.
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.
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.
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
- Excel VBA: Find the Next Empty Cell in Range (4 Examples)
- Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
- Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
- If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)