How to Remove Blanks from List Using Formula in Excel (4 Methods)

While working with a list of any kind i.e, a list of products or a list of employees you might come across a lot of blank cells in it. While such blank cells might be useful at times, mostly these blank cells could create a lot of problems for you making the list unnecessarily long and difficult to read. In this tutorial, I will show you how to remove blanks from the list formula in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


4 Easy Ways to Remove Blanks from List Using Formula in Excel

Let’s assume a scenario where we have an Excel worksheet that has a list of various computer hardwares and accessories. Problem is that the list has a lot of blank spaces in it.  We will use this list to show you how to remove blanks from the list using formulas in Excel. The image below shows the original list and the list after all the blanks are removed from it.

excel remove blanks from list formula


Method 1: Remove Blanks From Vertical List Using an Array Formula

We can use an array formula to remove the blanks from a vertical list very easily. Let’s see how we can do that.

Step 1:

  • First, we will write down the below formula in cell D5.
=INDEX($B$5:$B$14, SMALL(IF(ISBLANK($B$5:$B$14), “”, ROW($B$5:$B$14)-MIN(ROW($B$5:$B$14))+1), ROW(A1)))
Note: This is an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.

Formula Breakdown:

  • INDEX function will return the value of an element in a table or an array.
  • SMALL function will return the k-th smallest value in a dataset.
  • ISBLANK function returns TRUE if the cell reference used as the argument is blank, otherwise returns FALSE.
  • ROW function returns the row number of references used as the argument.
  • MIN function extracts the lowest or smallest value from a range of cells or cell references.

Remove Blank Cells Using an Array Formula

  • Then, upon pressing ENTER, the formula will return the first item of the list.

Remove Blanks From Vertical List Using an Array Formula

Step 2:

  • Now, we will drag the fill handle of cell D5 to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will get the list of all the products without any blank cells.

Remove Blanks From Vertical List Using an Array Formula

Read More: How to Set Cell to Blank in Formula in Excel (6 Ways)


Method 2: Perform an Array Formula to Remove Blanks From Horizontal List

Alternatively, we can also another array formula to remove blank cells from a horizontal list. We have to follow the below steps.

Step 1:

  • First, we will write down the below formula in cell B8.

=IF(COLUMN(B:B)<=SUM(–($B$6:$L$6<>””))+1,INDEX($B$6:$L$6,0,SMALL(IF($B$6:$L$6<>””,

COLUMN($B$6:$L$6)-1,””),COLUMN(B:B)-1)),””)

Note: This is an array formula. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.

Formula Breakdown:

  • IF function allows us to make logical comparisons between a value and what you expect.
  • SUM function adds all the numbers in a range of cells.
  • INDEX function will return the value of an element in a table or an array.
  • SMALL function will return the k-th smallest value in a dataset.
  • COLUMN function returns the column number of references used as the argument.
  • MIN function extracts the lowest or smallest value from a range of cells or cell references.


Remove Blank Cells Using an Array Formula

  • Then, upon pressing ENTER, the formula will return the first item of the list.

Perform an Array Formula to Remove Blanks From Horizontal List

Step 2:

  • Now, we will drag the fill handle of cell D5 horizontally to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will get the list of all the products without any blank cells.

Perform an Array Formula to Remove Blanks From Horizontal List

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


Similar Readings:


Method 3: Use the Go To Special Option to Remove Blanks From List

The easiest and most efficient way to remove blanks from a list is to use Go To Special menu. To do that, we have to follow the below steps.

Step 1:

  • First, go to Home > Editing > Find & Select and click on Go to Special. With the Go to Special feature, we can find and select cells based on what they are containing.

Use the Go To Special Menu Option to Remove Blanks From List

  • Alternatively, you can also press the F5 key to open the Go To menu. Then, click on the Special button on the down-left corner of that menu.

Use the Go To Special Menu Option to Remove Blanks From List

Step 2:

  • Next, we will select Blanks from the Go To Special.
  • Then, we will click OK.

Use the Go To Special Menu Option to Remove Blanks From List

Step 3:

  • Now, we will click on the Delete drop-down under the Home Then, we will select Delete Cells from the drop-down menu.

Use the Go To Special Menu Option to Remove Blanks From List

  • Then, a window titled Delete will appear. Next, we will select Shift cells up from that window.
  • Next, we will click on OK.

Use the Go To Special Menu Option to Remove Blanks From List

  • Finally, we will get the list of all the products without any blank cells.

Use the Go To Special Menu Option to Remove Blanks From List

Read More: Fill Blank Cells with Value Above in Excel (4 Methods)


Method 4: Remove Blanks from List Using the COUNTBLANK Function

Another way to remove blanks from the list is to use the COUNTBLANK function and the table filter together. Let’s see how we can do that. 

Step 1:

  • First, we will write down the below formula in cell D5.

=COUNTBLANK(B5)

 Remove Blanks from List Using the COUNTBLANK Function

Formula Breakdown:

The COUNTBLANK formula will count the blank cells within that range.

  • Upon pressing ENTER, we will see that the formula returns zero (0) as cell B5 is not empty rather contains the text Keyboard

 Remove Blanks from List Using the COUNTBLANK Function

Step 2:

  • Then, we will drag the fill handle of cell C5 downward to apply the formula to the rest of the cells.

 Remove Blanks from List Using the COUNTBLANK Function

  • Finally, we will see that the formula returns zero (0) for the cells containing text, and one (1) for those that are empty in the Product column. 

 Remove Blanks from List Using the COUNTBLANK Function

Step 3: 

  • Now, we will select all the cells in the data range including the column header
  • Then, we will select Table under the Insert ribbon. 

 Remove Blanks from List Using the COUNTBLANK Function

  • Now, a window titled Create Table will appear. We will select the My Table has headers option. 
  • Then, we will click on OK

 Remove Blanks from List Using the COUNTBLANK Function

  • Now, we will see that our data range has been formatted as a table. Notice, there is a Filter icon shown on each of the columns.
  • Then, we will select the Filter icon of the Blanks column.
  • After that, select only 1.
  • Finally, press OK.

COUNTBLANK Function

Step 4: 

  • Finally, we will only see the empty cells

 Remove Blanks from List Using the COUNTBLANK Function

  • Now, select the entire data range.
  • Then, right-click on any cell in the Blanks column to bring up the Context Menu.
  • After that, go to Delete >>> Entire Sheet Row.

COUNTBLANK Function

  • Now, we will see that all the blanks have been removed. 
  • Next, we will bring back the table by clearing the filter. 
  • First, select the filter icon.
  • Then, select Clear Filter From “Blanks”.

 Remove Blanks from List Using the COUNTBLANK Function

  • Finally, we will now only see the rows that contain text in the Product column. 

COUNTBLANK Function

  • Further, we can remove the blank column to make our dataset smaller. First, we will right-click on any cell in the Blanks column to bring up the Context Menu.
  • Then, we will click on Delete>Table Columns

 Remove Blanks from List Using the COUNTBLANK Function

  • Finally, we will see that we have removed all the blanks from the list

COUNTBLANK Function

Read More: How to Delete Empty Cells in Excel (6 Methods)


Quick Notes

🎯 The two formulas we have used are array formulas. So, you must press CTRL+SHIFT+ENTER together to insert any of these formulas in a cell. And it will put two curly braces around the whole formula.

🎯 Also, if you are looking for how to remove blank spaces in Excel, you can read this article. And also read this one to learn how to add blank spaces in Excel.


Conclusion

In this article, we have learned how to remove blanks from the list formula in Excel. I hope from now on you can remove blanks from the list very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

1 Comment
  1. Hi – I tried this and at first all I got in the cell with the formula was the #NAME error. I double-clicked the cell to mess around with the formula, changed nothing, hit enter, and then it worked for the first item. When I propagated the formula, though, every other cell returned an error: #NAME? or #NUM!. It would be AMAZINGLY AWESOME to get this formula to work because there are many situations where I would love to have a formula that automatically updates and removes blank lines from a series of cells but I have no idea how to fix it. Here is the formula I put into my worksheet:

    =INDEX($C$8:$C$55, SMALL(IF(ISBLANK($C$8:$C$55), “”, ROW($C$8:$C$55)-MIN(ROW($C$8:$C$55))+1), ROW(A1)))

    My data are in column C from C8 to C55. They are:

    C8 = Housing
    C9-C11 = blank
    C12 = Vehicles
    C13-15 = blank
    C16 = Gas
    C17 = blank
    C18 = Utilities
    C19-23 = blank
    C24 = Food
    C25-27 = blank
    C28 = Party
    C29 = blank
    C30 = Saving
    C31-33 = blank
    C35 = Carl
    C35-38 = blank
    and so on. I can add more if you need it.

    To be fair, although I have minor experience with coding (C++ and Python), INDEX functions in Excel are like hieroglyphics to me; I just don’t get it. What am I doing wrong? Why will it work for the first cell but when I propagate (drag the fill button as it says above) does it stop working?

Leave a reply

ExcelDemy
Logo