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.

**Table of Contents**hide

**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.

**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.

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

** **

**Step 2:**

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

** **

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

** **

**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.

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

**Step 2:**

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

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

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

**Similar Readings:**

**How to Delete Blank Cells in Excel and Shift Data Up****Apply Conditional Formatting in Excel If Another Cell Is Blank****How to Ignore Blank Cells in Range in Excel (8 Ways)****If Cell is Blank Then Show 0 in Excel (4 Ways)****How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas**

**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.

- 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.

** **

**Step 2:**

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

**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.

** **

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

** **

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

** **

**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)**

**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**.

**Step 2:**

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

- 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.

**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.

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

- 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**.

**Step 4: **

- Finally, we will only see the
**empty cells**.

- 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**.

- 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”**.

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

- 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**.

- Finally, we will see that we have
**removed all the blanks**from the**list**.

**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!!!

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?

Hello, TRYSTAN! Can you please email us your Excel file containing the dataset with the problem? Thanks.

Why not use the FILTER function? Perhaps combined with the UNIQUE function?

=UNIQUE(FILTER(B5:B14,B5:B14″”))

Hello NK,

It’s awesome that you have found another solution which is applicable by adding a simple symbol in the formula you have mentioned. Yes obviously it is possible to remove blank from lists using the combination of

UNIQUEandFILTERfunctions. The formula should be for the dataset of this article.=UNIQUE(FILTER(B5:B14,B5:B14<>“”))You need to add the symbol

<>extra.Here, the

FILTER functionis used to remove any blank values from the data.The

<>symbol is a logical operator that meansdoes not equal.The filtered data is returned directly to the

UNIQUE functionas the array argument. TheUNIQUE functionthen removes duplicates and return the final array.Thanks with Regards,

Towhid

Excel & VBA Content Developer