How to Transpose Excel Data

Transpose in Excel

How to Transpose Excel Data with the TRANSPOSE Function. Transpose is returns a vertical range of cells as a horizontal range, or horizontal range of cells as a vertical range. The Transpose function is entered as an array formula in a selected range that has the same number of rows and columns as the source range has (equal number of rows and columns). To transpose literally means to allow two or more things to change places with each other. Essentially, this means flipping the data you want to change rows into columns and vice versa.

Reason we transpose our data?

Transpose creates a new data record in which the rows and columns within the original data 

file are transposed or rearranged in such a order that cases (rows) become variables and variables (columns) become cases. It allows you rotate the data from columns to rows, or vice versa.

Shortcut of how to transpose Excel Data

You can use the Ctrl + Alt + V as keyboard shortcut or the Paste button in the Home tab. You can select the All radio button under the Paste options. Then, check the Transpose box and Press the OK button.

Characteristics of the TRANSPOSE Function

The features of this function are:

  • It links the data to the original source.
  • It gives the value error if the number of rows and columns selected are not equal to the columns and rows of the source data.
  • Once you enter the data, any individual cell which is a part of this function cannot be changed.

Using TRANSPOSE Function as a Dynamic Array To Transpose Data in Excel


3 Steps On How to Transpose In Excel

Step 1: Select blank cells

Firstly select some blank cells. Be sure you select the same/equal number of cells as the original set of cells, but in the opposite direction. For example, below are 8 cells that are arranged vertically:

Now, we need to select eight horizontal cells, as shown below. The new transposed cells will end up here.

Step 2: Type =TRANSPOSE(

The next step is to type: =TRANSPOSE(…,With those blank cells still selected,

Excel will look similar to this as below. Note:  that the eight cells are still selected even though we have entered a formula.

Step 3: Type the range of the original cells that needs to be transposed

Now enter the range of the cells you want to transpose in worksheet. In the below example, we want to transpose data in cells from A1 to B4. So the transpose formula in this example would be: 

=TRANSPOSE(A1:B4) — , and go to the next step.

Excel will look similar to this as below:

Step 4: Finally, press CTRL+SHIFT+ENTER

Now press CTRL+SHIFT+ENTER. Note: the TRANSPOSE function is only used in array formulas, and that’s how you finish an array formula.

An array formula, in quick is a formula that gets applied to more than one cell. Because more than one cell is selected in step 1 ,the formula will get applied to more than one cell.

Below is the result after pressing CTRL+SHIFT+ENTER:

Transposing data is a very common task in excel irrespective of what program you’re working in with your data. Sooner or later you’ll come across the need to Transpose in Excel too!

Also read about our Blog on Sumif Function

Leave a comment

Your email address will not be published.