## Reverse a list ignoring blanks

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions.

#### Table of Contents

## 1. Reverse a list ignoring blanks

The image above demonstrates a formula in cell D3 that rearranges values, bottom value is now on top etc.

Formula in C2:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell D3

#### Step 1 - Find non-empty cells

$B$3:$B$14<>""

becomes

{"AA"; ""; "BB"; "CC"; ""; "BB"; "DD"; ""; "FF"; "GG"; ""; "HH"}<>""

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}

#### Step 2 - Replace TRUE with corresponding row value

The IF function returns the row number if cell is not blank. FALSE returns "" (nothing).

IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, {1;2;3;4;5;6;7;8;9;10;11;12}, "")

and returns

{1;"";3;4;"";6;7;"";9;10;"";12}

#### Step 3 - Find k-th largest row number

The LARGE function extracts the k-th smallest number from cell range or array. LARGE(array. k) The second argument contains ROWS($D$1:D1), it has an expanding cell reference that grows when cell is copied to cells below.

LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, 1)

and returns 12.

#### Step 4 - Return value

The INDEX function returns a value based on a row and column number. In this formula the cell range is only in one column, the row number is only needed.

INDEX($B$3:$B$14, LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1)))

becomes

INDEX($B$3:$B$14, 12)

and returns "HH" in cell D3.

### Get Excel *.xlsx file

Invert a list ignoring blanks.xlsx

## 2. Reverse a list ignoring blanks - Excel 365

This example shows an Excel 365 formula that extracts values in cell range B3:B14, ignores blank cells and returns the values starting from the bottom.

Excel 365 dynamic array formula in cell D3:

### 2.1 Explaining formula

#### Step 1 - Logical expression

The less than and larger than characters combined lets you filter non-empty values, the result is a boolean value TRUE or FALSE.

B3:B14<>""

becomes

{"AA"; ""; "BB"; "CC"; ""; "BB"; "DD"; ""; "FF"; "GG"; ""; "HH"}<>""

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}.

#### Step 2 - Filter list excluding blanks

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(B3:B14, B3:B14<>"")

becomes

FILTER({"AA"; ""; "BB"; "CC"; ""; "BB"; "DD"; ""; "FF"; "GG"; ""; "HH"}, {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE})

and returns

{"AA"; "BB"; "CC"; "BB"; "DD"; "FF"; "GG"; "HH"}.

#### Step 3 - Calculate the number of values in the array

The ROWS function returns the number of rows a given reference or array contains.

ROWS(*ref*)

ROWS(FILTER(B3:B14,B3:B14<>""))

becomes

ROWS({"AA"; "BB"; "CC"; "BB"; "DD"; "FF"; "GG"; "HH"})

and returns 8.

#### Step 4 - Create a sequential list of numbers from n to 1

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(ROWS(FILTER(B3:B14, B3:B14<>"")), , ROWS(FILTER(B3:B14, B3:B14<>"")), -1)

becomes

SEQUENCE(8, , 8, -1)

and returns {8; 7; 6; 5; 4; 3; 2; 1}.

#### Step 5 - Get values backwards

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(*array*, *[row_num]*, *[column_num], [area_num]*)

INDEX(FILTER(B3:B14,B3:B14<>""),SEQUENCE(ROWS(FILTER(B3:B14,B3:B14<>"")),,ROWS(FILTER(B3:B14,B3:B14<>"")),-1))

becomes

INDEX({"AA"; "BB"; "CC"; "BB"; "DD"; "FF"; "GG"; "HH"}, {8; 7; 6; 5; 4; 3; 2; 1})

and returns

{"HH"; "GG"; "FF"; "DD"; "BB"; "CC"; "BB"; "AA"}.

#### Step 6 - Optimize formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

INDEX(FILTER(B3:B14,B3:B14<>""),SEQUENCE(ROWS(FILTER(B3:B14,B3:B14<>"")),,ROWS(FILTER(B3:B14,B3:B14<>"")),-1))

FILTER(B3:B14, B3:B14<>"") is repeated three times in the formula.

x - FILTER(B3:B14, B3:B14<>"")

ROWS(FILTER(B3:B14, B3:B14<>"")) is repeated two times in the formula.

y - ROWS(x)

LET(x, FILTER(B3:B14, B3:B14<>""), y, ROWS(x), INDEX(x, SEQUENCE(y, , y, -1)))

## 3. Reverse a list ignoring blanks - manual steps

Create a list of numbers next to the list or table.

- Type 1 in the first cell.
- Type 2 in the next cell below.
- Select both cells.
- Press and hold with left mouse button on the dot in the lower right corner of the selection.
- Drag with mouse as far as needed.
- Release left mouse button.

- Select all cells, see the image above.
- Press CTRL + SHIFT + L to apply the Excel FILTER feature. Filter buttons appear at the top cells.

- Press with left mouse button on the right button, a popup menu appears.
- Press with mouse on "Sort Largest to Smallest".

- Press with left mouse button on the left button, and a popup menu appears.

- Press with left mouse button on the check box next to blanks to deselect it, see the image above.
- Press with left mouse button on the OK button.

Press CTRL + SHIFT + L to disable the Excel Filter. Delete the numbers next to the list.

### Sort values category

Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form