Technology

Detailed, Simple, and Fast Guide to Using the TRANSPOSE Function in Excel

If you are looking to switch the columns and rows in Excel and Google Sheets, you can use the TRANSPOSE function. This function allows you to convert columns into rows and vice versa. If you are unfamiliar with how to use the TRANSPOSE function, do not worry. In this detailed tutorial, we will guide you through the simple and quick steps to use this function effectively.

Administrator

25 December, 2023

100

What is the TRANSPOSE function?

The TRANSPOSE function is used to quickly and easily convert columns into rows and rows into columns in Excel.

Instructions on how to use the TRANSPOSE function in Excel

Applications of the TRANSPOSE function:

Saves a significant amount of time when entering data.
Assists with quick conversion, ensuring accurate data.
Helps users operate more professionally.

The formula of the TRANSPOSE function

Formula: =TRANSPOSE(array)

Where “array” is the range reference of the data you want to convert from rows to columns or vice versa.

Example of the TRANSPOSE function

For example, you want to change the display of data from vertical to horizontal as follows:

To obtain the result as shown in the image above, you need to follow these steps:

Step 1: Select the destination range by clicking on the first cell, then dragging the mouse to the last cell in the data range. The selected destination range should have the same number of rows and columns as the original data range.

Step 2: Next, simply enter the formula of the TRANSPOSE function in the first cell at the top left corner of the selected data range.

You can see 2 curly braces before the first character and the last character of the formula in the formula bar. These 2 curly braces are indicators that the selected cell is an array formula.

Detailed instructions on using the TRANSPOSE function

This guide is for Excel 2007, 2010, 2013, 2016, 2019 versions, Windows 10 operating system, and MacOS.

For example: Use the TRANSPOSE function to convert column data into row data in the following table:

Step 1: Select the rows and columns you want to convert

Step 2: Enter the formula =TRANSPOSE(C4:D6)

Step 3: Press Ctrl + Shift + Enter, the screen will display the result.

Note:

The TRANSPOSE function formula is only applied to multiple cells. If you are using the latest Microsoft 365 version, you only need to enter the formula at the top left corner of the output range, then press Enter.
If you change the data in the original data, the data range where the TRANSPOSE function formula is applied will also change.
You should not use this formula on a large range as it may cause the software to freeze, display N/A errors, or slow down the system, resulting in wasted time.

Some tips when using the TRANSPOSE function

When converting data from columns to rows and vice versa, you can apply the following tips to save time:

After you enter “=TRANSPOSE(“, you can select the cell range by clicking and dragging the mouse from the start to the end of the cell range.
You cannot copy the exact column and row formatting when using the TRANSPOSE function. If you want to copy the formatting, right-click, then click on the Transpose (T) option in the Paste Options menu. However, you should note that if you make any edits to any cell in the original data range, the new data will not be updated in the table after the conversion.
If you need to create a separate data range for convenience in tracking, while retaining the data in the original range, you can use the TRANSPOSE function to freely convert rows into columns and vice versa as desired. In that case, the data in the destination range will be automatically updated when the data in the original range changes. You don’t have to go through the hassle of reviewing and updating the new data anymore.