TRANSPOSE In Excel

What Is TRANSPOSE In Excel?

TRANSPOSE in Excel is a function to rotate (switch) data from rows to columns and vice versa. It is a part of the LOOKUP and reference functions. In addition, the TRANSPOSE function helps users sort unformatted data in the desired format.

TRANSPOSE function in Excel also converts the range of table array from vertical to horizontal and vice versa. Its formula is:

=TRANSPOSE(array)

For example, the following image shows the array of three numbers 10, 20, and 30 as input in row 2. We will transpose the table data into column E with the TRANSPOSE formula.

  • Select the empty cell E2:E4.
  • Type the formula =TRANSPOSE(A2:C2).
  • Press CTRL+SHIFT+ENTER.

The output is 10, 20, and 30 in column E as a vertical representation.

Syntax Of TRANSPOSE() In Excel

The following image shows the syntax of the TRANSPOSE() in excel:

  • array = This is the range of cells to be transposed.

How To TRANSPOSE In Excel?

Transposing Excel data can be tricky and difficult. There are three approaches to TRANSPOSE Excel data. Let us look at those along with examples to understand how to use the TRANSPOSE formula:

1. Paste Special

It is quick and easy to use. However, this approach is best used when you want to transpose data as a one-off action and do not need to transpose data repeatedly for months, weeks, or days.

The following image depicts the Roll Number, Names, and Marks of 7 art students in a vertical table array. Next, we need to transpose the vertical table data into a horizontal data table with the Paste Special Transpose method.

In the table, the data is reflected as below:

  • Column A shows Roll Number
  • Column B contains the Names of the students
  • Column C contains Marks of the students

Let us look at the step-by-step explanation of the Paste Special Transpose approach:

  • Step 1Select the table array range A2:C9, which is to be transposed.
  • Step 2Right-click and click “Copy.”
  • Step 3Select the empty cell F2, where you want to transpose the data.
  • Step 4Right-click and click “Paste Special.”
  • Step 5The Paste Special” window pops up. The shortcut key for opening the “Paste Special” window is CTRL+ALT+V.
  • Step 6Select the checkbox “Transpose” and click “OK
  • Step 7: The output comes as follows in the cells F2:M4.

Therefore, the horizontal data table is transposed into the vertical data table.

2. TRANSPOSE Function

The TRANSPOSE function in Excel shifts data from horizontal to vertical representation and vice versa. This function is very simple to understand. It takes only one table array which you want to transpose.

Syntax of TRANSPOSE in Excel

  • array = This is the range of cells to be transposed.
Example #1

The following image depicts a company’s invoice, featuring Invoice ID, Date, Unit Price, Tax, and Total Price in a vertical table array. Next, we must transpose the vertical data table into a horizontal data table with the TRANSPOSE function.

In the table, the data is reflected as below:

  1. Column A shows Invoice ID
  2. Column B contains the Date
  3. Column C contains the Unit Price of the products
  4. Column D contains the Tax amount
  5. Column E contains the Total amount.
  • Step 1: Select the empty cell H1:M5.

Step 2: Type the formula =TRANSPOSE(A2:E7).

Step 3: Press CTRL+SHIFT+ENTER.

The output comes as follows in the cells H1:M5 as the horizontal representation.

Therefore, the horizontal data table is transposed into the vertical data table.

Example #2

The following image depicts the company’s employee ID, Names, and Salary in a horizontal table array. We need to transpose the horizontal data table into a vertical one with the TRANSPOSE function.

In the table, the data is reflected as below:

  1. Row 1 shows Emp. ID
  2. Row 2 contains Emp. Name
  3. Row 3 contains the Annual Salary of the employees
  • Step 1: Select the empty cell A6:C11.
  • Step 2: Type the formula =TRANSPOSE(B1:G3).

Step 3: Press “CTRL + SHIFT + ENTER”.

The output comes as follows in the cells A6:C11 as the vertical representation.

Therefore, the vertical data table is transposed into the horizontal data table.

3. Power Query Tool

It is a method of choice when we repeatedly get data from the same source, and we need to transpose it. Also, it is a quick and powerful tool.

