In this article, we will show you how to reverse order in Excel. We can reverse order vertically and horizontally in Excel. For this purpose, we can use the Sort feature or the SORTBY function and a combination of functions like INDEX, ROWS, and COLUMNS. Also, we can use VBA codes to reverse the data order.
Reversing the order in Excel means flipping the data, placing the bottom value at the top, and moving the top value to the bottom. We need to reverse the order for analysis and comparison or for processing data. The ability to reverse the order in Excel gives users an opportunity to manage data in a better way. I hope this article will help you to learn everything about reversing the order in Excel.
Download Practice Workbook
You can download and practice this workbook.
Reverse Order in Excel: Vertically and Horizontally
How to Reverse Data in Excel Vertically
1. Use Sort Feature from Data Tab to Reverse Column Order
In Excel, we can use the Sort feature to reverse the order of any dataset. We have taken a dataset that has different Products and the number of Unit Sold. With the Sort feature, we will reverse the order.
- First, we have to insert a Helper Column.
- Also, insert the serial number starting from 1.
- Now, select the data range D5:D14 (only serial numbers).
- Then, go to the Data tab and select the Sort option.
- A Sort Warning box will appear.
- Make sure Expand the selection is marked and click the Sort button.
- Another window named Sort will appear.
- We want to sort by the Helper Column on the basis of Cell Values from Largest to Smallest.
- So, check these three boxes if they are appropriately filled.
- Then, click on OK.
- After clicking OK, we will see the reverse order.
2. Reverse Order with INDEX, ROWS, and COLUMNS Functions
We can also reverse order with the INDEX function. For this method, we will have to include ROWS and COLUMNS functions to refer row and column index.
- Write the following formula in E6 and press Enter.
=INDEX($B$6:$C$15,ROWS(B6:$B$15),COLUMNS($B$6:B6))- You can see, we have got the last product name.
- Now, use Fill Handle to copy the formula to all cells.
- By doing so, we will get the whole dataset in reverse order vertically.
3. Reverse Order with SORTBY Function
The SORTBY function returns a range of values. You can use this method and can reverse the data order very quickly.
- Write the following formula in E6 and press Enter.
=SORTBY(B6:C15,ROW(B6:B15),-1)- This will reverse the whole data order vertically as shown in the following image.
4. Use VBA Code to Reverse Order in Excel
- First, select the data range B5:C14.
- Then, copy the following code in a Module and click on the Run button.
Sub Reverse_Vertically()
    Dim Rng As Range
    Dim Array_Rev() As Variant
    Dim i, j, Row_Num, Column_Num As Long
    Set Rng = Selection
    Array_Rev = Rng.Value
    Row_Num = Rng.Rows.Count
    Column_Num = Rng.Columns.Count
    For i = 1 To Row_Num \ 2
        For j = 1 To Column_Num
            Dim temp As Variant
            temp = Array_Rev(i, j)
            Array_Rev(i, j) = Array_Rev(Row_Num - i + 1, j)
            Array_Rev(Row_Num - i + 1, j) = temp
        Next j
    Next i
     Rng.Value = Array_Rev
End Sub
- And with this VBA code, we have reversed the data order vertically.
How to Reverse Data in Excel Horizontally
1. Use Sort Feature from Data Tab to Reverse Column Order
The Sort feature can also be used to reverse data orders horizontally. For this procedure, we have taken a dataset that is aligned horizontally.
- Now, we need Helper Row.
- So, add the Helper Column and insert serial numbers starting from 1.
- Now, select the whole dataset and go to the Data tab.
- Then, click on the Sort option.
- A Sort window will open.
- You have to click on the Options button.
- Then, select Sort left to right and press OK (this will allow you to sort horizontally).
- We have to sort by Row 6 on the basis of Cell Values from Largest to Smallest.
- Make sure, all these criteria are filled in the Row, Sort On and Order sections.
- Press OK to proceed.
- As a result, we will get the horizontally reversed data order as shown in the following image.
2. Reverse Order Horizontally with INDEX, ROWS and COLUMNS Functions
We can also use the INDEX function with ROWS and COLUMNS to reverse the data order horizontally. For this method, we have added the blank table to show the reverse order.
- Write the following formula in C8 and press Enter.
=INDEX($C$4:$G$5,ROWS($C$4:C4),COLUMNS(C4:$G$4))
- You can see, this returns the last Product which is Headphones.
- Now, using the Fill Handle, copy the formula to all other cells.
- In this way, we will get a horizontally reversed order for the whole dataset.
3. Reverse Order Horizontally with SORTBY Function
- Write the following formula in C8 and press Enter.
=INDEX($C$4:$G$5,ROWS($C$4:C4),COLUMNS(C4:$G$4))- And, we get the whole dataset in horizontally reversed order.
How to Reverse Name in Excel
We can also reverse the first and second names using a merged formula. For this method, we have to include SEARCH and LEN functions in the INDEX function. We have taken a dataset of famous GOT character names. We will reverse the names in the next column.
- Write the following formula in C5 and press Enter.
=MID(B5&" "&B5,SEARCH(" ",B5)+1,LEN(B5))- And you will see the first name has been reversed.
- Now use the Fill Handle to copy the formula to other cells.
- Doing so, all the names will be reversed as shown in the following image.
Things to Remember
- When you are using the Sort feature or the VBA code, the original data order could be lost. So, you should back up the original data order.
- While using the VBA code, be careful of selecting the data range. Don’t select the table header. It will mess up your data order.
- In case of using the complex formula, you should first understand them. Then you should use them in your data table.
Frequently Asked Question
1. Can I Reverse the Order of a Single Column without Copying and Pasting?
Yes, you can reverse the order of a single column without copying and pasting. You should use the Sort feature to solve the problem.
2. Is There Any Built-In Function to Reverse Order in Excel?
There is no such built-in function to reverse order in Excel. But, you can combine some functions like INDEX, ROWS, and COLUMNS and can reverse the order very easily.
3. Why Do We Need to Reverse Data Order in Excel?
We may insert data in the wrong order. To solve it, we need to reverse the data table. Also, we can reverse the order for data analysis and for the purpose of comparing.
Conclusion
Thank you for reaching this far. We have shown you all possible ways to reverse order in Excel. We hope you find the content of this article useful. If there are further queries or suggestions, you can leave them in the comment section. For further knowledge, you can visit our website ExcelDemy.com.
Excel Reverse Order: Knowledge Hub
<< Go Back to Sort in Excel | Learn Excel
