Sometimes you mistype data in a table in a column by which the correct data is in rows. Is there a way to turn a column into a row instead of having to retype the data? Very simple with the following 2 ways.
Method 1: Use Transpose in Paste Special
Step 1 : Open the Excel data file you want to convert the column to row
Step 2 : Select (black out) the columns (or rows) you want to convert into rows (columns) -> then press Ctrl + C to copy (or right-click and select copy function) in the function table that appears when Right click.
Then click the location to convert data and right click on that cell -> select Paste Special .
When the Paste Special dialog box appears, put a checkmark in the box before Transpose
Finally click OK to complete.And here is the result of converting the column to row in Excel.
=> This is the simplest way to convert columns into rows on an Excel spreadsheet, so you save a lot of time and effort compared to manually having to re-enter data if you get it wrong.
Method 2: Use the TRANSPOSE function directly in the spreadsheet
Usually you will rotate, convert cells by copying, pasting and using the Swap option.But doing that will create duplicate data.If you do not want to, you can use the TRANSPOSE function.
Step 1:Select the blank cells
First, select some blank cells.However, make sure to select the same number of cells as the original set of cells but in a different direction.
So we need to select eight horizontal cells, like the image below. This is where the new, swapped cells appear.
Step 2: Enter = TRANSPOSE (
While keeping those cells blank, enter: = TRANSPOSE (
Note that eight cells are still selected even after we have started entering the formula.
Step 3:Enter the range of original cells
Now enter the range of cells you want to swap, the formula will be: = TRANSPOSE (A1: B4).
Then press CTRL + SHIFT + ENTER . This is the result after pressing CTRL + SHIFT + ENTER
* Why is CTRL + SHIFT + ENTER instead of ENTER as usual functions
Why?Because the TRANSPOSE function is only used in array formulas and that is how you complete the array formula.An array formula, in short, is a formula that will be applied to multiple cells.Because you selected multiple cells in step 1 (you did that, didn't you?), The formula will be applied to multiple cells.