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.

	A	B	C
1	one	one	{=IFERROR(INDEX(A1:A5,SMALL(IF(NOT(ISBLANK(A1:A5)),ROW(A1:A5),""),ROW(A1))),"")}
2	two	two
3		three
4	three	four
5	four

Built-in Functions

IF - The value based on whether a 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 boolean 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, table or cell range.

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.