## Delete blanks and errors in a list

The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they all will be presented in a new column.

### Array formula in cell D3:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### How to copy array formula

- Copy (Ctrl + c) cell D3
- Paste (Ctrl + v) array formula on cell range D3:D11

### Explaining formula in cell D3

#### Step 1 - Identify blank cells

The ISBLANK function returns TRUE if cell is blank (empty) and FALSE if not.

ISBLANK($B$3:$B$20)

becomes

ISBLANK({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})

and returns

{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}

#### Step 2 - Identify errors

The ISERROR function returns TRUE if cell contains an error and FALSE if not.

ISERROR($B$3:$B$20)

becomes

ISERROR({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}

#### Step 3 - Add arrays

If at least one of the boolean values is TRUE then the result must be TRUE, addition is what we need to use.

Boolean |
Boolean |
Multiply |
Add |

FALSE | FALSE | 0 (zero) | 0 (zero) |

FALSE | TRUE | 0 (zero) | 1 |

TRUE | TRUE | 1 | 2 |

ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20)

becomes

{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE} + {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}

returns {0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}.

#### Step 4 - Convert array to row numbers

The IF function lets you use a logical expression to determine which value (argument) to return.

IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20)))

becomes

IF({0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}, "", {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})

and returns

{1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}

#### Step 5 - Get k-th smallest row number

To be able to return a single value from the array we need to use the SMALL function to extract a single row number. The second argument in the SMALL function uses the ROWS function with an expanding cell reference to extract a new value in each cell.

SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1))

becomes

SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, ROWS($A$1:A1))

becomes

SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, 1)

and returns 1.

#### Step 6 - Return value based on row number

The INDEX function returns a value from a cell range based on a row and column number, our cell range is a single column so we need to only specify a row number in order to get the correct value.

INDEX($B$3:$B$20, SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$20, 1)

becomes

INDEX({2;4;0;3;"AA";0;"CC";0;5;#REF!;0;#DIV/0!;0;"ZZ";"DD";0;7;#NUM!}, 1)

and returns 2 in cell D3.

### Get excel *.xls

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

How to find errors in a worksheet

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]

How to quickly select blank cells

In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]

### 2 Responses to “Delete blanks and errors in a list”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Thank you! This is my favorite excel formula so far.

Hi , I'm having trouble getting your formula to work properly in my sheet. Your formula works fine in your sheet but when I copy it into mine I cannot get **** excel to copy it while incrementing the row1:1 portion of the formula. I can copy the first cell identically into all cells in the column but that is no good. If I try to edit it I just get an error telling me I can't edit an array fomula. If I try to use "fill down" then the first cell works fine but all the others in the column return NUM or VAL errors! How the flip do I get **** excel to copy the formula down a column while incrementing the ROW1:1 to ROW2:2 etc?

This is driving me nuts!

Running windoze 10 home with whichever version of excel that shipped with.

Your formula does pretty much exactly what I need which is great- if I can just get it to increment those row numbers!

Help gratefully received!