How to convert data from columns to rows in excel is extremely simple

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.

How to convert data from columns to rows in excel is extremely simple Picture 1

Then click the location to convert data and right click on that cell -> select Paste Special .

How to convert data from columns to rows in excel is extremely simple Picture 2

When the Paste Special dialog box appears, put a checkmark in the box before Transpose

How to convert data from columns to rows in excel is extremely simple Picture 3

Finally click OK to complete.And here is the result of converting the column to row in Excel.

How to convert data from columns to rows in excel is extremely simple Picture 4

=> 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.

How to convert data from columns to rows in excel is extremely simple Picture 5

So we need to select eight horizontal cells, like the image below. This is where the new, swapped cells appear.

How to convert data from columns to rows in excel is extremely simple Picture 6

Step 2: Enter = TRANSPOSE (

While keeping those cells blank, enter: = TRANSPOSE (

How to convert data from columns to rows in excel is extremely simple Picture 7

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).

How to convert data from columns to rows in excel is extremely simple Picture 8

Then press CTRL + SHIFT + ENTER . This is the result after pressing CTRL + SHIFT + ENTER

How to convert data from columns to rows in excel is extremely simple Picture 9

* 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.