Effortless Data Rearrangement with Excel TRANSPOSE Function: A Quick Guide

Examples of the Excel TRANSPOSE Function

Here are few examples of using the Excel TRANSPOSE function in Excel:

Example 1: Basic Usage

Suppose you have data in the range A3:D6, and you want to switch the rows to columns or vice versa. In cell F3, input the following formula:

But, if you are using pre-dynamic array versions of Excel (Excel 2019 and earlier) and apply the formula described in the above example, you will encounter a #VALUE! error because they do not support array constants.

Now What?, you will find the solution in Example No. 2

Example 2: How to Use TRANSPOSE Function in Excel 2019 and Earlier Versions

First, count how many columns and rows are in the data you want to transpose

So, the data we want to TRANSPOSE has 4 columns and 5 rows.

Now, select some empty cells and make sure there are as many rows as the original table has columns and as many columns as the original table has rows.

Here, we select the same number of cells but they will be rotated from horizontal orientation to vertical, or vice versa.

After choosing some empty cells, type in the Transpose formula: =TRANSPOSE(A1:D5)

But wait, don’t press Enter just yet! Your Excel Transpose formula should look something similar like this:

To finish your formula correctly, press Ctrl + Shift + Enter.

After you press Ctrl + Shift + Enter, Excel will add {curly braces} around your Transpose formula. You will see them in the formula bar. Don’t try to type these braces yourself, it won’t work.

Look at the picture below. It shows that our original table has been successfully Transposed, turning 4 columns into 4 rows and 5 rows into 5 columns.

Example 3: TRANSPOSE With Paste Special

The TRANSPOSE feature, also available in Paste Special, provides a non-formula approach to transpose data. Here is how to use this feature.

• First, select the table you want to transpose and copy it. Then,
• Right-click on the cell where you want to transpose the data and choose ‘Paste Special‘ or you can also use keyboard shortcut Ctrl + Alt + V

• Now, the Paste Special dialog box will open.
• Select ‘TRANSPOSE’ check box and click ‘OK.

So, now you can see that the copied part has been transposed successfully

Choosing Between TRANSPOSE Function and TRANSPOSE (Paste Special)

Now, you have two options available: one is the TRANSPOSE function, and the other is the TRANSPOSE Paste Special. You can choose the option that suits your requirements.

Dynamic Link vs. Static Data:

• Use the TRANSPOSE function when you want a dynamic link between the original and transposed data.
• Choose TRANSPOSE (Paste Special) when you need a one-time transformation and want the transposed data to be static.

Formula vs. Copy-Paste:

• Use the TRANSPOSE function for a formula-based, dynamic transformation.
• Choose TRANSPOSE (Paste Special) for a quick, non-formula, static rearrangement.

Practice Workbook