Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the practice workbook from the link below.


2 Ways to Flip Table in Excel

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.

 how to flip table in excel

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.

Using Sort Option

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

 how to flip table in excel

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.

Sorting Descending Order

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

how to flip table in excel with Sort option


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.

Utilizing SORTBY Function

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

how to flip table in excel using SORTBY Function


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.

Employing INDEX Function

Using Fill Handle

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

 how to flip table in excel

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

how to flip table in excel using INDEX Function


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.

Applying VBA Code

This opens the Visual Basic Editor in a new window.

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

Inserting 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

VBA Code

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

VBA Code Explanation

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

Running VBA Code

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

how to flip table in excel with VBA Code

Read More: How to Flip Data from Horizontal to Vertical in Excel (6 Methods)


Similar Readings


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.

Using Sort Option

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

how to flip table in excel

Now, this opens the Sort wizard.

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

Sort by Rows

  • 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.

Sort Descending Order

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

how to flip table in excel with Sort Option


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

Employing VBA Code

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.

VBA Code Breakdown

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

Running VBA Code

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

Selecting Range

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

how to flip table in excel with VBA Code

Read More: How to Flip Data Horizontally in Excel (3 Effective Methods)


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.

Utilizing Transpose Option to Convert Columns to Rows

📌 Steps:

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

Copying Dataset

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

Using Paste Special

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

Using Transpose option

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

how to flip table in excel with Transpose option

Read More: How to Flip Columns and Rows in Excel (2 Easy Methods)


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.

Practice Section


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. And, visit ExcelDemy for many more articles like this.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo