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.

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

**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

*version installed in your system, you can use the*

**Microsoft Excel 365****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**.

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 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 theargument.*logical_test* **“”**→ It indicates theargument.*[value_if_true]***B7:H13**→ This refers to theargument.*[value_if_false]*

- Here,
- 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.

### 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**.

`=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.

## 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**.

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

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.

## 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**.