How to Reverse Order of Data in Excel (4 Useful Methods)

To illustrate, we have used 2 types of datasets in Excel; one for horizontally reversing, and the other one for vertically reversing, the data order that contains the Categories and Quantities of some Products.

Vertical Flip:

how to reverse order of data in excel

Horizontal Flip:

Method 1 – Reverse Order of Data Using Excel Sort Dialog Box

1.1 Column Order

Steps:

Type Order as the column heading in the column adjacent to Quantity.
Enter a series of numbers in the Order column (1, 2, 3 & 4) like in the screenshot below.

Select the total dataset (B4:D8).
Go to the Data tab.

Go to the Sort & Filter group and select the Sort option from there.

The Sort dialog box will pop up.
Select Order from the Sort by dropdown.
From the Order dropdown, choose Largest to Smallest.
Click OK to reverse the column order.

This will sort the data based on the values of the Order columns, reversing the order of the names in the data.

1.2 Row Order

Steps:

Enter Order as the row’s heading in the row below.
Enter a series of numbers (1, 2, 3 & 4) in the Order row.
Select the whole dataset.
Go to the Data tab.

From the Sort & Filter group, click on the Sort option.

The Sort dialog box will appear.
Click on Options in the Sort dialog box.

The Sort Options dialog box will open up. Select Sort left to right.
Click OK.

Go to the Sort dialog box and select Row 6 from the Sort by dropdown (the row that contains the Order of your dataset).
Select Largest to Smallest from the Order dropdown.
Click the OK button.

The result is a horizontal flip of the entire dataset (B4:F6).

Method 2 – Use Excel Data Tab to Reverse Order of a Table

Steps:

Select the values (D5:D8) under the Order column.
Go to the Data tab.

Click on the option (see the screenshot below) from the Sort & Filter group.

The Sort Warning dialog box will appear.
Select Expand the selection from the dialog box.
Click OK.

It will reverse the data order of an entire table.

Method 3 – Data Order Reversing with Excel Functions

3.1 SORTBY Function

Steps:

Copy the table headers (Product Category & Quantity) and paste them into the location (cells E4 & F4) where you want the reversed table.

Go to the cell (E5) of the left-most header.
Reverse the data order type the following formula in the cell:

=SORTBY($B$5:$C$8,ROW(B5:B8),-1)

Press the Enter key to get the final result like the screenshot below.

Here, the range $B$5:$C$8 indicates the contents of the whole dataset. The $ sign is for locking the range.

3.2 INDEX Function

Steps:

Place the column headers in the specific location just like the previous method.

To reverse the order of data, enter the following formula in cell E5:

=INDEX($B$5:$C$8,ROWS(B5:$B$8),COLUMNS($B$5:B5))

After pressing Enter, you will get the last content of the column.

Drag the fill handle both right & down to get the entire reversed table (E5:F8).

Method 4 – Apply VBA in Excel to Flip Data

4.1 Vertical Order

Steps:

Choose the B5:C8 data range.
Go to the Developer tab and select Visual Basic from the Code group.

The Microsoft Visual Basic for Applications window will open.
Go to the Insert tab and click on Module.

Hence a Code window will appear.
You need to insert the VBA code below in the Code window. Make sure to keep the cursor in the module before clicking the play button.

Sub Reverse_Vertical_Order()
Dim Initial_Row As Variant
Dim Last_Row As Variant
Dim First_Num As Integer
Dim Final_Num As Integer
Application.ScreenUpdating = False
First_Num = 1
Final_Num = Selection.Rows.Count
Do While First_Num < Final_Num
Initial_Row = Selection.Rows(First_Num)
Last_Row = Selection.Rows(Final_Num)
Selection.Rows(Final_Num) = Initial_Row
Selection.Rows(First_Num) = Last_Row
First_Num = First_Num + 1
Final_Num = Final_Num - 1
Loop
Application.ScreenUpdating = True
End Sub

It will reverse the order of data in the table successfully.

4.2 Horizontal Order

Steps:

Go to the Developer tab and select Visual Basic.

Select the Module from the Insert dropdown.

Enter the following VBA code and click the play button after keeping the cursor in the module.

Sub Reverse_Horizontal_Order()
Dim RowRange As Range
Dim RowArray As Variant
Dim Row1 As Integer, Row2 As Integer, Row3 As Integer
On Error Resume Next
RowTitleId = "Reversing Data Horizontally"
Set RowRange = Application.Selection
Set RowRange = Application.InputBox("Cell Range Selection", _
RowTitleId, RowRange.Address, Type:=8)
RowArray = RowRange.Formula
Application.ScreenUpdating = False
Application.Calculation = RowlCalculationManual
For Row1 = 1 To UBound(RowArray, 1)
Row3 = UBound(RowArray, 2)
For Row2 = 1 To UBound(RowArray, 2) / 2
RowArray_Temp = RowArray(Row1, Row2)
RowArray(Row1, Row2) = RowArray(Row1, Row3)
RowArray(Row1, Row3) = RowArray_Temp
Row3 = Row3 - 1
Next
Next
RowRange.Formula = RowArray
Application.ScreenUpdating = True
Application.Calculation = RowlCalculationAutomatic
MsgBox "Done"
End Sub

A window named Reversing Data Horizontally will pop up.
Select the data range (C4:F5) after keeping the cursor in the Cell Range Selection box.
Click OK.