The following image depicts sales of textile business in Europe, Asia, and North America. First, we need to transpose the horizontal data table into a vertical one using the Power Query Tool method.

In the table, the data is reflected as below:

  1. Row 1 shows Sales by Region
  2. Row 2 contains the total sales revenue in Europe
  3. Row 3 contains the total sales revenue in Asia
  4. Row 4 contains the total sales revenue in North America

Let us look at the step-by-step explanation of the Power Query Tool approach:

  • Step 1Select the data table array B1:F4 to be transposed.
  • Step 2Click the “Data” tab.

Step 3Select “From Table/Range” under the “Get & Transform Data” option.

  • Step 4The “Create Table” dialogue box opens. Enter the table array range $B$1:$F$4.
  • Step 5Then, the “Query Editor” dialogue box opens.
  • Step 6Select the “Transform” tab.

Step 7In the “Use First Row As Headers” selection, choose the “Use Headers As First Row” option. This ensures that the table’s header is treated as a part of the data and transposed.

Step 8Then click the “Transpose” option.

Step 9Go to the “File” tab, and click the “Close & Load” option.

Step 10The transposed data is now loaded on the new sheet of the same workbook.

Important Things To Note

  • The TRANSPOSE function links data to the source.
  • The formula only copies values, not the data format.
  • It is an array function, so users need to use CTRL+SHIFT+ENTER, not just the ENTER.
  • It returns the #VALUE! error if the number of rows is not equal to the number of columns and rows of the source data.
  • The result cannot be changed once the function is entered into the cell. Instead, the whole array needs to be deleted to change.
  • The function can be combined with IF Function, CONCATENATE Function, INDEX function, MATCH Function, etc.
  • It can also be used with the LOOKUP function in Excel.
  • The TRANSPOSE function accepts the mandatory argument, “array.”
  • It is very easy to use in Microsoft 365, and the results will automatically come up as an array of values.
  • If you want the transposed results to update when you change the source data, you need to type the TRANSPOSE formula into the cell where you want the transposed data. The data will flow down and to the right of the cell with the formula; no need to copy and paste.
  • The TRANSPOSE function is dynamic. The result data automatically changes when the changes are done in the source data table.

Frequently Asked Questions (FAQs)

1. What does it mean to transpose data?

Transpose means two or more things changing their places with each other. For example, the TRANSPOSE in Excel formula flips data of the table array from horizontal to vertical and vice versa.

=TRANSPOSE(array)

The TRANSPOSE function changes the position of data, like the first row of the array becomes the first column, the second row becomes the second column, and so on. Only cell values are copied and shifted. The TRANSPOSE function does not do the formatting of the cell. Transposing data can be a very time-consuming technique.

2. What is TRANSPOSE in Excel shortcut?

You can locate the TRANSPOSE function in Excel using the following steps:

1. Copy the cell range of the table array.
2. Select the empty cell where you want to paste the data.
3. Right-click and select the “Paste Special.”A window pops up of “Paste Special.”
4. Select the checkbox of “Transpose” under the “Operation” option.
5. Click OK.

Transpose Function - FAQs 1

3. How do I TRANSPOSE data in Excel?

Here is the step-by-step representation of how the TRANSPOSE function transposes the cell content:

1. Select a blank cell.
2. Type the formula =TRANSPOSE(array)
3. Select the range of the table array which you want to transpose).
4. Press CTRL+SHIFT+ENTER.

For example, the following image shows the table array of alphabets – aa, bb, cc, and dd as input in the column. We will transpose the data from horizontal representation into a row representation with the TRANSPOSE formula.

• Select the empty cells D2:G2 where we want to transpose data.

Transpose Function - FAQs 2

• Type the formula =TRANSPOSE(A2:A5).

Transpose Function - FAQs 3

• Press CTRL+SHIFT+ENTER.
• The Output comes as aa, bb, cc, and dd in a row as a horizontal representation.

Transpose Function - FAQs 4

Download Template

This article must be helpful to understand the TRANSPOSE function in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to TRANSPOSE in Excel. Here we learn the top 3 methods (Paste special, Formula and Power Query) to transpose data in excel along with examples and a downloadable template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.