How to remove blanks from a list

by | May 21, 2018 | Excel Tips & Tricks

How to remove blanks from a list

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

  1. Begin by typing in = IFERROR( function to return an empty string when an error occurs
  2. Type in INDEX(
  3. Select or type in the range reference that contains your original list (i.e. $B$3:$B$11)
  4. Continue with a SMALL( function which will provide the row indexes of non-empty cells
  5. IF( to return an array that contains numbers and empty strings
  6. Type in ISTEXT($B$3:$B$11) to check whether the original list has blank cells or not
  7. Continue with ,ROW($A$1:$A$9),""), to give TRUE/FALSE conditions for the IF function
  8. Type in ROW(A1) to produce an incremental number for the array generated by the IF function
  9. 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 1st row and ends at 9th 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 1st 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.