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.

Learn the basics of Excel arrays

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:

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Download excel *.xls file

(Excel 97-2003 Workbook *.xls)

Recommended article

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

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 FREQUENCY function

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

INDEX function explained

Fetch a value in a data set based on coordinates.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

IF function explained

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

How to use Excel’s MATCH function

Identify the position of a value in an array.