As you know that people look at Excel data in their own way. Having data in rows might be sometimes needed to flip into columns or vice versa. Converting column data into rows and rows’ data into columns merely requires you to follow simple steps. For instance, if an Excel spreadsheet has data in horizontal cells, it might be possible that your boss prefers that data to be flipped vertically.
In this case, you will have to transpose Excel data to accommodate your preference. In Excel, you can use multiple methods for Excel flip rows and columns.
Before diving into the explanation of methods, let’s have a look at what is Transpose function used in Excel.
What is Transpose Function in Excel?
Using the transpose function let your data change its orientation such as the vertical range of your columns’ orientation and the horizontal range of your rows’ orientation. The transpose function is purely handy as well as versatile. For instance, this function could be used in Excel formulas or with paste options. Well, below you will find some differences that let you decide which method you should use.
Two major methods are described here for Excel flip rows and columns.
- Excel Ribbon Method
- Mouse Method
Let’s grab details with examples!
Excel Flip Rows and Columns with Copy and Paste
Below in this image, you can see the sales data location-wise.
This data is presented in horizontal order, whereas we need this data to be in the vertical orientation. Changed orientation will help us in comparison. Here are the easy to follow steps for converting columns to rows:
- Go to the Home tab after selecting the entire data.
- Under the Clipboard menu, choose the Copy option. Or else you may press CTRL + C for copying the data.
- Now, click on any blank cell from where you need the data to be.
- Hit the Paste option given under the Clipboard menu. See the image below.
A Paste dialogue box will open up. Now, select the option “Transpose.”
Doing this will flip the column to rows and make the orientation as we needed. Below is the final look of the converted datasheet.
Now, you can apply filters and see the data differently.
Excel Flip Rows and Columns with Mouse
Following these steps not only flip rows into columns, but you can even flip columns into rows as well. Let’s get into the final process.
Below in this image, you can see subject wise data of some students.
The above data is presented in columns and we will convert it into rows.
Here are the steps to follow:
- Select the entire data and right-click on it. You will see some options and choose Copy from the list.
- Now, click on an empty cell where you need to put that copied data.
- Once again, right-click and choose Paste Special option.
- A paste special dialogue box will appear once again. Choose the Transpose option and click on the OK button.
- Now, you can see the changed orientation of this data from columns to rows. Below you can see the final look in the image.
Moving your cursor on Paste Special option, you will see a list of options as you can see in the image below:
Flipping Multiple Rows and Columns in Excel Right from another Workbook
You have gone through two different methods to convert rows into columns or vice versa. The above method is perfect in case you need to flip rows into columns from another workbook with the help of an Excel desktop. To do this, you will have to keep both spreadsheets open for completing the copy and pasting work as explained earlier.
On the other hand, Excel Online does not work for different workbooks. Here you will not find the Paste Transpose option.
That’s why it is ideal to opt for the Transpose function for converting columns into rows and rows into columns.
Excel Flip Rows and Columns using VBA Macro
Using VBA can help you in executing multiple functions specifically when you need some customized functioning. If you are good at coding, VBA is for you. You can make all sorts of changes with just a few codes.
For instance, below you can see a coding script that lets you enjoy flipping rows into columns or vice versa:
Sub TransposeColumnsRows()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:=”Please select the range to transpose”, Title:=”Transpose Rows to Columns”, Type:=8)
Set DestRange = Application.InputBox(Prompt:=”Select the upper left cell of the destination range”, Title:=”Transpose Rows to Columns”, Type:=8)
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
Sub RowsToColumns()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:=”Select the array to rotate”, Title:=”Convert Rows to Columns”, Type:=8)
Set DestRange = Application.InputBox(Prompt:=”Select the cell to insert the rotated columns”, Title:=”Convert Rows to Columns”, Type:=8)
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
- Once this code is used, you have to go to the Visual Basic Editor (VBE). Use your mouse and go to the Developer tab => Visual Basic or else use the keyboard and press ALT + F11.
- Go to Insert => Module, in VBE.
- Copy and paste the script into the window.
- Shut down the VBE and go to Developer => Macros. You will find RowsToColumns macro.
- Click on the Run option.
- Choose the array to rotate and the cell to add the rotated columns.
All done!
Things to Consider When Converting Columns to Rows in Excel
You know one usage of Paste Special option mentioned above. In reality, you can use it for other purposes as well including Add, Multiply, Subtract, and Divide between datasets.
When you are selecting the cells, you may find #VALUE error, for instance, if the transposed data don’t have equal number of rows and columns to the number of rows and columns of the source data.
