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.

**Table of Contents**hide

## 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 theargument that refers to the*array**Names and Sales*values. Following,**ROW(B5:B14)**represents theargument which returns the row numbers of the*by_array1**Names and Sales*values. Lastly,**-1**is the optionalargument which indicates the*sort_order1***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 theargument which is the marks scored by the students. Next,*array***ROWS(B5:$B$14)**is theargument which indicates the row location. Lastly,*row_num***COLUMNS($B$5:B5)**is the optionalargument that points to the column location.*column_num***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.

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

**Read More:** **How to Flip Data Vertically in Excel**

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

**Read More: ****How to Flip Data Horizontally in ExcelÂ **

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

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