How to Reverse Order in Excel: 5 Effortless Methods

Don’t like the data order of your Excel spreadsheet? Find out how to reverse order in Excel using multiple foolproof methods.

Reversing the Excel data order might sound simple and a one-click task to you, but it isn’t. The traditional sort function of MS Excel can sort the values on a column based on alphabetical or numeric order. But, what you want to achieve via data reverse is the flipping of data from its original order.

Worry no more as I’ll tell you how to reverse order in Excel. Using the step-by-step instruction for the methods I’m about to describe, you can easily reverse the current data order of an Excel column. Later, you can also use reversed data for different purposes.

Reasons to Reverse Order of Data in Excel

• If you have imported data from another application or resource into Excel, and the data is appearing in the wrong sequence, reversing the order will help you rearrange it in the intended sequence.
• If the default data order isn’t suitable for your charts and graphs, you need to reverse the order for better data visualization.
• Data order reversing is also necessary to format the data in your preferred format or structure.
• While using Pivot tables for data analysis, you may need to align the Excel data with the default order of the pivot table by reversing it.
• With hierarchical data, like organizational charts or decision trees, you can reverse the data order to reflect on the relationships or dependencies in a better way. It also helps with easy navigation and clarity.
• Apart from the above reason, you may just want to reverse the data order in Excel for personal preference or convenience.

Reverse Order of Data in Excel Using the Sort Tool

The Sort tool is the basic command on Excel to reverse the order of your dataset. If there’s a list of numerical values in ascending or descending order, the task becomes easier. If there aren’t any values besides the text values in a column, you can create a helper column to flip the data order and then delete the undesirable column.

Considering there isn’t any serial number for the items that need to be reversed in a column, Here are the quick steps you can follow:

1. Type Helper in the topmost cell of the column to the left of the target column.
2. Enter 1, 2, and 3 against the first, second, and third text values just below the Helper column.
3. Now, drag the fill handle down the column to populate serial numbers for all the text values in the column on the left of the Helper column.

4. Now, select the entire dataset including the column headers.
5. Press Alt > H > S > U to bring up the Custom Sort or Sort dialog box.
6. There, click the Sort by drop-down list and select Helper.
7. Select Largest to Smallest by clicking the Order drop-down list.
8. Hit the OK button to reverse the order of the existing dataset by the values in the Helper column.

9. Now, right-click the Helper column header and click Delete in the context menu to erase the Helper column.

Excel Reverse Order Using the SORT Function

If the above method seems time-consuming when you need to order data in a column in reverse order, you can use the SORTBY function in Excel.

However, the SORTBY formula isn’t available on Excel desktop apps earlier than Excel 2021. Fortunately, the function is available in Excel for the web. Find below the steps to use this function to flip data:

1. Highlight any empty cell as the starting point for the reversed data order in the worksheet.
2. Enter the following formula into the cell and hit Enter:

=SORTBY($A$2:$A$8,ROW(A2:A8),-1)

3. In the above formula, ensure you modify the following elements:
   • $A$2:$A$8: put the absolute reference of the cell range of the dataset you need to flip
   • A2:A8: the variable reference of the first column of the source data column inside the ROW function

4. You should now see the reverse order of the input data.

5. Select the column, click Ctrl + C, and paste the data in the source column or anywhere else by pressing Ctrl + Alt + V.
6. On the Paste Special dialog box, click Values and then select OK to paste the values only.

This is a quick and easy way to flip data orders in Excel if you’ve got the latest Excel app or web app access. Also, pasting values only will ensure that the reversed data won’t change when you alter the source data.

Reverse Order in Excel Using INDEX and ROW Functions

Another Excel function combination you can use to reverse the order of data in Excel is by using the INDEX and ROW formulas. Here’s the exact formula and steps you can use:

1. Highlight a cell where you want to populate the reverse data order.
2. Enter this formula into the cell and hit Enter:

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

3. Now, drag the fill handle until the end of the column to arrange all the reference data in reverse order in the new column.

You may want to copy the reversed data in another column as values by following the steps mentioned in the SORT function section earlier. It’ll secure the data from future changes in the formula.

If the order of data you want to flip isn’t in the A2:A8 cell range, then modify the references according to your own dataset. For instance, your data is in the F15:F100 cell range. So the formula would become as shown below:

=INDEX($F$15:$F$100, COUNTA($F$15:$F$100) - ROW() + 2)

Use This VBA Script to Reverse Order in Excel

Suppose, you’ve created an Excel VBA program for automated data analysis and visualization. At a point, you require to reverse the order of certain data in a column. You might think is that diable using a VBA script? Yes, you can do that. Find below the code as well as the steps to implement such automation:

1. On your Excel worksheet, bring up the Excel VBA Editor by pressing Alt + F11 keys.
2. Click Insert and then select Module to add a blank module.
3. Enter the following VBA script into the blank module:

Sub ReverseData()
    Dim originalRange As Range
    Dim reversedRange As Range
    Dim cell As Range
    Dim i As Long
    
    ' Set the range of original data
    Set originalRange = Range("A2:A8")
    
    ' Set the range where reversed data will be pasted
    Set reversedRange = Range("B2:B8")
    
    ' Clear any existing data in the reversed range
    reversedRange.ClearContents
    
    ' Loop through the original range in reverse order
    For i = originalRange.Rows.Count To 1 Step -1
        ' Get the cell from the original range
        Set cell = originalRange.Cells(i)
        
        ' Calculate the corresponding row in the reversed range
        Dim reversedRow As Long
        reversedRow = originalRange.Rows.Count - i + 1
        
        ' Copy the value from the original cell to the reversed range
        reversedRange.Cells(reversedRow).Value = cell.Value
    Next i
End Sub

4. Make some changes in the above script according to the source worksheet. For example:
   • A2:A8: is the source data range
   • B2:B8: is the destination where Excel will populate data in reverse order
5. Click Save and close the VBA Editor.

6. Now, hit Alt + F8 to open the Macro dialog box.
7. There, select the ReverseData macro and hit the Run button.
8. You should now see the output in the cell range B2:B8.

Use Power Query to Reverse Order of Data in Excel

By far, the most intuitive way to flip the order of data in a column is using the Power Query tool in Excel. It’s equally useful if you’re importing data from an external data source or organizing data from a local Excel worksheet. Find below the step-by-step instructions

1. Select the Excel dataset or table for which you need to flip the order.
2. Now, go to the Data tab on the ribbon and click From Table/Range command.
3. On the Create Table dialog box, click OK.

4. In the Power Query window that pops up, click the Transform tab on the ribbon menu.
5. Click Reverse Rows inside the Table commands block.
6. Check inside the Power Query tool if the reverse order looks okay.

7. Now, click the File tab on the ribbon and select Close & Load to import the transformed data into the Excel worksheet.

8. Excel will create a new worksheet for the imported data.

Conclusion

There could be different reasons why you may want to reverse the data order in Excel. Since Excel doesn’t offer any one-click solution to this issue, you need to try any of the methods discussed here.

However, if you aren’t an expert Excel user and still want to know how to reverse order in Excel, I suggest you use any approaches except the ones involving Power Query, or Excel VBA. 

Users with any level of Excel knowledge can implement the suggested methods without facing any trouble. If you have any questions regarding this topic, don’t hesitate to ask in the comment section.