Author: Oscar Cronquist Article last updated on December 08, 2010


Reverse a list ignoring blanks

Formula in C2:

=INDEX(List, LARGE(IF(List<>"", ROW(List)-MIN(ROW(List))+1, ""), ROWS($C$1:C1))) + CTRL + SHIFT + ENTER

copied down as far as necessary.

Named ranges
List (A2:A13)
What is named ranges?

How to implement array formula to your workbook
Change named range. Change $C$1:C1 to your start cell of your new list.

Download excel example file.
Invert a list ignoring blanks.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) Returns the rownumber of a reference

Returns a value or reference of the cell at the intersection of a particular range

ROWS(array) returns the number of rows in a reference or an array

LARGE(array,k) returns the k-th largest row number in this data set

Returns the smallest number in a set of values. Ignores logical values and text