In Excel, you might occasionally need to reverse the order of data, which means reversing the data order from left to right in a horizontal dataset and also inverting data upside down in a vertical dataset. There are various easy ways in Excel to do so. In this article, we are going to see some useful methods to reverse the order of data in Excel.
How to Reverse Order of Data in Excel: 4 Useful Methods
Here, we will learn 4 useful methods to reverse the order of data in Excel. For this, we have used 2 types of datasets in Excel; one is for horizontally and the other one is for vertically reversing the data order that contains the Categories and Quantities of some Products.
So, without further delay, let’s get started.
1. Reverse Order of Data Using Excel Sort Dialog Box
Here, we will use the Sort dialog box in Excel to reverse the order of data. It is available in the Data tab. The Sort dialog box is the most versatile technique for sorting your data because it allows you to select how you want the data arranged. In this method, we will use this tool both for the column and row order.
1.1 Column Order
The steps to reverse the column order using the Sort dialog box is below.
- First, type Order as the column heading in the column adjacent to Quantity.
- Secondly, enter a series of numbers in the Order column (1, 2, 3 & 4) just like the screenshot below.
- Thirdly, select the total dataset (B4:D8).
- After that, go to the Data tab.
- Then, go to the Sort & Filter group and select the Sort option from there.
- In turn, the Sort dialog box will pop up.
- Subsequently, select Order from the Sort by dropdown.
- Next, from the Order dropdown, choose Largest to Smallest. See the screenshot below.
- Finally, click OK to reverse the column order.
- In this way, the preceding steps would sort the data based on the values of the Order columns, reversing the order of the names in the data.
Read More: How to Reverse Column Order in Excel
1.2 Row Order
We can also reverse the row order by following the similar process in the above method. Using the Sort left to right feature from the Sort dialog box, we can easily sort the data horizontally. Let’s see the steps to reverse data horizontally of the dataset (B4:F6) below.
- In the beginning, enter Order as the row’s heading in the row below.
- Then, enter a series of numbers (1, 2, 3 & 4) in the Order row.
- Next, in addition to the Order row, select the whole dataset.
- After that, go to the Data tab.
- Now, from the Sort & Filter group, click on the Sort option.
- In turn, the Sort dialog box will appear.
- Afterward, click on Options in the Sort dialog box.
- Therefore, the Sort Options dialog box will open up and you need to click on the Sort left to right.
- Click OK.
- Again, go to the Sort dialog box and select Row 6 from the Sort by dropdown (the row that contains the Order of your dataset).
- Consequently, select Largest to Smallest from the Order dropdown.
- Click the OK button.
- Thus, the preceding steps would result in a horizontal flip of the entire dataset (B4:F6).
2. Use Excel Data Tab to Reverse Order of a Table
In this method, we will learn to use the Data tab in Excel to reverse the order of the table (B4:D8) below. Here, we need to add the Order column again like the previous methods. Let’s see the steps below.
- In the first place, select the values (D5:D8) under the Order column.
- Then, go to the Data tab.
- Now, click on the option (see the screenshot below) from the Sort & Filter group.
- Eventually, the Sort Warning dialog box will appear.
- At this moment, select Expand the selection from the dialog box.
- Afterward, click OK.
- In this way, we can reverse the data order of an entire table. See the final output in the picture below.
3. Data Order Reversing with Excel Functions
In this method, we will use the SORTBY function and the INDEX function in Excel to reverse the order of data.
3.1 SORTBY Function
Based on the values of a related range or array, the SORTBY function sorts the contents of a range or array. In this method, we will use the SORTBY function to reverse the data order of the below dataset (B4:C8) in Excel. Let’s see the steps below to do so.
- First, copy the table headers (Product Category & Quantity) and paste them into the location (cells E4 & F4) where you want the reversed table.
- Therefore, go to the cell (E5) of the left-most header.
- Then, to reverse the data order type the following formula in the cell:
- In the end, press the Enter key and thus you will 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
We usually use the INDEX function in Excel to return a value from a table or range, or a reference to a value. Here, we will use this function to reverse the order of data in the table (B4:C8) below. The steps to do so are below.
- First, place the column headers in the specific location just like the previous method.
- Eventually, to reverse the order of data, type the following formula in cell E5:
- After pressing Enter, you will get the last content of the column.
- Lastly, drag the fill handle both right & down to get the entire reversed table (E5:F8).
4. Apply VBA in Excel to Flip Data
In our earlier methods, we either utilized an Excel command or a function to reverse data. In our fourth method, we will now use the VBA code in Excel to reverse data both vertically and horizontally.
4.1 Vertical Order
First, we are going to utilize a VBA code in Excel to flip data vertically. However, to achieve the desired outcome, we must enter the proper command and sequence into the code. To gain a clear idea, follow the steps below.
- To begin with, choose the B5:C8 data range.
- Eventually, go to the Developer tab and select Visual Basic from the Code group.
- As a consequence, the Microsoft Visual Basic for Applications window will open.
- Next, 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
- In this way, we can reverse the order of data in the table successfully.
4.2 Horizontal Order
Our final step will be to reverse data using another VBA code. However, we will now horizontally invert the data. To do so, follow the steps below.
- First, go to the Developer tab and select Visual Basic.
- Second, select the Module from the Insert dropdown.
- Now 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
- Consequently, a window named Reversing Data Horizontally will pop up.
- Therefore, you need to select the data range (C4:F5) after keeping the cursor in the Cell Range Selection box.
- Click OK.
- Thus, the Microsoft Excel window will appear and you need to click OK.
- Finally, after running the code and choosing the cell range, the horizontally arranged data will be flipped.
Things to Remember
- Excel doesn’t have any built-in feature or tool to reverse the order.
- INDEX + ROWS combination will flip the order.
- The Sort option is the best and most straightforward of all the strategies available.
- Knowing VBA macros is necessary to comprehend VBA code.
Download Practice Workbook
Download the practice workbook from here.
I hope the above methods will be helpful for you to reverse the order of data in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.