## Delete blanks and errors in a list

*Article last updated on August 25, 2017*

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

### Download excel sample file for this tutorial

remove-blanks.xls

(Excel 97-2003 Workbook *.xls)

### Functions used in this tutorial:

**SMALL(**array, k**)** returns the k-th smallest number in this data set.

**ROW(**reference**)**

returns the row number of a reference

**INDEX(**array;row_num;[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

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. […]

How to automatically fill all blanks with missing data or formula

Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]

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 […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form