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

**Table of Contents**hide

**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**k**th 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**k**th 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 theportion of our*value_if_false***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**k**th 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)**