How to Use TRANSPOSE Function in Excel?

While working with Microsoft Excel, some users prefer the formation of data vertically in columns, while others choose horizontal arrangements in rows. In situations where you need to immediately modify the orientation of a given range, TRANSPOSE is the function to use. Here in this article, we will see how to use the TRANSPOSE function in Excel.

Let’s see a quick view of the TRANSPOSE function in the following image.

Introduction to Excel TRANSPOSE Function

Now, we will discuss the basics of the TRANSPOSE function in Excel. At this stage, let’s discuss the syntax and arguments of the TRANSPOSE function in Excel.

• Summary

This function converts a vertical range of cells to a horizontal range or vice versa.

• Syntax

• Arguments

• Version

The TRANSPOSE function is available from Excel 2007. If you have the Microsoft Excel 365 version installed in your system, you can use the dynamic array feature.

How to Use TRANSPOSE Function in Excel: 3 Suitable Examples

In this section of the article, we will see three practical examples of using the TRANSPOSE function in Excel. Let’s have a dataset of sales information with salesperson names and the months. The data is all in a vertical format. Now, our task is to rotate the whole dataset into a horizontal formation by converting its rows to columns.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

Example 1: Copy or Paste Data

If data are arranged horizontally or vertically in a worksheet, we can use the TRANSPOSE function to list the data vertically, in a different location. And we can use the function as a formula in two ways with a dynamic array formula. We will convert them into a horizontal format using a dynamic array formula with the TRANSPOSE function. So, let’s use the steps outlined below to do this.

Steps:

• Firstly, enter the following formula in cell B16.

Here, the range of cells B7:H13 indicates our entire dataset.

• After that, press ENTER.

Consequently, all the data will be transformed from a vertical to a horizontal formation, as demonstrated in the following picture.

Example 2: Transpose Table Without Zeros

When we transpose a data table in Excel, if there are any blank cells on that table, the blank cells are automatically replaced with zeros. This phenomenon is shown in the following image.

Now, our task is to transpose the table without zeros using the TRANSPOSE function. Our dataset will be like the following image. Here, we have some blank cells marked in light orange color.

Steps:

• Firstly, use the following formula in cell B16.

• Then, hit ENTER.

As a result, we can see all the blank cells have remained the same as in the source table as shown in the following picture.

Example 3: Calculate Highest Total Salary for the Following Years

Now, we will use the TRANSPOSE function to calculate the highest total salary for consecutive years. Let’s say, we have a dataset of employees’ salary records. All the datasets are organized in a horizontal position. Now our task is to find out the last four years’ highest salary summation using a formula. Let’s consider whether we will get the highest total salary for the following years for Morgan.

Steps:

• Firstly, enter the following formula in cell C16.

Here, the range of cells C11:H11 refers to the salaries of Morgan from the year 2015 to 2020, and cell B16 indicates the number of Years.

• Following that, hit ENTER and the sum of the highest salaries will be shown in cell C16 as demonstrated in the following image.

Alternatives of Using TRANSPOSE Function

In Excel, we can achieve the outputs of the TRANSPOSE function by following some easy, and quick alternatives. In the following section, we will discuss these alternatives to the TRANSPOSE function.

1. Using the Transpose Option from Paste Options

The Transpose option from the Paste options can be used as an effective alternative to the TRANSPOSE function.

1.1 Rotating Data from Rows to Columns

Now, we will rotate the data from rows to columns using the Transpose option of Excel, later on, we will discuss how we can revert back to the original dataset. Now, let’s follow the steps mentioned below.

Steps:

• Firstly, select the dataset and copy it using the keyboard shortcut CTRL + C.

• After that, select a new location from where you want to start your new table. In this case, we have selected cell B16 as our destination cell.
• Then, go to the Home tab from Ribbon.
• Next, click on the Paste option from the Clipboard group.
• Afterward, choose the Transpose (T) option from the drop-down.

After clicking on the Transpose (T) option, all the data will be formatted vertically as shown in the image below.

• If you want to go back to the original dataset, copy it again with the keyboard shortcut CTRL + C.

• Following that, select the destination cell. Here, we choose cell B25.
• Then, use the same procedure mentioned earlier to revert back to the original dataset and you will get the following output on your worksheet.

1.2 Manipulating Data

In this example, we will manipulate data at the time of transposing using the TRANSPOSE option. Let’s say, we have a dataset of two team players’ names. The dataset is horizontally oriented. Now our task is to transpose the dataset vertically and rewrite all the names using this formula “Team Name – Player Name”.

Steps:

• Firstly, we will go for Team A. Select the data from Team A only and copy them using the keyboard shortcut CTRL + C.

• Following that, select a destination cell where you want to start your new table. In this case, we have chosen cell B16 as our destination cell.
• Afterward, go to the Home tab from Ribbon.
• Now, click on the Paste option from the Clipboard group.
• Subsequently, choose the Paste Link (N) option from the drop-down.

As a result, you will have the following output on your worksheet as marked in the image below.

• Now, use the keyboard shortcut CTRL + H to open the Find and Replace dialogue box.
• After that, in the dialogue box, type = in the Find what box.
• Next, type “Team A –” in the Replace with field.
• Following that, click on the Replace All option.

• Subsequently, Excel will pop up a dialogue box with a confirmation. Click on OK in that dialogue box.
• Then, click Close in the Find and Replace dialogue box.

• After that, select the marked cells as shown in the following image and press CTRL + C to copy the cells.

• Then, select a destination cell where you want to start your new table. In this case, we have chosen cell B8 as our destination cell.
• Afterward, go to the Home tab from Ribbon.
• Now, click on the Paste option from the Clipboard group.
• After that, choose the Transpose (T) option from the drop-down.

Consequently, selected data will be transposed vertically, as demonstrated in the following picture.

• Now, follow the same procedure for Team B and you will get the following output on your worksheet as shown in the image below.

2. Applying Keyboard Shortcut

In Excel, we have a handful of keyboard shortcuts to achieve our desired outputs. The output TRANSPOSE function can also be achieved by using two keyboard shortcuts. These shortcuts are described in the following section.

🔁 Keyboard Shortcut 1

This keyboard shortcut provides a one-click output just like the TRANSPOSE function. Now, use the steps outlined below.

Steps:

• Firstly, select the entire dataset and press CTRL + C to copy the dataset.

• After that, select the destination cell.
• Then, use the keyboard shortcut ALT + H + V + T.

That’s it! The keyboard shortcut will swap the rows and columns of the original dataset, and you will get the following output, as shown in the following picture.

🔁 Keyboard Shortcut 2

This shortcut includes the keyboard shortcut for the Paste Special command in Excel. Let’s apply the steps discussed in the following section to do this.

Steps:

• To begin, select the entire dataset and copy it using the keyboard shortcut CTRL + C.

• Following that, select the destination cell.
• Then, use the keyboard shortcut CTRL + ALT + V. This will open the Paste Special dialogue box.

• Now, press E from your keyboard.
• Finally, hit ENTER.

Consequently, you will have the following output on your worksheet.

How to Delete TRANSPOSE Function in Excel

In this section of the article, we will learn how we can delete the TRANSPOSE function in Excel. So, let’s follow the steps mentioned below to do this.

Steps:

• Firstly, use the steps mentioned in the first method to get the following output.

• After that, select the first cell of the output table.
• Then, press the DELETE key from your keyboard.

Consequently, the TRANSPOSE formula will be deleted from the entire output table all at once.

Common Errors While Using TRANSPOSE Function in Excel

We face some unexpected errors while using the TRANSPOSE function in Excel. Now, we will show these errors as they appear.

Things to Remember

• If you also need to transpose the text and cell formatting, then try to use the copy-and-paste options. You can also use the Transpose option to do this. But this may create duplicates. So, if your original cells get changed, then the copies won’t update automatically.
• You don’t have to type the range by hand. After typing =TRANSPOSE(, you can choose the range with your mouse. Simply click and drag from the start of the range to the finish.
• As an array function, TRANSPOSE does not support modifying part of the array it returns. To edit a TRANSPOSE formula, select the entire range the formula refers to, make the desired change, and press ENTER to save the updated formula.
• In the older versions of Excel, you need to press CTRL + SHIFT + ENTER after writing the TRANSPOSE function.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Download Practice Workbook

Conclusion

So, these are some of the practical examples of how to use the TRANSPOSE function in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

<< Go Back to Excel Functions | Learn Excel