How to Reverse Data in Excel Cell (5 Easy Ways)

While working in Excel, users may have to reverse the data in the worksheet’s cells for working purposes. But there are no integrated options in Excel to do that. You can reverse data that number from largest to smallest or vice versa. Or you can reverse the data alphabetically. But when your working criteria do not match the two options, you have to apply other methods. That’s why, in this article, we will show you how to reverse data in an Excel cell.

How to Reverse Data in Excel Cell: 5 Handy Approaches

The main goal of our article is to reverse data in Excel cells regardless of whether they are numeric or alphabetical characters. Therefore, you will see five different methods in this article for reversing data in an Excel cell. First, we will combine the INDEX function and the ROWS function of Excel in our first method. Then, we will merge the SORTBY function and the ROW function into our second one. We will use the help of a helper column and a helper row to complete our task in the third procedure.

Lastly, we will apply two different VBA codes in our fourth and fifth procedures to reverse column data and row data, respectively. The following data set will be needed to demonstrate our working procedures. Here in column B, we have the names of some states, and column C represents the capitals of those states.

1. Combining INDEX and ROWS Functions to Reverse Data

In our first approach, we will combine the INDEX function and the ROWS function to reverse data. Here we will use the ROWS function to call the row number for a fixed cell range, and then the INDEX function will input the value for that specific row number. The detailed steps of this procedure are as follows.

Step 1:

• First of all, create a second data table in your worksheet to see the result after reversing.

Step 2:

• Secondly, in cell E5 of the new data table, insert the following formula.

Step 3:

• Thirdly, press Enter and you will see the last data of column B as the first data of column E.
• Then, use the AutoFill feature to drag the formula to the lower cells.

Step 4:

• Fourthly, to reverse the name of the capitals, write the following formula in cell F5.

Step 5:

• Then, again press Enter and drag the Fill Handle to get results for the lower cells.

Read More: How to Reverse Order of Data in Excel

2. Merging SORTBY and ROW Functions to Reverse Data in Excel Cell

As for our second method, we will merge the SORTBY function with the ROW function. The ROW function will give the row numbers of the total cell range, and then the SORTBY function will sort the data according to those numbers. For a better understanding, see the following steps.

Step 1:

• Firstly, type the following formula in cell E5 of the new data table.

Step 2:

• Then, after pressing Enter, the formula will reverse the data for the whole data set.

3. Inserting Helper Column and Helper Row to Reverse Data

We will insert the Sort command in Excel to reverse data in an Excel cell. For that purpose, we will need the help of a helper column. Because the data in the data set is not numerical and we don’t want to sort them alphabetically. So, we need to insert this column to reverse the data.

3.1 Inserting Helper Column

Due to the differences in how the data is organized into columns and rows, reversing data in columns and rows requires a different procedure. So, to reverse the data in a column, we will insert a helper column. The steps for this procedure are as follows.

Step 1:

• First of all, make the space for the helper column in column D.

Step 2:

• Then, number the total rows of your data set.
• The numbering is totally up to the reversing requirements of the users.
• For our example, we will number them from 1 to 6.

Step 3:

• Thirdly, choose the whole data range which is B4:D10.
• After that, go to the Home tab of the ribbon.
• Then, select Sort & Filter from the Editing group.

Step 4:

• Fourthly, from the Sort & Filter dropdown choose Custom Sort.

Step 5:

• Fifthly, you will see the Sort dialogue box.
• In there, under the Sort by dropdown select Helper Column.
• Then, in the Order dropdown, select Largest to Smallest.
• Lastly, press OK.

Step 6:

• Finally, the data will be reversed according to the sequence of the helper column.

3.2 Inserting Helper Row

The above procedure was to reverse the data of a column. But, the procedure will be somewhat different if the data are arranged horizontally or in a row. Here, instead of a helper column, we will insert a helper row. How we can do this is described in the following.

Step 1:

• Firstly, insert a new row under the data table to create the helper row.
• Then, name the row and number it according to your desired sequence.

Step 2:

• Secondly, select the data range from cell B4:H6.
• After that, select the Custom Sort command just like in the previous discussion.
• Then, in the Sort dialogue box choose Options before sorting.

Step 3:

• Thirdly in the Sort Options dialogue box, you will see several options.
• Then, select Sort left to right for the horizontal sorting in our procedure.
• After that, press OK.

Step 4:

• Fourthly, the Sort dialogue box will appear again.
• In the Sort by dialogue box, input the helper row’s row number which is Row 6.
• Then, in the Order dropdown, select Largest to Smallest.
• Lastly, press OK.

Step 5:

• Finally, This will reverse the data horizontally.

4. Applying VBA to Reverse Data Vertically in Excel Cell

In our previous methods, we have either used a function or an Excel command to reverse data. Now, we will apply a VBA code in our fourth method to reverse data vertically. We have to input the correct sequence and command into the code to get our desired result. Follow the below steps to get a clear concept.

Step 1:

• Firstly, select the data range B5:C10.
• Then, from the Developer tab, choose Visual Basic. Alternatively, you can press ALT+F11 to do so. If you don’t see the Developer tab, then follow this link to display the Developer tab on the ribbon.

Step 2:

• Secondly, after choosing the command you will see the VBA window.
• Then, from the Insert tab choose Module.

Step 3:

• Thirdly, paste the following code into the module.

Sub VBA_Reverse_Column_Data()
Dim First_Row As Variant
Dim Final_Row As Variant
Dim Initial_Num As Integer
Dim Last_Num As Integer
Application.ScreenUpdating = False
Initial_Num = 1
Last_Num = Selection.Rows.Count
Do While Initial_Num < Last_Num
    First_Row = Selection.Rows(Initial_Num)
    Final_Row = Selection.Rows(Last_Num)
    Selection.Rows(Last_Num) = First_Row
    Selection.Rows(Initial_Num) = Final_Row
    Initial_Num = Initial_Num + 1
    Last_Num = Last_Num - 1
Loop
Application.ScreenUpdating = True
End Sub

Sub VBA_Reverse_Column_Data()

Dim First_Row As Variant
Dim Final_Row As Variant
Dim Initial_Num As Integer
Dim Last_Num As Integer

Application.ScreenUpdating = False

Initial_Num = 1
Last_Num = Selection.Rows.Count

Do While Initial_Num < Last_Num
    First_Row = Selection.Rows(Initial_Num)
    Final_Row = Selection.Rows(Last_Num)
    Selection.Rows(Last_Num) = First_Row
    Selection.Rows(Initial_Num) = Final_Row
    Initial_Num = Initial_Num + 1
    Last_Num = Last_Num - 1
Loop

Application.ScreenUpdating = True
End Sub

Step 4:

• Fourthly, save the code in the module and press F5 or the play button to run it.
• Before pressing the play button, keep the cursor in the module.

Step 5:

• Finally, after running the code, the data in the column will be reversed vertically.

Read More: How to Reverse Order of Columns Vertically in Excel

5. Using VBA to Reverse Data Horizontally

We will use another VBA code for reversing data as our final procedure. But this time, we will reverse the data horizontally. See the below-given steps for understanding our last procedure in this article.

Step 1:

• First of all, we will use the following data set in our procedure.

Step 2:

• Secondly, open the VBA window from the Visual Basic command just like the previous method.
• Then paste the following code into the module of the window.

 Sub VBA_Reverse_Row_Data()
    Dim xRange As Range
    Dim xArray As Variant
    Dim x1 As Integer, x2 As Integer, x3 As Integer
    On Error Resume Next
    xTitleId = "ExcelDemy - Reverse Data Horizontally"
    Set xRange = Application.Selection
    Set xRange = Application.InputBox("Select the Cell Range", _
    xTitleId, xRange.Address, Type:=8)
    xArray = xRange.Formula
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For x1 = 1 To UBound(xArray, 1)
        x3 = UBound(xArray, 2)
        For x2 = 1 To UBound(xArray, 2) / 2
            xArray_Temp = xArray(x1, x2)
            xArray(x1, x2) = xArray(x1, x3)
            xArray(x1, x3) = xArray_Temp
            x3 = x3 - 1
        Next
    Next
    xRange.Formula = xArray
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Success!"
End Sub

Sub VBA_Reverse_Row_Data()

Dim xRange As Range
Dim xArray As Variant
Dim x1 As Integer, x2 As Integer, x3 As Integer

On Error Resume Next

xTitleId = "ExcelDemy - Reverse Data Horizontally"
Set xRange = Application.Selection
Set xRange = Application.InputBox("Select the Cell Range", _
xTitleId, xRange.Address, Type:=8)

xArray = xRange.Formula

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For x1 = 1 To UBound(xArray, 1)
x3 = UBound(xArray, 2)
For x2 = 1 To UBound(xArray, 2) / 2
xArray_Temp = xArray(x1, x2)
xArray(x1, x2) = xArray(x1, x3)
xArray(x1, x3) = xArray_Temp
x3 = x3 - 1
Next
Next

xRange.Formula = xArray
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Success!"
End Sub

Step 3:

• Thirdly, save the code in the module.
• Then keeping the cursor in the module, press F5 or play to run the code.

Step 4:

• Then the code will ask for the cell range to reverse the data.
• Here give the cell range C4:H5 and then press OK.

Step 5:

• Finally, the data arranged horizontally will be reversed after running the code and selecting the cell range.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to reverse data in the Excel cell by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

Related Articles

• How to Mirror Data in Excel
• How to Reverse Text to Columns in Excel
• How to Reverse Column Order in Excel
• How to Reverse Data in Excel Chart

<< Go Back to Excel Reverse Order | Sort in Excel | Learn Excel