Author: Oscar Cronquist Article last updated on November 16, 2018

In this blog post I will 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)))

Recommended article

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

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.
    1. Press Enter once.
  3. Release all keys.

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

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)

When you run out of values to show, the array formula above returns #NUM! errors. You can avoid this if you use the IFERROR function, however use it with great caution. It not only finds #NUM! errors but all errors. So if you formula or cells contain an error you won't see it, the IFERROR function removes that error too.

=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))), "")

You can read more about the IFERROR function here:

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

Download excel *.xls file

(Excel 97-2003 Workbook *.xls)

Recommended article

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

Functions in above array formula

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to use the MATCH function

Identify the position of a value in an array.