Sometimes, you need to convert the given values in a spreadsheet from rows to columns. Excel allows you to rotate data from rows to columns or vice versa.
It does not matter why you need to do it, Excel converts row to column, and vice versa function can be performed easily without creating a mess. You can do it manually or automatically depending on how you like it. Today, you will get to know how you can switch rows data to columns. It is basically transposing data that has several ways to execute it.
Let’s dig out!
How Excel Converts Row to Column?
We have two different approaches that convert rows to columns in Excel:
- Transposing Rows to Columns using Paste Special
- Transposing Rows to Columns using TRANSPOSE Formula
Transposing Rows to Columns using Paste Special
Below we have a dataset containing country names in columns. The list of countries you can see is lengthy that’s why it would be perfect to change columns to rows to make the sheet presentable.
Follow the steps given below:
- Choose the original data and select the entire table, such as every cell with data in a spreadsheet. Now, press CTRL + Home and then CTRL + Shift + End.
- Now, copy all the selected cells. You can copy the cells with right-click from the mouse or by pressing CTRL + C.
- Choose the first cell of the range. Make sure that cells are selected outside the collection of the original data. Also, ensure that the copy section and paste section partly cover each other. For instance, if you have 4 columns and 10 rows, you will have 10 columns and 4 rows table.
- Right-click on the targeted cell and open the Paste Special option given in the context menu. Now, choose the Transpose option.
Note: If the source data uses formulas, ensure that you are using relative and absolute references rightfully. It also depends on whether you need to adjust them or left them locked.
Converting data from rows to columns will not take much time. While applying this method, you will see the formatting of the original data is being copied as well. Using this method will let you face two downsides. That’s why this approach somehow does not seems perfect.
You will find it unsuitable for converting fully functional Excel tables. Copying the whole table and then opening the Paste Special dialog box will let you know the Transpose option is not enabled. In this situation, you will have to copy the table without column headers or convert it.
Choosing Paste Special option and then Transpose does not allow the new table to appear with the original data. The process needs to be repeated when the source data changes. Definitely, nobody likes to waste time switching the same rows and columns repeatedly.
How Excel Convert Row To Column using TRANSPOSE?
Using the TRANSPOSE function in Excel allows you to rotate rows and columns in Excel. Below is the syntax:
=TRANSPOSE(array)
array – it uses an array of columns or rows to transpose
generally the TRANSPOSE connects the converted columns or rows with the source rows or columns. Therefore, when the values convert in the rows, you will see the same changes in the converted columns.
Things to Consider
Using Paste Special method let you transpose rows to columns. You can connect the data to the original data by selecting “Paste Link” from the “Paste Special” dialog box.
Apart from that INDIRECT formula of Excel can be used along with ADDRESS functions to convert rows to columns and vice versa.
