Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks.

Answer:

In this blog post I´ll provide two solutions on how to remove blank cells and a solution on how to remove blank rows:

Remove blank cells (array formula)

Column B is the list with random blank cells. Column D is the list without the blank cells.

Array formula in cell D3:

=INDEX($B$3:$B$10, SMALL(IF(ISBLANK($B$3:$B$10), "", ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1), ROW(A1)))

How to create an array formula

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

How to copy array formula

  1. Copy (Ctrl + c) cell D3
  2. Paste (Ctrl + v) array formula on cell range D3:D8

Remove #num errors (excel 2007)

=IFERROR(INDEX($B$3:$B$10, SMALL(IF(ISBLANK($B$3:$B$10), "", ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1), ROW(A1))), "")

Download excel sample file for this tutorial:

remove-blanks_new.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

Remove blank cells (keyboard shortcut F5)

  1. Select range
  2. Press F5
  3. Click "Special"
  4. Click "Blanks"
  5. Click OK!
  6. Right click on one of the selected blank cells and select "Delete.."
  7. Click "Shift cells up"
  8. Click OK!

Remove blank rows (array formula)

Array formula in cell E2:

=IFERROR(INDEX(List, SMALL(IF(FREQUENCY(IF(List<>"", MATCH(ROW(List), ROW(List)), ""), MATCH(ROW(List), ROW(List)))>0, MATCH(ROW(List), ROW(List)), ""), ROW(A1)), COLUMN(A1)), "")

How to create an array formula

  1. Select cell E2
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell E2
  2. Copy cell (Ctrl +c)
  3. Select cell range E2:E10
  4. Paste

Download excel *.xlsx file

Remove blank rows from a cell range (formula).xlsx