Ratul Khan
If you have a worksheet with data in columns that you want to rotate so it’s rearranged in rows, you can use the Transpose feature. It lets you rotate the data from columns to rows or vice versa. In this article, I am going to show you how to convert columns to rows in Excel.
Download practice workbook
Convert Columns to Rows in Excel
1. Transposing Data
Here we have a dataset. The data shows Sales by Month in different countries. We will be converting the columns to rows.
Step-1
Select the whole dataset => Right Click on the dataset
Step-2
- Menu Bar appears
- Click on the Copy button
- The dataset will be highlighted.
Or after selecting the whole dataset, you could click on the Copy command on the Home Ribbon => Clipboard group of commands
Or press CTRL + C on the keyboard to select the dataset.
Step-2
- Menu Bar appears
- Click on the Copy button
- The dataset will be highlighted.
Or after selecting the whole dataset, you could click on the Copy command on the Home Ribbon => Clipboard group of commands
Or press CTRL + C on the keyboard to select the dataset.
A drop-down appears => Click on the Transpose (T) Formula.
Your dataset should now be like the following image.
2. Using the Transpose Function
Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don’t want that, you can type a formula instead using the TRANSPOSE function. To explain the method, Consider the dataset used in the previous formula.
Step-1
Select a blank cell. We select the A12 cell. Count the number of cells in A1:E6. In this case, it is 30 cells.Type =TRANSPOSE(
Excel will look like the picture below.
Step-2
Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to E6. So the formula for this example would be: =TRANSPOSE(A1:E6)
Step-3
Now press CTRL+SHIFT+ENTER. Because the TRANSPOSE function is only used in array formulas. An array formula is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1. The formula will be applied to more than one cell. Here’s the result after pressing CTRL+SHIFT+ENTER:
\
Transpose Formula & SPILL error
The Transpose formula version is bound to the original data. So, for example, if I change the value in C2 from 14 to 18, the new value will automatically be updated. But the reverse isn’t true. If I change any transposed cells, the original set will not change. Instead, we will get a SPILL error and the transposed data will disappear.
Transpose Formula & Blank Cells
In the Transpose formula, it will convert the empty cells to “0”s.
Things to Remember
The process of converting Column to Rows or Vice-Versa both methods also work when you want to convert a single column to a row or vice-versa.
Conclusion
So, these are the two easy methods on How to convert columns to rows in Excel. You can apply any of these two methods. We hope you find this article useful. Feel free to share your thoughts in the comment section.
