Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to copy and paste rows or columns in reverse order in Excel?

Sometimes, you may need to copy and paste a list of column or row in reverse order vertically or horizontally as below screenshot shown. How could you deal with this job quickly and easily in Excel?

Copy and paste a list of column or row in reverse order with formula

Copy and paste a range of columns or rows in reverse order with VBA code

Copy and paste a range of columns or rows in reverse order with an awesome feature


Copy and paste a list of column or row in reverse order with formula

Copy and paste a list of column in reverse order vertically

If you want to reverse a list of column cells, you can apply the below formula:

Enter or copy the following formula into a blank cell where you want to reverse the column order:

=OFFSET($A$15,-(ROW(A1)-1),0)

Note: In the above formula, A1 is the first cell in the column, and A15 is the last cell in the column.

Then, drag the fill handle down to the cells to extract the cell values in reverse order as below screenshot shown:


Copy and paste a list of row in reverse order horizontally

To copy and paste a list of row in reverse order, please use the following formula:

Enter or copy this formula into a blank cell:

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

Note: In the above formula, A1 is the first cell in the row, and 1:1 is the row number that your data located. If your data in row 10, you should change it to 10:10.

And then, drag the fill handle right to the cells that you want to apply this formula until all values extracted, and you will get all the values have been reversed horizontally, see screenshot:


Copy and paste a range of columns or rows in reverse order with VBA code

If you need to copy and paste a range of columns or rows in reverse order, here, I will introduce some VBA codes for solving it quickly and easily. Please do with the following steps:

Copy and paste a list of column in reverse order vertically

1. First, you should copy and paste your data into a new place, and then, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy and paste a range of cells in reverse order vertically

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

3. Then, press F5 key to run this code, and a prompt box is popped out to remind selecting the data range that you want to reverse vertically, see screenshot:

4. And then, click OK button, and the data range has been reversed vertically as below screenshots shown:


Copy and paste a range of cells in reverse order horizontally

To reverse the data range in horizontal order, please apply the below VBA code:

VBA code: Copy and paste a range of cells in reverse order horizontally

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

After running this code, you will get the following screenshots as you need:


Copy and paste a range of columns or rows in reverse order with an awesome feature

If you are not familiar with the above formulas and VBA codes, here, I will recommend an easy tool-Kutools for Excel, with its Flip Vertical Range and Flip Horizontal Range features, you can reverse the range of cells vertically and horizontally with only one click.

Tips:To apply this Flip Vertical Range and Flip Horizontal Range features, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

Copy and paste a range of cells in reverse order horizontally

1. Select the range of cells, and then click Kutools > Range > Flip Vertical Range > All (Only flip values), see screenshot:

2. And then, the range of cell values have been reversed vertically at once, see screenshots:


Copy and paste a range of cells in reverse order horizontally

1. Select the range of cells, and then click Kutools > Range > Flip Horizontal Range > All (Only flip values), see screenshot:

2. And then, all cell values in the selection have been reversed horizontally immediately, see screenshots:

Click to Download Kutools for Excel and free trial Now!


More relative copy and paste articles:

  • Copy And Paste Merged Cells To Single Cells In Excel
  • Normally, when you copy the merged cells and paste them into other cells, the merged cells will be pasted directly. But, you want to paste theses merged cells to single cells as following screenshot shown, so that you can deal with the data as you need. This article, I will talk about how to copy and paste merged cells to single cells.
  • Copy A Column And Paste Only The Unique Records In Excel
  • For a column with lots of duplicates, you may need to get a copy of this list with only the unique values. How can you do to achieve this? In this article, we provide two methods to paste only the unique records from a column list in Excel.
  • Copy Data From Protected Sheet
  • Supposing, you receive an Excel file from another person which with worksheets are protected by the user, now, you want to copy and paste the raw data to another new workbook. But the data in the protected sheet is not able to be selected and copied because of the user uncheck the Select locked cells and Select unlocked cells options when protecting the worksheet.
  • Copy Data To Another Worksheet With Advanced Filter In Excel
  • Normally, we can quickly apply the Advanced Filter feature to extract data from the raw data in the same worksheet. But, sometimes, when you try to copy the filtered result to another worksheet, you will get the following warning message. In this case, how could you deal with this task in Excel?


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.