Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use TRANSPOSE Function in Excel (3 Suitable Examples)

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.

excel transpose function


Download the Practice Workbook


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.

Syntax and Arguments in TRANSPOSE Function in Excel

  • Summary

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

  • Syntax
=TRANSPOSE (array)
  • Arguments
Argument Required/Optional Explanation
array Required Pass the array or range of cells to transpose
  • 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.


3 Practical Examples of Using the TRANSPOSE Function in Excel

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.
=TRANSPOSE(B7:H13)

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

  • After that, press ENTER.

Copy or Paste Data to use the TRANSPOSE function in Excel

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

Final output of method 1 to use the TRANSPOSE function in Excel


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.

Transpose Table Without Zeros to use the TRANSPOSE function in Excel

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 lite orange color.

Steps:

  • Firstly, use the following formula in cell B16.
=TRANSPOSE(IF(B7:H13="","",B7:H13))

Formula Breakdown

  • In the IF function we are checking if there are any blank cells in the given range or not. If there is any blank cell then print “”(blank cell), otherwise show the values from range B7:H13.
    • Here, B7:H13=”” → This is the logical_test argument.
    • “” → It indicates the [value_if_true] argument.
    • B7:H13 →  This refers to the [value_if_false] argument.
  • After that, if there are no blank cells then the TRANSPOSE function transposes the data from the range B7:H13.
  • 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.

Final output of method 2 to use the TRANSPOSE function in Excel


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.

Calculate Highest Total Salary for the Following Years by using TRANSPOSE function in Excel

Steps:

  • Firstly, enter the following formula in cell C16.
=MAX(MMULT(C11:H11,--(ABS(TRANSPOSE(COLUMN(C11:H11))-COLUMN(OFFSET(C11:H11,0,0,1,COLUMNS(C11:H11)-B16+1))-(B16-1)/2)<B16/2)))

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.

Formula Breakdown

  • The formula tests the ranges to see if there are enough consecutive columns. The results of those tests (1 or 0) are multiplied by the cell values, to get the total salaries.
  • TRANSPOSE function is used to do all the calculations after converting the rows into columns.
  • MMULT function is used to return the matrix product of two arrays.
  • MAX function is to find out the maximum salary from the given dataset.
  • Using two minus signs () next to each other causes the formula to convert a return value of “TRUE” into 1 and a return value of “FALSE” into 0.
  • Following that, hit ENTER and the sum of the highest salaries will be shown in cell C16 as demonstrated in the following image.

Final output of method 3 to use the TRANSPOSE function in Excel


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 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 them using the keyboard shortcut CTRL + C.

Using Transpose Option from Paste Options to use the TRANSPOSE function in Excel

  • 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.

Final output of method 4 to use the TRANSPOSE option in Excel


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”.

Manipulating Data to use the TRANSPOSE option in Excel

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.

Using Paste options to manipulate data by Transpose option in Excel

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.

Final output of method 5 to use the Transpose option in Excel


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.

Applying Keyboard Shortcut to use the TRANSPOSE option in Excel

  • 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.

Final output of the method 6 to use the Transpose option in Excel


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.

How to Delete TRANSPOSE Function in Excel

  • 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.

Final output of method 7 to Delete TRANSPOSE Function in Excel


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.

Common Errors  When They Show
Formatting problem TRANSPOSE does not carry over formatting.
#VALUE! If the formula has not been entered as an array formula. To solve this, the formula needs to be entered with CTRL+SHIFT+ENTER

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.

practice sectionto use the TRANSPOSE function in Excel


Conclusion

So, these are some of the practical examples to use the TRANSPOSE function in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo