Return the non blank items in a list

You can display a list of all the non blank items in a single column by using an array formula.
This single cell formula can be entered in the first cell and then dragged down.
If you are not using Microsoft 365 this formula must be entered with (Ctrl + Shift + Enter).
If you are using Microsoft 365 this formula can be entered without Ctrl + Shift + Enter, thanks to Dynamic Array Formulas.


Cells "A1:A5" displays our list, which includes some blank cells.
Cells "B1:B4" displays our list without the blanks.
Enter the formula into cell "B1" and press Enter.
Drag this cell (and its formula) down to cells B2, B3 and B4.

 ABC
1oneone{=IFERROR(INDEX(A1:A5,SMALL(IF(NOT(ISBLANK(A1:A5)),ROW(A1:A5),""),ROW(A1))),"")}
2twotwo 
3 three 
4threefour 
5four  

Built-in Functions

IF - The value based on whether a certain condition is True or False.
IFERROR - The value or something else if it evaluates to an error.
INDEX - The value from a cell range which is the intersection of a row AND a column.
ISBLANK - The value True or False depending if the value is blank.
NOT - The opposite of a True or False value.
ROW - The row number of a cell reference.
SMALL - The kth smallest value in a list or array of numbers.


Alternative Array Formulas

There are several Different Array Formulas that could be used.


User Defined Function

NONBLANKVALUES - Returns an array of all the non blank items in a range.


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top