Flip Columns, Rows, and Tables in ExcelLearn how to flip data vertically and/or horizontally in Excel

  |

There might come a time when you have to flip data in Excel. And no, I don’t mean literally flipping your computer to ‘flip’ the data.

What I mean is that you may have to flip or reverse a column(s), row(s), or even a table.

Now, you might think that flipping data is a built-in option in Excel. Surprisingly (and sadly) though, it isn’t.

There’s no dedicated function or feature to flip data in Excel. Well, there’s the Excel Sort feature which you can use though to reverse the order of sorted data.

The keyword here is sorted.

You just can’t use the Sort feature to flip a column, row, or table with unsorted data. You’ll have to perform a couple of things before you can do so.

While you can always do the flipping manually, it’s rarely practical and efficient. This is especially if you’re working with a large dataset.

As such, to do the task efficiently you’ll be using a combination of Excel functions to flip (unsorted) data in Excel.

In this article, you’ll be learning how to flip data vertically and horizontally in Excel using its features and functions.

By the end of the article, you should be able to flip data in Excel, be it sorted or unsorted.

Let’s get started.

Flip Data in Excel USING SORT and Helper Column/Row

While I did say that you can’t just use the SORT feature to flip unsorted data in Excel, I also said that you’ll have to perform a couple of things before you can do so.

Yes, you can use Excel’s Sort feature to flip unsorted data. And you’ll be doing so with a helper column/row.

The helper column/row isn’t a built-in Excel feature. Rather, it’s a column or row that you’ll manually be making.

In this column/row, you’ll be inputting numbers in ascending order (1, 2, 3, 4…). Then you’ll be sorting this helper column/row to flip the data you’re working on.

How to Flip Data Vertically (Flip Columns) in Excel

Let’s say that you want to flip this data-filled column:

To do so, you’ll be using Excel’s Sort feature and a helper column.

  • Select an empty column that is adjacent to the column that you want to flip. If there are no adjacent empty columns, insert one.
  • You may label this column as “Helper Column”. In this column, you’ll be inputting numbers in ascending order (until the next empty row).

  • Enter 1 in the first empty cell, and then 2 in the next one. From here, you’ll be using the Fill handle to automatically fill the rest of the column.

  • Select both cells (the ones that contain 1 and 2). Hover your mouse cursor over the bottom-right corner of the last cell of your selection (which is cell B3 in our illustration). This is how you access the Fill Handle (represented by a cross symbol). Double-click on it to autofill the column.

  • Select the column that you want to flip and your helper column. Open the Data tab. You should find a Sort button in the middle section of the ribbon. Click on it. This will open the Sort dialog box.

  • In the dropdown menu next to Sort by, select “Helper Column”. Next, since you want to flip the column, set Order to “Largest to Smallest”. Click the OK button after doing so.

  • You have successfully flipped the column.

You may now delete the helper column.

Do note that you can also use the above procedures for multiple columns or tables. Just select the columns or table before sorting.

How to Flip Data Horizontally (Flip Rows) in Excel

Suppose you want to flip this data-filled row:

To do so, you’ll be using Excel’s Sort feature and a helper row.

  • Select an empty row that is adjacent to the column that you want to flip. If there are no adjacent empty rows, insert one.
  • You may label this row as “Helper Row”. In this column, you’ll be inputting numbers in ascending order (until the next empty column).

  • Enter 1 in the first empty cell of the row, and then 2 in the next one. From here, you’ll be using the Fill handle to automatically fill the rest of the column.

  • Select both cells (the ones that contain 1 and 2). Hover your mouse cursor over the bottom-right corner of the last cell of your selection (which is cell C2 in our illustration) to access the Fill Handle. Click and drag it to fill the row with ascending numbers.

  • Select the row that you want to flip and your helper row. Open the Data tab. You should find a Sort button in the middle section of the ribbon. Click on it. This will open the Sort dialog box.

  • In the Sort dialog box, click the Options button. This will open the Sort Options dialog box. Select the “Sort left to right” option. Then press the OK button.

  • Back to the Sort dialog box, select “Row 2” or your helper row in the dropdown menu after Sort by. Set Order to Largest to Smallest to flip the row. Click the OK button after doing so.

  • You have successfully flipped the row.

You may now delete the Helper Row.

Do note that you can also use the above procedures for multiple rows. Just select the rows before sorting.

Flip Data in Excel Using Formulae

In this section, you’ll be learning how to use formulae to flip data in Excel. Unfortunately, these formulae can only flip columns. You can’t use them to flip rows.

Use the INDEX and ROWS Functions to Flip Columns in Excel

We’ll be using the following dataset for illustration:

You’ll be using the INDEX function to flip this column. Here are the steps to do so

  • Select an empty column that is adjacent to the column that you want to flip. If there are no adjacent empty columns, insert one. This column is where you’ll be showing the flipped column.
  • In the first empty cell of the selected column, input the following formula: =INDEX($A$2:$A$11,ROWS(A2:$A$11))

  • Press the Enter Then, copy the formula to the rest of the column (until the next empty row). You have successfully created a flipped copy of the column.

You may copy the flipped list column and paste it as values to replace the original column.

Note that you can use the above formula to flip multiple columns. Just make sure to change the references.

Use the SORTBY and ROWS Functions to Flip Columns in Excel (for the Office 365 Version)

We’ll be using the following dataset for illustration:

You’ll be using the SORTBY function to flip this column, which is only currently available in the Office 365 version of Excel.

Here are the steps to do so

  • Select an empty column that is adjacent to the column that you want to flip. If there are no adjacent empty columns, insert one. This column is where you’ll be showing the flipped column.
  • In the first empty cell of the selected column, input the following formula: =SORTBY($ A$2:$A$11,ROW(A2:A11),-1)

  • Press the Enter The formula should return a flipped copy of the column.

You may copy the flipped list column and paste it as values to replace the original column.

Note that you can use the above formula to flip multiple columns.

Just make sure to change the references.

Flip Data in Excel Using VBA

In this section, you’ll be learning how to flip data in Excel using VBA.

If you find yourself flipping data often in Excel, using VBA is ideal for you.

Flip Data Vertically (Flip Columns) in Excel Using VBA

The VBA code that you’ll be using to flip columns in Excel is as follows:

Sub FlipColumn()

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 = “Flip columns”

  Set WorkRng = Application.Selection

  Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)

  Arr = WorkRng.Formula

  Application.ScreenUpdating = False

  Application.Calculation = xlCalculationManual

  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

  Application.ScreenUpdating = True

  Application.Calculation = xlCalculationAutomatic

End Sub

Copy this code as you’ll be needing it to create a macro in Excel.

How to Add and Run VBA Code

  • Open the Visual Basic Editor via the Developer tab -> Visual Basic button. Alternatively, you may press the keyboard shortcut Alt + F11.

  • Open the Insert tab, and then select Module. This will open a window where you will be inserting the VBA code for flipping columns.

  • Paste the code you copied above in this window. You may press F5 to test-run the code

  • Close the Visual Basic Editor. Go back to the sheet that you’re working on. Open the Developer tab and press the Macro button. This will open the Macro menu.

  • Select the Macro you just created (FlipColumn) and press run. This will open a window with the title “Flip Columns”.

  • Select the column that you want to flip. Then, press the OK button.

  • You have successfully flipped the column.

To save the VBA code, you’ll have to save the Excel workbook as a .xls, .xslm, or .xlsb file.

Flip Data Horizontally (Flip Rows) in Excel Using VBA

The VBA code that you’ll be using to flip rows in Excel is as follows:

Sub FlipRows()

    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 = “Flip Rows”

    Set WorkRng = Application.Selection

    Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)

    Arr = WorkRng.Formula

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    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

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

End Sub

Copy this code as you’ll be needing it to create a macro in Excel.

How to Add and Run VBA Code

  • Open the Visual Basic Editor via the Developer tab -> Visual Basic button. Alternatively, you may press the keyboard shortcut Alt + F11.

  • Open the Insert tab, and then select Module. This will open a window where you will be inserting the VBA code for flipping rows.

  • Paste the code you copied above in this window. You may press F5 to test-run the code.

  • Close the Visual Basic Editor. Go back to the sheet that you’re working on. Open the Developer tab and press the Macro button. This will open the Macro menu.

  • Select the Macro you just created (FlipRows) and press run. This will open a window with the title “Flip Rows”.

  • Select the row that you want to flip. Then, press the OK button.

  • You have successfully flipped the row.

To save the VBA code, you’ll have to save the Excel workbook as a .xls, .xslm, or .xlsb file.

Conclusion

And those are several Excel features and functions that you can use to flip data in Excel.

We’re still hoping that Excel gives us a one-click option to flip data, but for now, these should do.

I hope that you’re able to use them in your future endeavors.