how to convert columns to rows in excel

原创 admin 2021-01-08 22:55 

Paste Special Transpose | Transpose Function | Transpose Table without Zeros | Transpose Magic

Use the 'Paste Special Transpose' option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function.

Paste Special Transpose

To transpose data, execute the following steps.

1. Select the range A1:C1.

2. Right click, and then click Copy.

Right Click Copy

3. Select cell E2.

4. Right click, and then click Paste Special.

5. Check Transpose.

Check Transpose

6. Click OK.

Paste Special Transpose Result

Transpose Function

To insert the TRANSPOSE function, execute the following steps.

1. First, select the new range of cells.

Select the New Range

2. Type in =TRANSPOSE(

3. Select the range A1:C1 and close with a parenthesis.

Transpose Function

4. Finish by pressing CTRL + SHIFT + ENTER.

Transpose Function in Excel

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range E2:E4 and press Delete.

Transpose Table without Zeros

The TRANSPOSE function in Excel converts blank cells to zeros. Simply use the IF function to fix this problem.

1. For example, cell B4 below is blank. The TRANSPOSE function converts this blank cell to a zero (cell G3).

Transpose Table

2. If blank, the IF function below returns an empty string (two double quotes with nothing in between) to transpose.

Transpose Table without Zeros

Transpose Magic

The 'Paste Special Transpose' option is a great way to transpose data but if you want to link the source cells to the target cells, you need a few magic tricks.

1. Select the range A1:E2.

2. Right click, and then click Copy.

Copy Data

3. Select cell A4.

4. Right click, and then click Paste Special.

5. Click Paste Link.

Click Paste Link


Paste Link Result

6. Select the range A4:E5 and replace all equal signs with xxx.

Replace All


Ready to Transpose

7. Use 'Paste Special Transpose' to transpose this data.

Transposed Data

8. Select the range G1:H5 and replace all occurrences of 'xxx' with equal signs (the exact opposite of step 6).

Transpose Magic

Note: for example, change the value in cell C2 from 16 to 36. The value in cell H3 will also change from 16 to 36.

版权声明: 本文为4find – 解决程序猿问题原创文章,欢迎转载。转载请务必注明出处! (参考格式:本文转自4find – 解决程序猿问题,原文地址: