How To Transpose In Excel

3.3 min read|Last Updated: January 11th, 2024|Categories: excel|
table of content

There are several ways to rearrange data in Excel. One of these ways is Transposing, which lets you convert rows to columns (or vice versa) easily, instead of typing the data one by one. For example, sometimes, we find out it’s better to switch the columns and rows. So in this tutorial, we’re going to learn how to do this.

There are two quick and easy ways to transpose the data:

  1. Paste Special
  2. The Transpose Function

The other way, which is not as simple as the two other ways, is using Arrays. If the data is the output of particular software, or if you always receive specific data with a specific format and tabulation, in the form of a table from a source, and you continuously need to transpose them in Excel using array formulas will make it easier. With this method, you will no longer need to perform repetitive operations of transposing with the two methods mentioned above.

Paste Special

To switch rows and columns in Excel  using the Paste Special option, follow these steps:

  1. Select your data.
  2. Copy the range you selected (use the Copy option from the menu by right-clicking on the rang or press Ctrl+C.)
  3. Select the cell you want to present the result, right-click and choose the Paste Special option.
How to use the Paste Special to transpose columns and rows (part 1)?
Picture 1- The Paste Special option in Excel

 

  1. From the Paste Special dialogue box, check the Transpose checkbox and press OK.
How to use the Paste Special to transpose columns and rows (part 2)?
Picture 2- Transpose by using the Paste Special option

 

Note: the shortcut of the transpose is the icon. You can find it from the Paste Special menu.