** 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*## Download Practice Workbook

You can download the practice workbook from here.

## 7 Handy Ways to Paste in Reverse Order in Excel

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.

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

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

**Read More: How to Reverse Data in Excel Cell (5 Easy Ways)**

### 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 Reverse Column Order in Excel (4 Easy Methods)**

### 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**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 Reverse Order of Columns Vertically in Excel (3 Ways)**

**Similar Readings**

**How to Reverse Text to Columns in Excel (6 Handy Methods)****How to Reverse X Axis in Excel (4 Quick Tricks)****Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)****How to Reverse the Order of Worksheets in Excel (3 Easy Ways)****How to Reverse Transpose in Excel (3 Simple Methods)**

### 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 (4 Easy Ways)**

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

## Conclusion

In this article, we have learned ** 7** effective ways to paste the date in reverse order in

**. This will allow users to arrange their data according to their needs.**

*Excel*