How to Flip Table in Excel (2 Quick Ways)

Flipping a data table in Excel seems like a trivial task, however, Excel offers no such functionality or any direct ways to invert data. Keeping this in mind, this article shows how to flip table in Excel for both columns and rows. In addition, we’ll also see how to convert multiple columns to rows.

How to Flip Table in Excel: 2 Ways

In Excel, you can flip a table by columns or by rows, here, we’ll show how to flip data by columns and rows using Excel’s built-in options and functions. Therefore, without further delay, let’s see each method individually and in detail.

1. Flipping Table by Columns

Flipping table by columns is the most common scenario, so let us start with this popular query.
Now, considering the List of Sales Reps dataset shown in the B4:C14 cells. Here, we have the Names of the sales reps and their Sales in USD respectively.

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1.1 Using Sort Option

Let’s begin with the most popular way to flip data, that is to say, using Excel’s Sort option. So, just follow these steps shown below.

📌 Steps:

Initially, make a Helper Column and number it serially as shown in the picture below.

Next, go to the Data tab >> click the Sort button.

Now, this opens the Sort window.

Then, insert a check in the My data has headers option >> in the Sort by option, select the column heading Helper Column >> lastly, in the Order field, choose Largest to Smallest option >> hit the OK button.

Finally, this flips the table as shown in the image below.

1.2 Utilizing SORTBY Function

If you’re one of those people who enjoy using Excel functions then the following method has you covered. Here, we’ll use the SORTBY function which sorts a range or array in ascending or descending order based on another given range or array. Hence, let’s see it in action.

📌 Steps:

In the first place, navigate to the E5 cell and enter the expression given below.

=SORTBY($B$5:$C$14,ROW(B5:B14),-1)

Here, the B5:C14 cells refer to the Name and Sales values respectively.

Formula Breakdown:

SORTBY($B$5:$C$14,ROW(B5:B14),-1) → sorts a range or array based on the values in a corresponding range or array. Here, $B$5:$C$14 is the array argument that refers to the Names and Sales values. Following, ROW(B5:B14) represents the by_array1 argument which returns the row numbers of the Names and Sales values. Lastly, -1 is the optional sort_order1 argument which indicates the Descending order.

📃 Note: The SORTBY function is available on Microsoft Excel 365, if you’re using an older version of Excel, then please check the next method.

Lastly, your output should look like the screenshot shown below.

1.3 Employing INDEX Function

Another way to flip the table involves using the INDEX function which returns a value according to given row and column numbers. Moreover, the INDEX function is compatible with older versions of Excel. So, let’s dive in!

📌 Steps:

First and foremost, jump to the E5 cell and type in the expression given below.

=INDEX($B$5:$C$14,ROWS(B5:$B$14),COLUMNS($B$5:B5))

In this formula, the B5:C14 cells refer to the Name and Sales values respectively while the B5:B14 cells indicate the Names.

Formula Breakdown:

INDEX($B$5:$C$14,ROWS(B5:$B$14),COLUMNS($B$5:B5)) → returns a value at the intersection of a row and column in a given range. In this expression, the $B$5:$C$14 is the array argument which is the marks scored by the students. Next, ROWS(B5:$B$14) is the row_num argument which indicates the row location. Lastly, COLUMNS($B$5:B5) is the optional column_num argument that points to the column location.
- Output → Mary

📃 Note: Please make sure to use lock the cell references by pressing the F4 key on your keyboard.

Then, use the Fill Handle Tool to copy the formula into the cells below.

Next, select the E5:E14 cells >> drag the Fill Handle Tool across to copy the formula into the adjacent cells.

That’s it you’ve flipped the table. It’s that simple!

1.4 Applying VBA Code

If you often need to flip the table by columns, then you may consider the VBA code below. It’s simple & easy, just follow along.

📌 Steps:

First, navigate to the Developer tab >> click the Visual Basic button.

This opens the Visual Basic Editor in a new window.

Second, go to the Insert tab >> select Module.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Invert_Table_By_Columns()

Dim First_row As Variant
Dim End_row As Variant
Dim First_num As Integer
Dim End_num As Integer

First_num = 1
End_num = Selection.Rows.Count

Do While First_num < End_num
First_row = Selection.Rows(First_num)
End_row = Selection.Rows(End_num)

Selection.Rows(End_num) = First_row
Selection.Rows(First_num) = End_row

First_num = First_num + 1
End_num = End_num - 1

Loop

End Sub

⚡ Code Breakdown:

Now, I will explain the VBA code for flipping the table. In this case, the code is divided into 2 steps.

In the first portion, the sub-routine is given a name, here it is Invert_Table_By_Columns().
Next, define the variables First_row, End_row, First_num, and End_num.
Then, assign Variant and Integer data types respectively.
In addition, set First_num equal to 1 and use Selection.Rows.Count to obtain the End_num.
In the second portion, apply the Do While VBA statement to interchange the first and the last rows.
Eventually, the loop moves to the next row and repeats this until all the rows are flipped.

Third, select the B5:C14 cells >> click the Macros button >> choose Invert_Table_By_Columns macro >> hit the Run button.

Eventually, the results should look like the screenshot given below.

2. Flipping Table by Rows

Flipping a table in rows is the opposite case of flipping by columns, so let us see how we can do this.

2.1 Using Sort Option

So far, we’ve discussed how to flip tables in Excel by columns, what if you flip the table along the rows? You’ve landed in the right place, here, we’ll utilize Excel’s Sort option so let us see it in action.

📌 Steps:

First of all, insert a Helper Column and number it sequentially.

Second, go to the Data tab >> click the Sort button.

Now, this opens the Sort wizard.

In turn, click the Options button >> choose the Sort left to right option.

Following this, in the Sort by option, select the row number Row 6 >> in the Order field, choose Largest to Smallest option >> hit the OK button.

Subsequently, this flips the table as shown in the image below.

2.2 Employing VBA Code

You might be wondering, is there a way to flip a table by rows with VBA code? Then, I have some great news for you. Now, let’s have a look at the process in the steps below.

📌 Steps:

At the very beginning, run Steps 1-2 from the previous methods to open the Visual Basic editor, insert a new Module and enter the code.

Sub Invert_Table_By_Rows()
 
    Dim r_range As Range
    Dim wk_range As Range
    Dim ar_rng As Variant
    Dim x As Integer, y As Integer, z As Integer
 
    On Error Resume Next
 
    xTitleId = "Invert_Table_Horizontally"
    Set wk_range = Application.Selection
    Set wk_range = Application.InputBox("Select a range of cells", xTitleId, wk_range.Address, Type:=8)
    ar_rng = wk_range.Formula
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For x = 1 To UBound(ar_rng, 1)
        z = UBound(ar_rng, 2)
        For y = 1 To UBound(ar_rng, 2) / 2
            xTemp = ar_rng(x, y)
            ar_rng(x, y) = ar_rng(x, z)
            ar_rng(x, z) = xTemp
            z = z - 1
        Next
    Next
 
    wk_range.Formula = ar_rng
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

⚡ Code Breakdown:

Now, I will explain the VBA code for flipping the table. In this case, the code is divided into 3 steps.

In the first portion, the sub-routine is given a name, here it is Invert_Table_By_Rows().
Next, define the variables and assign Range, Variant, and Integer data types respectively.
In the second portion, an input box prompts the user to enter the range of cells to flip.

In the third portion, use nested For Loop to iterate through all the values in the given range and swap their positions one by one.

Next, click the Macros button >> choose Invert_Table_By_Rows macro >> hit the Run button.

Following this, select the C4:L5 range of cells >> press the OK button.

Finally, the results should look like the picture shown below.

Utilizing Transpose Option to Convert Columns to Rows

Excel allows you to convert multiple columns in a table into rows using the Transpose option. Now, allow me to demonstrate the process in the steps below.
Assuming the Marks Distribution of Students dataset shown in the B4:F11 cells. Here, the dataset shows the Names of the students and their scores in Physics, Chemistry, Biology, and Maths respectively.

📌 Steps:

To begin with, select the entire dataset, in this case, the B4:F11 cells >> press CTRL + C on your keyboard.

Next, move to the B13 cell >> hit CTRL + ALT + V keys to open the Paste Special dialog box.

Now, select the Transpose option >> click the OK button.

Consequently, the result should look like the image given below.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

You can download the practice workbook from the link below.

Flipping Table.xlsm

Conclusion

In this article, I have shown you 2 effective ways how to flip table in Excel. Now, I’d suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

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

Get FREE Advanced Excel Exercises with Solutions!

How to Flip Table in Excel (2 Quick Ways)

How to Flip Table in Excel: 2 Ways

1. Flipping Table by Columns

1.1 Using Sort Option

1.2 Utilizing SORTBY Function

1.3 Employing INDEX Function

1.4 Applying VBA Code

2. Flipping Table by Rows

2.1 Using Sort Option

2.2 Employing VBA Code

Utilizing Transpose Option to Convert Columns to Rows

Practice Section

Conclusion

Related Article

Leave a reply Cancel reply