We’ll use a simple dataset to show how to paste in reverse order.

### Method 1 – Using the Sort Command

#### 1.1 Pasting in Reverse Order Vertically

**Steps:**

- Select the
cell and enter*D5***1.**

- Enter numeric values in the subsequent cells in ascending order.

- Go to the
tab on the ribbon.*Data* - Choose
.*Sort & Filter* - Select
.*Sort Largest to Smallest*

- The data order will be reversed.

#### 1.2 Pasting in Reverse Order Horizontally

**Steps:**

- Enter
in**1**.*C6*

- Enter numbers in ascending order from the left to the right.
- Here,
to*1*.*4*

- Select the dataset and go to the
tab on the ribbon.*Data* - Choose
.*Sort & Filter* - Select
.*Sort*

- Select
.*Options*

- Select
in*Sort left to right**Sort Options**.* - Click
.*OK*

- Go back to
prompt and select*Sort**Sort by.*here.*Row 6,* - Choose
as the*Largest to Smallest*.*Order* - Click
.*OK*

- The dataset will be in reverse order.

### Method 2 – Combining OFFSET and ROW Functions

**Steps:**

- Select
and enter the following formula:*E5*

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

- Press
.*Enter*

- Data is pasted in reverse order.
- Drag the
**Fill Handler**across the cells you want to fill.

**Formula Breakdown:**

takes*(ROW(B2)-2): The ROW function*as a reference and returns*B2*(the number of the cell).*2*is then subtracted from the cell and returns zero.*2***OFFSET($B$10,-(ROW(B2)-2),0):**takes*The OFFSET function*as a reference. The two arguments –*B10*and*rows*– define how many rows and columns to move from the reference cell. As both*cols*and*rows*arguments have zero values, the function returns the value in*cols*, which is*B10*. On the other hand, in the next cell*Tom*holds*the ROW function*as a reference, so it will return*B3*. If we subtract*3*from*2*, the value is**3**. The*1*argument of*rows*will be*the OFFSET function*. The*-1*will return the value of the cell above the reference cell, which is*OFFSET function*.*Sheldon*

### Method 3 – Using the OFFSET, COUNTA, and COLUMN Functions

**Steps:**

- Select the
cell and enter the following formula.*C8*

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

- Press
.*Enter*

- Data is pasted in reverse order.
- Drag the
**Fill Handler**across the cells you want to fill.

**Formula Breakdown:**

**COLUMN(A1):**takes*The Column function*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 non-empty cells in the*The COUNTA function*range is*4:4*.*5***OFFSET($B$4,,COUNTA(4:4)-COLUMN(A1)):**takes*The OFFSET function*as a reference. The two arguments-*B4*and*rows*– 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*.*4*will return*T***h**e OFFSET functionthe number of cells to the right of*4,*In this case,*B4.*.*Alisa*

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

### Method 4 – Applying VBA Code

#### 4.1 Pasting in Reverse Order Vertically

**Steps:**

- Select the dataset.
- Go to the
tab on the ribbon.*Developer* - Select the
toolbar.*Visual Basic*

- In the
tab, click*Visual Basic*.*Insert* - Select
.*Module*

- In the coding module, enter the following code and save it.

```
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
```

- Click the
tab.*Run* - Select
from the drop-down menu to run the code.*Run*

- Data order is reversed.

#### 4.2 Pasting in Reverse Order Horizontally

**Steps:**

- Select the dataset.
- Go to the
tab on the ribbon.*Developer* - Select
.*Visual Basic*

- Click
*Insert.* - Select
.*Module*

- Enter the following code and save.

```
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
```

- Select the
tab and click*Run*from the drop-down menu to run the code, .*Run*

- Data order has been changed.

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

### Method 5 – Combine INDEX and ROWS Functions

**Steps:**

- Select the
cell and enter the formula below.*E5*

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

- Press
.*Enter*

- Data is pasted in the opposite order.
- Drag the
**Fill Handler**across the cells you want to fill.

**Formula Breakdown:**

**ROWS(B5:$B$10):**takes the*The ROWS function*range and returns the number of cells within that range. In this case,*B5:$B$10*. Notice that*6*has relative cell reference and*B5*has absolute cell reference. By moving down the cursor,*B10*will be*B5*, but*B6*will not change. So,*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*.*B5:B10*cell will be number*B5*and in ascending order*1*cell will be number*B10*. When*6*returns*the ROWS function*in the*6*formula,*ROWS(B5:$B$10)*takes it as the number of the row.*the INDEX function*will return the value in*T***h**e INDEX functionwhich is*B10*.*Tom*

**Read More:** How to Reverse Rows in Excel

### Method 6 – Using COUNTA and INDEX Functions

**Steps:**

- Select
cell and enter the formula below.*E5*

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

- Press
.*Enter*

- Data is pasted in the opposite order.
- Drag the
**Fill Handler**across the cells you want to fill.

**Formula Breakdown:**

**COUNTA(B4:B$10):**counts the number of non-empty cells in the range*The COUNTA function*. In this case,*B4:B$10*. Notice that*7*has relative cell reference and*B4*has absolute cell reference. So, by moving down the cursor,*B10*cell will be*B4*, but*B5*cell will not change. So,*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*.*B4:B10*will have*B4*and in ascending order*1*will have*B10*. When*7*returns*the COUNTA function*in the*7*formula,*COUNTA(B4:B$10)*takes it as the number of the row.*the INDEX function*will return the value in*T**he INDEX function*which is*B10*.*Tom*

### Method 7 – Combining ROW and SORTBY Functions

**Steps:**

- Select
cell and enter the formula below.*E5*

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

- Press
.*Enter*

- 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 defines data that will be sorted. Here, the cell range*SORTBY(B5:B10,ROW(B5:B10),-1): The SORTBY function’s*. The next argument is*B5:B10*which defines the array by which the previous array will be sorted. In this case, the array will be*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.*sort_order1*indicates a reverse order.*-1*

**Download the Practice Workbook**

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