The value in cell H3 will also change from 16 to 36. Note: for example, change the value in cell C2 from 16 to 36. Being a part of the Excel lookup and reference. Select the range G1:H5 and replace all occurrences of 'xxx' with equal signs (the exact opposite of step 6). The TRANSPOSE function in excel helps rotate (switch) the values from rows to columns and vice versa. Use ' Paste Special Transpose' to transpose this data.Ĩ. To use it in older versions of Excel (2013 2019) follow these steps: Select the range of cells where the transposed data should appear. Suppose we are given the data below: To transpose the data, we will use the following formula: To enter the formula, follow the steps below: First, we select some blank cells. Method 2: How to Transpose Data in Excel Using the TRANSPOSE Function The TRANSPOSE formula in Excel automatically switches the orientation of ranges from rows to columns and vice versa. To understand the use of TRANSPOSE, let us consider an example: Example. Select the range A4:E5 and replace all equal signs with xxx.ħ. TRANSPOSE is a built-in function that can be used as a worksheet function in Excel. Right click, and then click Paste Special.Ħ.

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.Ĥ. If blank, the IF function below returns an empty string (two double quotes with nothing in between) to transpose. The TRANSPOSE function converts this blank cell to a zero (cell G3).Ģ. Simply use the IF function to fix this problem.ġ.

The TRANSPOSE function in Excel converts blank cells to zeros.

Excel formula to transpose data