How to Paste in Reverse Order in Excel (7 Handy Ways)

Microsoft Excel does not have a default function to reverse the order of the dataset. But we often need to reverse the order of our data for various data analyses and representations. In this article, we will discuss 7 methods of how to paste data in reverse order in Excel.

How to Paste in Reverse Order in Excel: 7 Handy Ways

In this article, we will talk about seven effective methods to paste data in reverse order. Firstly, we will use the Sort command to reverse the order of data. Secondly, we will use a combination of the OFFSET function and the ROW function. Thirdly, we will use the OFFSET, COUNTA, and COLUMN functions in combination. After that, we will use two simple VBA codes to reverse the order both horizontally and vertically. Then, we will apply the INDEX function and the ROWS function together. Afterward, we will combine the COUNTA and INDEX functions to reverse the data order. Finally, we will use the SORTBY function and the ROW function to paste the data in reverse order in Excel. We will use the following sample dataset to illustrate the methods.

1. Using Sort Command

In the first method, we will use the Sort command to reverse the data order. The Sort command will allow us to sort the data both horizontally and vertically. We will use a sorting column or row to reverse the data.

1.1 Pasting in Reverse Order Vertically

In this sub-method, we will sort data vertically to reverse the data order.

Steps:

Firstly, select the D5 cell and write down 1.

Then, write down numeric values in the subsequent cells in ascending order.
In this case, we will insert values ranging from 1 to 6.

Then, go to the Data tab in the ribbon.
From there, select the Sort & Filter option.
Finally, select the Sort Largest to Smallest command.

Consequently, the data order will be reversed.

1.2 Pasting in Reverse Order Horizontally

In this sub-method, we will reorder the data written horizontally using the Sort command.

Steps:

To begin with, choose the C6 cell and enter 1.

Then, enter numbers in ascending order from left to right in the cells after that.
In our case, we will insert values ranging from 1 to 4.

After that, select the Data tab from the ribbon following the selection of the dataset.
Then, choose Sort & Filter from the menu.
Finally, select the Sort command.
Consequently, a prompt will appear on the screen.

From the prompt, select Options.

Then, from the Sort Options dialogue box, first, select Sort left to right.
Finally, click OK.

Back in the Sort prompt, first, select Sort by. Then sort it by Row 6.
Then, choose Largest to Smallest as the Order.
Finally, click OK.

Consequently, the dataset will be in reverse order.

2. Applying Combination of OFFSET and ROW Functions

In this instance, we will combine the OFFSET and ROW functions to paste the data in reverse order in Excel. The OFFSET function returns a reference to a range that is separated from a cell or a range of cells by a specified number of rows and columns. The ROW function returns the number of the cell that is referenced as an argument of the cell.

Steps:

Firstly, select the E5 cell and write the following formula,

=OFFSET($B$10,-(ROW(B2)-2),0)

Then, hit Enter.

Consequently, you will find that the data is pasted in reverse order.
Then, lower the cursor down to the last data cell.

🔎 Formula Breakdown:

(ROW(B2)-2): The ROW function takes the B2 cell as a reference and returns 2 which is the number of the cell. Then, 2 is subtracted from the cell and the sum is zero.
OFFSET($B$10,-(ROW(B2)-2),0): The OFFSET function takes the B10 cell as a reference. The function’s next two arguments- rows and cols – respectively define how many rows and columns to move from the reference cell. Here, the rows argument and the cols argument both have zero values. So the function returns the value in the B10 cell, which is Tom. But in the next cell, when the ROW function holds B3 as a reference, the ROW function will return 3. If we subtract 2 from that, then the value will be 1. Then, the rows argument of the OFFSET function will be -1. This means the OFFSET function will return the value of the cell that is one cell above the reference cell which is Sheldon.

3. Using OFFSET, COUNTA, and COLUMN Functions Together

In this example, we will use the OFFSET, COUNTA, and COLUMN functions in combination. The COUNTA function returns the number of cells that are not empty. The COLUMN function returns the column number of the cell that it refers to.

Steps:

To begin with, select the C8 cell and write the following formula,

