We often find data in a raw, unorganized form that contain empty cells. In this article we’re going to show you how to remove blanks from a list and create a new table using only formulas.

## Syntax

=IFERROR(INDEX(list range,SMALL(IF(ISTEXT(list range),ROW(rows from 1 to count of list items),””),ROW(cell reference from first row))),””)

## Steps

- Begin by typing in
**=****IFERROR(**function to return an empty string when an error occurs - Type in
**INDEX(** - Select or type in the range reference that contains your original list (i.e.
**$B$3:$B$11**) - Continue with a
**SMALL(**function which will provide the row indexes of non-empty cells **IF(**to return an array that contains numbers and empty strings- Type in
**ISTEXT($B$3:$B$11)**to check whether the original list has blank cells or not - Continue with
**,ROW($A$1:$A$9),””),**to give*TRUE/FALSE*conditions for the IF function - Type in
**ROW(A1)**to produce an incremental number for the array generated by the IF function - Type in
**)),””)**to close the formula and add a space, then press**Ctrl + Shift + Enter**to complete this as an*array*formula

## How

To eliminate blank cells in a range, we need to pinpoint the cells with data, and distinguish them from the original list. Before we start, keep in mind that we’re going to be using an array formula to avoid creating unwanted helper columns, and prevent this issue with a single formula.

First, we need to find out the cells with values. The **ISTEXT** function returns *TRUE* or *FALSE* whether a cell contains a value or not. Using the **ISTEXT** with original list (ISTEXT($B$3:$B$11)) returns:

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

To convert these Boolean values into meaningful index numbers, we need to use the array as a condition check for the **IF** function, and place the **ROW** function with a range that starts from the 1^{st} row and ends at 9^{th} row that simply provides index numbers from 1 to 9. Empty string (“”) represents a FALSE condition. IF(ISTEXT($B$3:$B$11),ROW($A$1:$A$9),””) returns an array with row index numbers:

{1;2;””;””;5;6;””;8;9}

The next step is to sort the row indexes in our new array. The **SMALL** function comes in handy for this task, as it can return the *nth* smallest number from an array and Excel considers string values to be infinitely larger than any number. This is why the **SMALL** is preferable over the **LARGE** function. To provide an *n* value to the **SMALL** function, we return the **ROW** function again with a single cell that should be in 1^{st} row to return numbers from 1 and keep the reference as relative to increase its row number as well as return value. As a result, SMALL(IF(ISTEXT($B$3:$B$11),ROW($A$1:$A$9),””), ROW(A1)) formula returns a row index value that will be used by the **INDEX** function to return a value from a non-empty cell.

{1}

Then, we combine the **INDEX** function with our original list range reference to return values, and the **IFERROR** to wrap all everything else to avoid error messages.

=IFERROR(INDEX($B$3:$B$11,SMALL(IF(ISTEXT($B$3:$B$11),ROW($A$1:$A$9),””), ROW(A1))),””)

Finally, press the Ctrl + Shift + Enter key combination, instead of just the Enter key to enter this formula. This combination defines this formula as an array formula.