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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

how to flip data in excel from horizontal to vertical


1. Using Paste Special Feature to Flip Data from Horizontal to Vertical in Excel

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.

STEPS:

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

how to flip data in excel from horizontal to vertical

Note: A green dotted line will appear all around the data which mainly indicates that the data is now copied.
  • 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.

how to flip data in excel from horizontal to vertical

Read More: How to Change Vertical Column to Horizontal in Excel


2. Flip 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 instructions to use this function to flip data.

STEPS:

  • 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.
=TRANSPOSE(B4:F5)
  • Press Enter.

  • This will flip the data from horizontal to vertical and change the format.

how to flip data in excel from horizontal to vertical

  • To keep the format as it is, we have to manually organize the format.

Read More: How to Flip Columns and Rows in Excel


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

STEPS:

  • In the beginning, go to the File tab from the ribbon.

how to flip data in excel from horizontal to vertical

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

how to flip data in excel from horizontal to vertical

  • Thus, all the columns change to numbers.

  • At this point, put the formula into a selected cell.
=R4C2
  • 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.

how to flip data in excel from horizontal to vertical

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

how to flip data in excel from horizontal to vertical

Note: The flipped data will likewise change if you alter any input in the original data set.

Read More: How to Convert Multiple Rows to Columns in Excel


4. Combine INDIRECT and ADDRESS Functions to Flip Data from Horizontal to Vertical in Excel

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.

STEPS:

  • In the first place, select the cell where we will flip data.
  • After that, enter the following formula there.
=INDIRECT(ADDRESS(COLUMN(B4)-COLUMN($B$4)+ROW($B$4), ROW(B4)-ROW($B$4)+COLUMN($B$4)))
  • Further, to complete the operation hit the Enter key.

how to flip data in excel from horizontal to vertical

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

Note: You can edit the transposed data without altering the whole data set.

Read More: How to Move Data from Row to Column in Excel


5. Applying Excel VBA Macro to Flip Data from Horizontal to Vertical

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.

STEPS:

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

how to flip data in excel from horizontal to vertical

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

VBA Code:

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.

how to flip data in excel from horizontal to vertical

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

Read More: Excel Macro: Convert Multiple Rows to Columns


6. Transposing Data with 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.

STEPS:

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

how to flip data in excel from horizontal to vertical

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

how to flip data in excel from horizontal to vertical

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

how to flip data in excel from horizontal to vertical

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

how to flip data in excel from horizontal to vertical

Read More: How to Transpose Multiple Columns to Rows in Excel


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.

Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

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.


Related Articles


<< Go Back to Transpose Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo