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

Comments(0) Filed in category: Blank cells, Excel

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.

Comments(2) Filed in category: Count values, Excel

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

Comments(0) Filed in category: Excel

Download excel *.xls file

remove-blanks_new.xls
(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 […]

Comments(0) Filed in category: Blank cells, Excel, Missing values

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.

Comments(3) Filed in category: Excel

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

SMALL function and LARGE function

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

Comments(12) Filed in category: Excel

IF function explained

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

Comments(9) Filed in category: Excel

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel