Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


5 Handy Approaches to Reverse Data in Excel Cell

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.

Handy Approaches to Reverse Data in Excel Cell


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.

Combining INDEX and ROWS Functions to Reverse Data in Excel Cell

Step 2:

  • Secondly, in cell E5 of the new data table, insert the following formula.
=INDEX($B$5:$B$10,ROWS(B5:$B$10))

Formula Breakdown

  • ROWS(B5:$B$10): The ROWS function will show the number of rows that are within this cell range. For our example, there are six rows between cells B5 and B10. As we drag the formula to the lower cells, the number will decrease because we have used a dynamic cell reference.
  • INDEX($B$5:$B$10,ROWS(B5:$B$10)): Then, the INDEX function will show the result based on the value from the second argument. That means, first it will input the value of the sixth row, then the fifth row, and so on…, thus reversing the data.

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.
=INDEX($C$5:$C$10,ROWS(C5:$C$10))

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 (4 Useful Methods)


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.
=SORTBY($B$5:$C$10,ROW(B5:B10),-1)

 Merging SORTBY and ROW Functions Reverse Data in Excel Cell

Formula Breakdown

  • ROW(B5:B10): The ROW function will show the total rows one by one that are in this cell range, that are row 5 to row 10 for our example.
  • SORTBY($B$5:$C$10,ROW(B5:B10),-1): Then the SORTBY function will sort the data according to the result of the ROW function. But the -1 in the formula will reverse the order thus the SORTBY function will fill the data table but in the reverse order.

Step 2:

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

Read More: How to Reverse a Number in Excel (6 Quick Tricks)


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.

Inserting Helper Column to Reverse Data in Excel Cell

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.

Inserting Helper Row to Reverse Data in Excel Cell

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.

Read More: How to Reverse Column Order in Excel (4 Easy Methods)


Similar Readings


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.

 Applying VBA to Reverse Data Vertically in Excel Cell

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

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure VBA_Reverse_Column_Data.
Sub VBA_Reverse_Column_Data()
  • Secondly, we define the variable types.
Dim First_Row As Variant
Dim Final_Row As Variant
Dim Initial_Num As Integer
Dim Last_Num As Integer
  • Thirdly, we disable the screen updating.
Application.ScreenUpdating = False
  • Then, we set the initial number as 1 and the last number as the number of rows in the selected range.
Initial_Num = 1
Last_Num = Selection.Rows.Count
  • Afterward, we use a loop. Here, we go through the selected range until the initial number is less than the number of rows. It swaps the columns to reverse the data.
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
  • Lastly, we re-enable the Screen Updating and finish the Sub procedure.
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.

VBA Code Run

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 (3 Ways)


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.

Using VBA to Reverse Data Horizontally in Excel Cell

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

VBA Code Breakdown

  • Firstly, we are calling our Sub procedure VBA_Reverse_Row_Data.
Sub VBA_Reverse_Row_Data()
  • Secondly, we declare the variable types.
Dim xRange As Range
Dim xArray As Variant
Dim x1 As Integer, x2 As Integer, x3 As Integer
  • Then, we instruct the code to ignore all errors and continue the loop.
On Error Resume Next
  • After that, we set the properties of the InputBox.
xTitleId = "ExcelDemy - Reverse Data Horizontally"
Set xRange = Application.Selection
Set xRange = Application.InputBox("Select the Cell Range", _
xTitleId, xRange.Address, Type:=8)
  • Then, we keep the formula intact.
xArray = xRange.Formula
  • Next, we set some properties to speed up the code.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
  • Afterward, we loop through the range and reverse the data.
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
  • Finally, we keep the formula intact and set the Application properties to default values and thus completing our code.
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.

Using VBA to Reverse Data Horizontally Final Output

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


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. The Exceldemy team is always concerned about your preferences.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo