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

## Dataset Overview

We will be working with the following dataset, which includes product names and their corresponding total prices. Our task is to rearrange the columns by flipping them and then organize them from a horizontal layout to a vertical one.

## Method 1 – Using Paste Special Feature to Flip Data from Horizontal to Vertical in Excel

In this first method, we will use the Excel Paste Special feature to flip data. Our main goal is to transpose the data using this feature. Transposing generates a new data arrangement where the original rows and columns are reversed. It automatically generates new variable names and provides a list of the new value labels. Excel includes a transpose feature that allows us to flip horizontal data to a vertical format.

STEPS:

• Select the entire dataÂ that is currently arranged horizontally.
• Navigate to the Home tabÂ in the ribbon.
• Click on Copyunder the Clipboard
• Alternatively, you can use the keyboard shortcutÂ Ctrl + Cto copy the entire data.

Note: A green dotted line will appear around the data, indicating that it has been copied.
• Select the cellÂ where you want to flip the data from horizontal to vertical. In this case, weâ€™ll choose cell B7.
• Go back to theÂ Home
• Click on theÂ Pastedrop-down menu under theÂ Clipboard
• SelectÂ Paste Special.
• Alternatively, you can use the keyboard shortcutÂ Ctrl + Alt + V to open theÂ Paste Special

• This will display the Paste SpecialÂ dialog box.
• Then, select theÂ TransposeÂ option as shown in the screenshot below.
• Finally, click theÂ OKÂ button to complete the process.

• After following the instructions above, youâ€™ll notice that the data has been flipped from horizontal to vertical.

## Method 2 – Flip Data from Horizontal to Vertical Using Excel TRANSPOSE Function

With theÂ TRANSPOSEÂ function, you can change the orientation of a group of cells from vertical to horizontal or vice versa. TheÂ TRANSPOSEÂ function flips the direction of a specified range. Letâ€™s follow the steps to use this function and flip the data:

STEPS:

• First, select the cell where you want to flip the data. In this case, weâ€™ll select cell B7.
• Next, enter the following formula into the selected cell:
`=TRANSPOSE(B4:F5)`
• Press Enter.

• After following these instructions, the data will be flipped from horizontal to vertical.

To maintain the existing format, we need to manually organize it.

Read More:Â How to Flip Columns and Rows in Excel

## Method 3 – Applying the Find & Replace Feature to Transpose Data in Excel

The Excel Find & Replace feature provides a variety of sophisticated options to help you refine your search and locate specific content. While itâ€™s possible to use this feature to flip data, it can be a bit tricky to do so.

To achieve this, follow these steps:

• Go to the File tab in the ribbon.

• Go backstage to the Excel menu bar.
• Click on Options.

• Navigate to the Formulas section.
• Checkmark the R2C1 reference style box under the Working with formulas options.
• Click OK.

As a result, all the columns will change to numbers.

• Place the formula into a selected cell:
`=R4C2`
• This will represent the 4th row and the 2nd column which is the Product Name.

• Instead of using the equal (=) sign, weâ€™ll reference the row and column. In our case, the reference is AA, resulting in the final output:Â AAR4C2.
• Drag the mouse cursor to fill the entire data range for flipping.

• Next, select the data and navigate to the Home tab.
• In the Editing group, click on the Find & Replace drop-down menu and choose Replace.
• Alternatively, you can use the keyboard shortcut Ctrl + H to open the Find & Replace dialog.

• The Find & Replace dialog box will appear.
• Enter AA in the Find what field and = in the Replace with field.
• Click Replace All.
• Finally, click Close.

As a result, youâ€™ve generated your flipped data.

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

## Method 4 – Combine INDIRECT and ADDRESS Functions to Flip Data from Horizontal to Vertical in Excel

STEPS:

• Select the Cell for Data Flipping:
• Choose the cell where you want to flip the data from horizontal to vertical.
• Enter the Formula:
• In that selected cell, enter the following formula:
`=INDIRECT(ADDRESS(COLUMN(B4)-COLUMN(\$B\$4)+ROW(\$B\$4), ROW(B4)-ROW(\$B\$4)+COLUMN(\$B\$4)))`
• Press the Enter key to complete the operation.

How Does the Formula Work?

• TheÂ ADDRESSfunction calculates the location of certain cells based on specific column and row values.
• TheÂ INDIRECTfunction then transfers the locations from one cell to another.
• Duplicate the Formula:
• Drag the Fill Handle down to duplicate the formula over the desired range.
• Alternatively, double-click on the plus (+) symbol to AutoFill the range.

• Replicate the Formula Horizontally:
• To replicate the formula across the range horizontally, drag the Fill Handle

• Result:
• Youâ€™ll notice that the data is now flipped from horizontal to vertical.
• Remember to manually adjust the format to maintain consistency.

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

## Method 5 – Applying Excel VBA Macro to Flip Data from Horizontal to Vertical

STEPS:

• Access the Developer Tab:
• Go to the Developer tab in the Excel ribbon.
• Open the Visual Basic Editor:
• From the Code category, click on Visual Basic to open the Visual Basic Editor.
• Alternatively, press Alt + F11 to directly open the Visual Basic Editor.
• You can also right-click on your worksheet and choose View Code to access the Visual Basic Editor.

• Enter the VBA Code:
• In the Visual Basic Editor, enter the following VBA code:

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``````
• This code flips the data from horizontal to vertical.

Run the Code:

• Click the Run Sub button or press the keyboard shortcut F5 to execute the code.

• Specify the Range:
• A window will appear, asking you to specify the range of data you want to flip.

As a result, your data will be flipped from horizontal to vertical.

Remember to manually adjust the format to maintain consistency.

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 begin the procedure and name itÂ Flip_Data. Next, we declare the variable names necessary 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 create a window that prompts us to specify the ranges we want to reverse. In this window, we define the title as Flip Data and the input 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 code block flips 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.

## Method 6 – Transposing Data with Power Query Tool in Excel

Power Query is an efficient tool for quickly transforming data in Excel. In this tutorial, weâ€™ll show you how to use Power Query to convert horizontal data into vertical format.

STEPS:

• Select the data which you want to flip.
• 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.
• The range you selected earlier will be displayed in the Where is the data for your table? field.
• Make sure the checkbox next to My table has headers is selected.
• Click OK to proceed.

• 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.
• Select Use Headers as First Row.
• This action will create a header for each column.

• Now, click on the Transpose option.

• This will flip the data from horizontal to vertical.

• Again, click on the Use First Row as Headers drop-down menu.
• Select Use First Row as Headers to remove the extra header.

• The Power Query Editor dialog box will close, and a new sheet containing the flipped data will be loaded into the workbook.

## Things to Keep in Mind

• Transpose Shortcut:
• When using the Transpose feature, pressÂ Ctrl + Shift + Enter instead of just Enter.
• Select a Range:
• Remember to choose a range of cells when transposing your dataset. The Transpose function works with larger amounts of data, not just a single data point.
• Excel VBA Code:
• If youâ€™re using Excel VBA code on your worksheet, make sure to save the file as anÂ Excel Macro-Enabled Workbook(with the extension .xlsm).

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF