Flipping the data simply means swapping the column values. Therefore indicates the very last item in the column should be the first value in the opposite order, the next to last ought to be the second value, and so on, with the first value in a flipped column being the first value. A horizontal position is defined as a flat or level perpendicular to the plane surface; at a correct angle to the perpendicular. In this article, we will demonstrate some different effective ways to flip data from horizontal to vertical in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
6 Effective Methods to Flip Data from Horizontal to Vertical in Excel
While working with Microsoft Excel, we may need to reorder or rearrange the dataset we made before. We can do this by creating the dataset again with the required order. But this work is time-consuming. Excel has some amazing features and tools and also some built-in formulas to do this work just with a few clicks.
For example, we are going to use the following dataset which contains some product names and the total price of the products. Now, we need to rearrange the columns by flipping them and then putting them from a horizontal order to a vertical order.
1. Use Paste Special Feature to Flip Data from Horizontal to Vertical
In this first method, we will utilize the Excel Paste Special feature to flip data. We will mainly transpose the data using this feature. Transpose generates a new data source whereby the initial data document’s rows and columns are reversed. It generates new variable names instantly and provides a list of the new value labels. Excel has the transpose feature with which we will flip the horizontal data to vertical.
- Firstly, select the whole data which is in horizontal order.
- Secondly, go to the Home tab from the ribbon.
- Thirdly, click on Copy under the Clipboard category.
- Alternatively, you can use the keyboard shortcut Ctrl + C to copy the whole data.
- Further, select the cell where you want to flip the data from horizontal to vertical. In our case, we select cell B7.
- Similarly, go to the Home tab again.
- Then, click on the Paste drop-down menu under the Clipboard group.
- And, select Paste Special.
- Instead of doing this, you can use the keyboard shortcut Ctrl + Alt + V to open the Paste Special feature.
- This will display the Paste Special dialog box.
- Next, checkmark the Transpose option shown in the screenshot down.
- Finally, click on the OK button to finish the procedures.
- And, you will be able to see that the data is flipped from horizontal to vertical after following the above instructions.
2. Reverse Data from Horizontal to Vertical Using Excel TRANSPOSE Function
With the TRANSPOSE function, we may change the orientation of a group of cells from vertical to horizontal or from horizontal to vertical. Transpose flips the direction of a specified range, to put it another way. Let’s follow the instruction to use this function to flip data.
- To begin with, select the cell where you want to flip the data, so, we select cell B7.
- After that, enter the formula into that selected cell.
- Press Enter.
- This will flip the data from horizontal to vertical and change the format.
- To keep the format as it is, we have to manually organize the format.
3. Utilize Find & Replace Feature to Transpose Data in Excel
Excel Find & Replace feature offers a wide range of sophisticated alternatives to help you narrow down your search as much as is required to locate what you’re searching for. We can flip data with this feature, but this is a tricky way to do that.
- In the beginning, go to the File tab from the ribbon.
- This will take you backstage to the excel menu bar.
- Subsequently, click on Options.
- Further, go to Formulas.
- Checkmark the R2C1 reference style box, under Working with formulas options.
- Then, click OK.
- Thus, all the columns change to numbers.
- At this point, put the formula into a selected cell.
- This will represent the 4th row and the 2nd column which is the Product Name.
- Now, we will make a reference to the row and the column instead of the equal (=) sign. In our case, the reference is AA. The final output will be AAR4C2.
- Drag the mouse cursor to fill the whole data to flip them.
- After that, select the data and go to the Home tab.
- In the Editing group, click on Find & Replace drop-down menu and select Replace.
- Alternatively, you can use the keyboard shortcut key Ctrl + H to open the Find & Replace dialog.
- Thus, the Find & Replace dialog box will appear.
- Further, type AA in the Find what field and = in the Replace with field
- Then, click on Replace All.
- Finally, click on Close.
- Lastly, we have generated our flipped data.
Read More: How to Flip Rows in Excel (4 Simple Methods)
- How to Flip Axis in Excel (4 Easy Methods)
- Flip Data in Excel from Bottom to Top (4 Quick Methods)
- How to Flip Data Upside Down in Excel (4 Ways)
4. Combine INDIRECT and ADDRESS Functions to Flip Data
The INDIRECT function enables us to transfer the location from one cell to the other and then retrieve the content of the initial cell by recognizing the content of the subsequent. Given specific column and row values, we may use the ADDRESS function to find the location of a cell in a spreadsheet. We can flip data by combining the INDIRECT and ADDRESS functions.
- In the first place, select the cell where we will flip data.
- After that, enter the following formula there.
- Further, to complete the operation hit the Enter key.
🔎 How Does the Formula Work?
- ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4)): This will find the location of the certain cells and then do the appropriate calculation to flip data.
- INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4))): This transfers the locations from one cell into the other.
- Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.
- Further, to replicate the formula throughout the range, drag the Fill Handle rightwards.
- And, finally, you will be able to see that the data is flipped from horizontal to vertical. And, we manually change the format to keep the same format.
5. Apply VBA Macro to Flip Data from Horizontal to Vertical in Excel
With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. We can use the Excel VBA to flip data from horizontal to vertical by just writing a simple code. Let’s look at the steps for using the VBA code to do the work properly. For this, we are using the same dataset as before.
- Firstly, go to the Developer tab from the ribbon.
- Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor where we write our code to flip data from horizontal to vertical.
- Next, copy and paste the VBA code shown below.
Sub Flip_Data() Dim ran As Range Dim ary As Variant Dim int1 As Integer, int2 As Integer, int3 As Integer On Error Resume Next xTitleId = "Flip Data" Set ran = Application.Selection Set ran = Application.InputBox("Range", xTitleId, ran.Address, Type:=8) ary = ran.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For int2 = 1 To UBound(ary, 2) int3 = UBound(ary, 1) For int1 = 1 To UBound(ary, 1) / 2 xTemp = ary(int1, int2) ary(int1, int2) = ary(int3, int2) ary(int3, int2) = xTemp int3 = int3 - 1 Next Next ran.Formula = ary Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Range("B7:C11").Value = WorksheetFunction.Transpose(Range("B4:F5")) End Sub
- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- This will appear in a window that we made by writing some code lines. The window will ask for the ranges. Select the ranges, then, click OK to complete the procedure.
- And, you can see that the dataset is flipped from horizontal to vertical but without keeping the format as it is.
- We must manually organize the format in order to maintain the current layout.
VBA Code Explanation
Sub Flip_Data() Dim ran As Range Dim ary As Variant Dim int1 As Integer, int2 As Integer, int3 As Integer
Here, we start the procedure and name the procedure Flip_Data. Then, just declare the variable names which we need to execute the code.
xTitleId = "Flip Data" Set ran = Application.Selection Set ran = Application.InputBox("Range", xTitleId, ran.Address, Type:=8) ary = ran.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
Those lines are making the window, which will ask for the ranges that we want to reverse. There we define the title box as Flip Dats and the box name as Range.
For int2 = 1 To UBound(ary, 2) int3 = UBound(ary, 1) For int1 = 1 To UBound(ary, 1) / 2 xTemp = ary(int1, int2) ary(int1, int2) = ary(int3, int2) ary(int3, int2) = xTemp int3 = int3 - 1 Next Next ran.Formula = ary Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic
This block of code is flipping the data from horizontal to vertical.
Range("B7:C11").Value = WorksheetFunction.Transpose(Range("B4:F5"))
The line of code is making the data in vertical order.
6. Transpose Data Using Power Query Tool in Excel
A really efficient tool for swiftly transforming data in Excel is Power Query. This time, we’ll demonstrate how to use the Power Query tool to flip data from horizontal to vertical.
- Firstly, select the data which you want to flip.
- Secondly, go to the Data tab from the ribbon.
- Further, click on From Table/Range under the Get & Transform Data.
- The Create Table dialog box will appear and the range will show in the Where is the data for your table? the field which we chose at the beginning.
- Furthermore, the checkmark box beside My table has headers.
- Click OK.
- This will open the Power Query Editor window.
- Go to the Transform tab from the menu bar.
- Then, click on the Use First Row as Headers drop-down menu.
- Next, select Use Headers as First Row.
- This will make a header in each column.
- Now, click on the Transpose option.
- Consequently, this will flip the data from horizontal to vertical.
- Again, click on the Use First Row as Headers drop-down menu and select Use First Row as Headers.
- And, you can see the extra header will be removed now.
- Go to File and select Close & Load.
- The Power Query Editor dialog box will be closed and consequently, a new sheet containing the flipped data will be loaded into the workbook.
Things to Keep in Mind
- When utilizing the Transpose feature, press Ctrl + Shift + Enter instead of just Enter.
- Don’t forget to pick a range of cells when transposing your dataset because the Transpose function works with big amounts of data, not just a single data point.
- While using Excel VBA code on your worksheet, make sure you save the file with Excel Macro-Enabled Workbook and the extension will be .xlsm.
The above methods will assist you to Flip Data from Horizontal to Vertical in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!