=OFFSET($B$4,,COUNTA(4:4)-COLUMN(A1))

Then, press Enter.

As a result, you will observe that the data is pasted in reverse order.
Next, move the cursor to the right up to the last data cell.

🔎 Formula Breakdown:

COLUMN(A1): The Column function takes the A1 cell as a reference and returns 1 which is the column number of the cell.
COUNTA(4:4): The COUNTA function counts the number of non-empty cells in a given range. In this case, the number of nonempty cells in the 4:4 range is 5.
OFFSET($B$4,,COUNTA(4:4)-COLUMN(A1)): The OFFSET function takes the B4 cell as a reference. The function’s next two arguments- rows and cols– respectively, define how many rows and columns to move from the reference cell. Here, the rows argument is empty. The cols argument is COUNTA(4:4)-COLUMN(A1) which returns, 5-1 or 4. That means the OFFSET function will return the value 4 cells to the right of the B4 In this case, the value is Alisa.

4. Applying VBA Code

In this method, we will use 2 VBA codes to reverse the order of the dataset. The two codes will reverse the order of the dataset vertically and horizontally.

4.1 Pasting in Reverse Order Vertically

In this sub-method, we will reverse the data order vertically by using a simple VBA code.

Steps:

Firstly, select the dataset.
Then, go to the Developer tab in the ribbon.
From there, select the Visual Basic toolbar.
Consequently, the Visual Basic window will be opened.

After that, in the Visual Basic tab, click on Insert.
Then, select the Module option.

Consequently, a coding module will appear.
In the coding module, write down the following code.
Then, save the code.

Sub PasteInReverseVertically()
'declaring variable
Dim StartRow As Variant
Dim EndRow As Variant
Dim TopNum As Integer
Dim LastNum As Integer
Application.ScreenUpdating = False
'initializing variable with value
TopNum = 1
LastNum = Selection.Rows.Count
'running do while loop
Do While TopNum < LastNum
StartRow = Selection.Rows(TopNum)
EndRow = Selection.Rows(LastNum)
Selection.Rows(LastNum) = StartRow
Selection.Rows(TopNum) = EndRow
TopNum = TopNum + 1
LastNum = LastNum - 1
Loop
Application.ScreenUpdating = True
End Sub

Finally, go to the Run tab and click on it.
From the drop-down option, select the Run command to run the code.

Consequently, we will see that the data order is reversed.

4.2 Pasting in Reverse Order Horizontally

In this sub-method, we will reverse the order of data in a horizontal arrangement using a VBA code.

Steps:

To start with, select the dataset.
After that, select the Developer tab in the ribbon.
From there, hoover down to the Visual Basic tab.
As a result, the Visual Basic window will appear.

After that, click on Insert in the Visual Basic editor.
Then, select the Module option.

As a result, a coding module will show up.
Write the following code in the coding module.
After that, save the code.

Sub PasteInReverseHorizontally()
'declaring variable
    Dim activrng As Range
    Dim arry As Variant
    Dim a As Integer, b As Integer, c As Integer
    On Error Resume Next
 'setting the selected range as the value of activrng variable
    Set activrng = Application.Selection
    arry = activrng.Formula
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 'running for loop
    For a = 1 To UBound(arry, 1)
        c = UBound(arry, 2)
        For b = 1 To UBound(arry, 2) / 2
            xTemp = arry(a, b)
            arry(a, b) = arry(a, c)
            arry(a, c) = xTemp
            c = c - 1
        Next
    Next
    activrng.Formula = arry
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Last but not least, select the Run tab and then click.
To run the code, choose the Run command from the drop-down menu.

As a result, we will observe that the data order has been changed.

5. Use a Combination of INDEX and Rows Functions

In this method, we will amalgamate the INDEX and ROWS functions to reverse the data order. The INDEX function extracts a value from a table or range, or a reference to a value, and returns it.

Steps:

To start with, select the E5 cell and write the formula below,

=INDEX($B$5:$B$10,ROWS(B5:$B$10))

Then, hit Enter.

Because of this, you will notice that the data is pasted in the opposite order.
Finally, hover the cursor down to the last data cell.

🔎 Formula Breakdown:

ROWS(B5:$B$10): The ROWS function takes the B5:$B$10 range and returns the number of cell within that range. In this case, it is 6. One thing to notice is that the B5 cell has relative cell reference and the B10 cell has absolute cell reference. So, if we move the cursor down, the B5 cell will be the B6 cell, but the B10 cell will remain unchanged. So, then the ROWS function will return 5.
INDEX($B$5:$B$10,ROWS(B5:$B$10)): The INDEX function will number the cells in the range B5:B10. The B5 cell will have the number 1 and in ascending order the B10 cell will have the number 6. Now, when the ROWS function returns 6 in the ROWS(B5:$B$10) formula, the INDEX function will take this as the number of the row that the INDEX function has assigned to. Thus, the INDEX function will return the value in the B10 cell which is Tom.

Read More: How to Reverse Rows in Excel

6. Utilizing COUNTA and INDEX Functions Together

In this instance, we will use the COUNTA and INDEX functions in conjunction to reverse the order of the dataset. The COUNTA function counts the number of non-empty cells in a range.

Steps:

Firstly, select the E5 cell and write the formula below:

=INDEX($B$4:$B$10,COUNTA(B4:B$10))

Then, hit the Enter button.

As a result, you will observe that the data is pasted in the opposite order.
Then, move the cursor to the last data cell.

🔎 Formula Breakdown:

COUNTA(B4:B$10): The COUNTA function counts the number of non-empty cells in the range B4:B$10.In this case, the value will be 7. One thing to notice is that the B4 cell has relative cell reference and the B10 cell has absolute cell reference. So, if we move the cursor down, the B4 cell will be the B5 cell, but the B10 cell will remain unchanged. So, then the COUNTA function will return 6.
INDEX($B$4:$B$10,COUNTA(B4:B$10)): The INDEX function will number the cells in the range B4:B10. The B4 index will have the number 1 and in ascending order the B10 cell will have the number 7. Now, when the COUNTA function returns 7 in the COUNTA(B4:B$10) formula, the INDEX function will take this as the number of the row that the INDEX function has assigned to. Thus, the INDEX function will return the value in the B10 cell which is Tom.

7. Combining ROW and SORTBY Functions

In the final method, we will use the ROW and the SORTBY functions together to do the job. The SORTBY function uses values from a corresponding range or array to sort the contents of a range or array.

Steps:

To begin with, select the E5 cell and type the formula below:

=SORTBY(B5:B10,ROW(B5:B10),-1)

Then, hit the Enter button.

As a result, you will find that the data is pasted in reverse order.

🔎 Formula Breakdown:

ROW(B5:B10): The ROW function returns the number of rows in the range B5:B10.
SORTBY(B5:B10,ROW(B5:B10),-1): The SORTBY function’s first argument is the array of data that it will sort. Here, the array will be the values in the cell range B5:B10. The next argument is the by_array1 which indicates the array by which the previous array will be sorted. In this case, the array will be the number 1-6 which is returned by the ROW function. The next array is the sort_order1 which indicates in which order the array will be sorted. The -1 indicates a reverse order.

Download Practice Workbook

You can download the practice workbook from here.

Paste in Reverse Order.xlsm

Conclusion

In this article, we have learned 7 effective ways to paste the date in reverse order in Excel. This will allow users to arrange their data according to their needs.

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

Get FREE Advanced Excel Exercises with Solutions!

How to Paste in Reverse Order in Excel (7 Handy Ways)

How to Paste in Reverse Order in Excel: 7 Handy Ways

1. Using Sort Command

1.1 Pasting in Reverse Order Vertically

1.2 Pasting in Reverse Order Horizontally

2. Applying Combination of OFFSET and ROW Functions

3. Using OFFSET, COUNTA, and COLUMN Functions Together

4. Applying VBA Code

4.1 Pasting in Reverse Order Vertically

4.2 Pasting in Reverse Order Horizontally

5. Use a Combination of INDEX and Rows Functions

6. Utilizing COUNTA and INDEX Functions Together

7. Combining ROW and SORTBY Functions

Conclusion

Related Articles

Leave a reply Cancel reply