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.
- 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.
- In the first place, navigate to the E5 cell and enter the expression given below.
Here, the B5:C14 cells refer to the Name and Sales values respectively.
- 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.
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!
- First and foremost, jump to the E5 cell and type in the expression given below.
In this formula, the B5:C14 cells refer to the Name and Sales values respectively while the B5:B14 cells indicate the Names.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
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.