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
- Reverse a List or Range with Sort command
- Reverse a List or Range with VBA
For example, to reverse the range A2:A5, you can use the following formula:
Let’s see how the above formulas works:
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.
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.
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.
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…
You can also use the below two excel formula to reverse a list or a range:
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.
2# click any cell in the range B2:B5
3# click “Sort Z to A”command under Data tab.
4# delete the Column B, you will see that the range A2:A5 is reversed.
1# click on “Visual Basic” command under DEVELOPER Tab.
2# then the “Visual Basic Editor” window will appear.
3#click “Insert” ->”Module” to create a new module.
4# paste the below VBA code into the code window. Then clicking “Save” button.
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.
6# click “RUN” button, then input the range value A2:A5 that you want to reverse.
7# let’s see that last result.
- 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)…
- 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)…