Ratul Khan
If you are looking for the Transpose feature to convert columns to rows or the inverse one in Excel, then you have come to the right place. In this article, we’re going to show you how to convert columns to rows in Excel.
Download practice workbook
You can download the Practice Workbook that we used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.
3 Methods to Convert Columns to Rows in Excel
Here, we will describe 3 methods to convert columns to rows in Excel. In addition, for your better understanding, we’re going to use the following dataset. Which contains 5 columns. They are Serial No, Products, Volume, Unit Cost, and Revenue.
1. Transposing Data to Convert Columns to Rows
Here, we have the dataset. The data shows Sales by Month in different countries. Now, we will be converting the columns to rows.
1.1. Use of Context Menu Bar
Here, we will use the Paste Options from the Context Menu Bar to convert columns to rows in Excel. Actually, this is the easiest option for converting columns to rows. Eventually, you can also convert rows to columns with this feature in Excel. Now, let’s see the steps given below.
- Firstly, select the whole dataset >> Right-Click on the dataset.
- Secondly, from the Context Menu Bar >> Click on the Copy button. Or, after selecting the dataset, press CTRL+C on the keyboard to copy the dataset.
As a result, the dataset will be highlighted.
- Now, select the B11 cell where we want to keep the output.
- Then, Right-Click on the cell >> from the Context Menu Bar >> choose Transpose (T) which is under the Paste Options.
Lastly, you will see the following image.
1.2. Applying Paste Special Feature
Here, we will use the Paste Special feature to convert columns to rows in Excel. Now, let’s see the steps given below.
Steps:
- Firstly, select the whole dataset.
- Secondly, from the Home Ribbon >> you may click on the Copy command which is under the Clipboard group of commands.
As a result, the dataset will be copied.
- Now, select the B11 cell where we want to keep the output.
- Then, from the Home tab >> go to Paste command.
- Then, choose Paste Special option.
At this time, a new dialog box named Paste Special will appear.
- Now, mark Transpose.
- After that, press OK.
Lastly, you will see the converted rows.
Read More: Convert Columns to Rows in Excel Using Power Query
Similar Readings
- Transpose Multiple Rows in Group to Columns in Excel
- VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
- How to Convert Column to Comma Separated List With Single Quotes
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
2. Use of INDIRECT & ADDRESS Functions in Excel
Now, you can use a combination of some functions to convert columns to rows in Excel. Here, we will use INDIRECT, ADDRESS, COLUMN, and ROW functions.
Steps:
- Firstly, you must select a cell, where you want to keep the result. Here, we have selected the B11 cell.
- Secondly, you need to use the corresponding formula in the B11 cell.
=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))- Thirdly, press ENTER.
Formula Breakdown
- Firstly, COLUMN(B4)—> returns the column number from a given reference.
- Output: {2}.
- Secondly, ROW(B4)—> returns the row number from a given reference.
- Output: {4}.
- Thirdly, ROW(B4) – ROW($B$4) + COLUMN($B$4)—> becomes {4}-{4}+{2}. Here, the Dollar Sign ($) will fix column B.
- Output: {2}.
- Fourthly, COLUMN(B4) – COLUMN($B$4) + ROW($B$4)—> becomes {2}-{2}+{4}. Here, the Dollar Sign ($) will fix column B.
- Output: {4}.
- So, the ADDRESS function will return the position of row number 4 and column number 2.
- Output: {“$B$4”}.
- Finally, the INDIRECT function will return the value of that cell.
- Output: SL No.
- After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in cells B12:B15.
- Similarly, you have to drag the Fill Handle icon to AutoFill the corresponding data in cells C12:G15.
Lastly, you will see the converted rows.
Read More: How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
3. Using TRANSPOSE Function to Convert Columns to Rows
Sometimes you may want to rotate cells. Actually, you may do this by the previous methods, and also by using the VBA with Transpose option. But that might create matched values. So, in this section we will see another way, you can use a formula using the TRANSPOSE function. To explain the method, we will consider the dataset used in the previous formula.
Steps:
- Firstly, select a blank cell. Here, we select the B11 cell. Count the number of cells in B4:F9. In this case, it is 30 cells.
- Secondly, type the range of the cells you want to transpose. In this example, we want to transpose cells from B4 to F9. So the formula for this example would be:
=TRANSPOSE(B4:F9)
- Then, press ENTER.
The formula will be applied to more than one cell. Here’s the result after pressing ENTER:
How to Solve TRANSPOSE Function & SPILL Errors in Excel
The Transpose formula version is bound to the original data. So, for example, if we change the value in D5 from 14 to 2, the new value will automatically be updated. But the reverse isn’t true.
If we change any transposed cells, the original set will not change. Instead, we will get a SPILL error and the transposed data will disappear.
How to Use TRANSPOSE Function with Blank Cells in Excel
The TRANSPOSE function will convert the empty cells to “0”s. Follow the image below, you will get the scenario.
Read More: Excel Transpose Formulas Without Changing References (4 Easy Ways)
How to Convert Rows to Columns in Excel
Here, you can also convert rows to columns in Excel. Let’s have the following dataset. Where the information is row-wise decorated. Now, we want to make a dataset that will be column-wise decorated.
Here comes the most interesting part, you can follow method-1 to convert the rows to columns. Actually, you can follow any of the methods. As the method 1 is the easiest one so we have mentioned it. After following the steps, you will get the following converted dataset.
Practice Section
Now you can practice the explained methods by yourself.
Things to Remember
Here, the process of converting columns to rows or vice-versa all those methods also works when you want to convert a single column to a row or vice-versa.
Conclusion
So, these are the three easy methods how to convert columns to rows in Excel. You can apply any of these three methods. So, we hope you find this article useful. Furthermore, feel free to share your thoughts in the comment section.
Further Readings
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- Data clean-up techniques in Excel: Changing vertical data to horizontal data
- How to Swap Rows in Excel (2 Methods)
- How to Reverse Order of Columns Horizontally in Excel
