*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*. Thirdly, we will use

**the ROW function****in combination. After that, we will use two simple**

*the OFFSET, COUNTA, and COLUMN functions***codes to reverse the order both horizontally and vertically. Then, we will apply**

*VBA*

*the INDEX function**and the*together. Afterward, we will combine

**ROWS function**

*the COUNTA**and*to reverse the data order. Finally, we will use

**INDEX functions**

*the SORTBY function**and the*to paste the data in reverse order in Excel. We will use the following sample dataset to illustrate the methods.

**ROW function**### 1. Using Sort Command

In the first method, we will use the ** Sort** command to reverse the data order. The

**command will allow us to sort the data both horizontally and vertically. We will use a sorting column or row to reverse the data.**

*Sort*#### 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
cell and write down*D5***1.**

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

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

- 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
cell and enter*C6*.**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
to*1*.*4*

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

- From the prompt, select
.*Options*

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

- Back in the
prompt, first, select*Sort*Then sort it by*Sort by.*.*Row 6* - Then, choose
as the*Largest to Smallest*.*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.

**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 OFFSET function***returns the number of the cell that is referenced as an argument of the cell.**

*The ROW function***Steps:**

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

`=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:**

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

### 3. Using OFFSET, COUNTA, and COLUMN Functions Together

In this example, we will use *the OFFSET, COUNTA, **and *** COLUMN functions** in combination.

**returns the number of cells that are not empty.**

*The COUNTA function***returns the column number of the cell that it refers to.**

*The COLUMN function***Steps:**

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

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

**Read More:** How to Paste in Reverse Order in Excel

### 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
tab in the ribbon.*Developer* - From there, select the
toolbar.*Visual Basic* - Consequently, the
window will be opened.*Visual Basic*

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

- 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
tab and click on it.*Run* - From the drop-down option, select the
command to run the code.*Run*

- 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
tab in the ribbon.*Developer* - From there, hoover down to the
tab.*Visual Basic* - As a result, the
window will appear.*Visual Basic*

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

- 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
tab and then click.*Run* - To run the code, choose the
command from the drop-down menu.*Run*

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

**Read More:** How to Use Excel VBA to Reverse String

### 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.

**extracts a value from a table or range, or a reference to a value, and returns it.**

*The INDEX function***Steps:**

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

`=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):**takes the*The ROWS function*range and returns the number of cell within that range. In this case, it is*B5:$B$10*. One thing to notice is that the*6*cell has relative cell reference and the*B5*cell has absolute cell reference. So, if we move the cursor down, the*B10*cell will be the*B5*cell, but the*B6*cell will remain unchanged. So, then*B10*will return*the ROWS function*.*5*will number the cells in the range*INDEX($B$5:$B$10,ROWS(B5:$B$10)): The INDEX function*. The*B5:B10*cell will have the number*B5*and in ascending order the*1*cell will have the number*B10*. Now, when*6*returns*the ROWS function*in the*6*formula,*ROWS(B5:$B$10)*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*the INDEX function*cell which is*B10*.*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.

**counts the number of non-empty cells in a range.**

*The COUNTA function***Steps:**

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

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

- Then, hit the
button.*Enter*

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

**uses values from a corresponding range or array to sort the contents of a range or array.**

*The SORTBY function***Steps:**

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

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

- Then, hit the
button.*Enter*

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

🔎 **Formula Breakdown:**

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

**Download Practice Workbook**

You can download the practice workbook from here.

## 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.

**Related Articles**

- How to Reverse a String in Excel
- How to Reverse a Number in Excel
- How to Reverse Names in Excel
- How to Switch First and Last Name in Excel with Comma

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