How to Reverse a List or Range

This post will guide you how to reverse a list of values or a range of values in excel. How to reverse the order of a column data or a list of items using excel formula. How to reverse a range with sort command. How to reverse a list or column value with VBA.

Reverse a List or Range with Formula

If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula.

For example, to reverse the range A2:A5, you can use the following formula:

=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)

Let’s see how the above formulas works:

= COUNTA($A$2:$A$5)

reverse a list counta function1

The COUNTA function returns the number of cells in the range A2:A5.  The above COUNTA formula used the absolute reference as its arguments, so it won’t be changed when dragging Fill handle to other single cell.

 

=ROWS($C$2:C2)

reverse a list rows function2

The ROWS function returns the number of rows in the range $C$2:C2. The first Cell is an absolute reference and the second Cell is a relative reference in the argument of the ROWS function. So the second Cell reference can be changed when dragging the Fill handle.

 

= COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1

reverse a list3

The number of Cells in the range ($A$2:$A$5 subtracted the number of rows in the range $C$2:C2, then add 1, it returns the position of the last value in the range A2:A5. And it returns 4.

 

=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)

reverse a list4

We can use the INDEX function to extract the value based on the position result returned by the above COUNTA-ROWS formula.  So it returns value 403.

Next, we just need to drag the Fill Handler in Cell C1 to other single cells, such as: C2,C3,C4…

reverse a list5

You can also use the below two excel formula to reverse a list or a range:

=INDEX($A$2:$A$5,ROWS(C2:C$5))

reverse a list6

=INDEX($A$2:$A$5, COUNTA($A$2:$A$5) + ROW($A$2:$A$5)-ROW(),1)

reverse a list7

Reverse a List or Range with Sort command

You can also use the Sort command to reverse a list or a range in excel, for example, if you want to reverse the values in the range A2:A5, just refer to the following steps:

1# enter the value 1 into the cell B2, and then fill cells to B5 with a series by using the fill handle.

reverse a list sort command1

2# click any cell in the range B2:B5

3# click “Sort Z to A”command under Data tab.

reverse a list sort command2

4# delete the Column B, you will see that the range A2:A5 is reversed.

reverse a list sort command3

Reverse a List or Range with VBA

You can follow the below steps to create a new excel macro to reverse a list or range in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3#click “Insert” ->”Module” to create a new module.

How to split text string into an Array with VBA code2

4# paste the below VBA code into the code window. Then clicking “Save” button.

reverse a list with vba8

Sub ReverseRange()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Arr As Variant
    Dim i As Integer, j As Integer, k As Integer
    On Error Resume Next
    xTitleId = "ReverseColumnValue"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Arr = WorkRng.Formula
    For j = 1 To UBound(Arr, 2)
        k = UBound(Arr, 1)
        For i = 1 To UBound(Arr, 1) / 2
            xTemp = Arr(i, j)
            Arr(i, j) = Arr(k, j)
            Arr(k, j) = xTemp
            k = k - 1
        Next
    Next
    WorkRng.Formula = Arr
End Sub

5# back to the current workbook, then click Macros button under DEVELOPER tab, or Press F5 key to run the above macro.

reverse a list with vba9

6# click “RUN” button, then input the range value A2:A5 that you want to reverse.

reverse a list with vba10

7# let’s see that last result.

reverse a list with vba11


Related Formulas

  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula.you can use the following formula:=INDEX(A2:A5,MATCH(200,A2:A5)+1)…

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. And it returns the number of non-blank cells within a range or value. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The ROWS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROWS function is as below:= ROWS(array)…
Related Posts

How To Transpose Every N Rows of Data into Muliptle Columns in Excel
How to Transpose every N rows from one column to multiple2

This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel. Assuming that you have a list of ...

How To Increment Cell Reference by X Rows in Excel
increase cell reference by x rows2

This post will guide you how to increment cell reference by x Rows when you drag a formula to populate cells in a column in Excel. By default, when you drag the AutoFill Handle down to other cells , the ...

How to Get Row Number From a Vlookup in Excel
get row number from vlookup2

This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel. Assuming that you have a list of data in range ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

How to Move Every Other Row to a New Column in Excel
move every other row to new column7

This post will guide you how to move every other row to a new column in Excel. How do I move every odd row to another column with formula in Excel 2013/2016. How to move every even row to other ...

How to Convert Horizontal List of Data into Vertical in Excel
convert horizontal list to vertical6

This post will guide you how to transpose horizontal list to vertical list in your current worksheet in Excel. How do I convert horizontal rows to vertical columns with a formula in Excel 2013/2016. Convert Horizontal List to Vertical with ...

How to Get the Active Cell Address in Excel
get active cell address4

This post will guide you how to get the active Cell address with a formula in Excel. How do I return address of active cell with a VBA Macro in Excel. Get the Active Cell Address with Formula Get the ...

How to Convert Multiple Rows into a Single Row in Excel
convert multiple rows into a single row6

This post will guide you how to convert multiple rows or columns into a single row with a formula in Excel 3013/2016. How do I convert groups of rows to a single row of columns with VBA Macro in Excel. ...

How to Remove Numbers from Text in Excel
remove numbers from text2

This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers ...

How to Highlight Every Other Row Using Conditional Formatting in Excel
highlight every other row6

This post will guide you how to highlight every other row in Excel. How do I shade every other row with conditional formatting in Excel. Highlight Every Other Row Video: Highlight Every Other Row Highlight Every Other Row Assuming that ...

Sidebar