How to use Transpose function?


This function gives the advantage of turning the way the data is presented in the worksheet. Excel transpose Formula converts horizontal data into a vertical one or vertical data into a horizontal one. The Transpose Function is one such option for changing the way data is presented.

You can flip your data located in rows to columns or columns to rows. You can transpose a single row or column or multiple set of rows or columns using the Transpose function.

The main purpose of this function is to flip the range of cells. It returns in a new oriented format. This function is also at an in-built function in Excel categorized under Lookup/Reference Function. It is a worksheet function in Excel so it can be entered as a part of the formula. This function applied to all the Excel versions from 2000 to 2016.

There are two ways you can use the Transpose function in Excel. The first way is to use the Copy and Paste Option to transpose data and the second way is to use the Excel Transpose formula. This tutorial will explain how you can transpose data in these two ways.

Transpose Data using Copy and Paste Special option

One of the ways to use transpose data is to use copy and paste special options. The steps to Transpose data using Copy and paste special is as follow:

Step 1: Select the range of cells that you want to transpose. Here A1: B7 is selected.

Excel transpose Formula - Example
Excel Transpose Function Example

Step 2: Right-click on the Mouse and Select Copy. and select the cell where you want to Paste the copied Data. Here D3 is selected. Right Click on the Mouse and Go to Paste Special > Select Transpose

Excel transpose Formula - Paste Special
Excel Transpose - Paste Special Transpose

Result: You will see that your data will be transposed. Vertical data will be converted into horizontal data in our case.

Excel transpose Formula
Excel Transpose Result

Transpose Data using Excel Transpose Formula

The Syntax of the Transpose function in Excel is:

This function requires only one argument. That is the array argument. In the array argument, you enter the range of cells that you want to transpose.

The steps to transpose data using the Transpose formula is as follow:

Step 1: Select the range of cells. The range selected should be equivalent to the range we are going to transpose. In our example, we have 2 columns and 7 rows. To Transpose data we will have to select 2 rows and 7 columns.

Excel transpose Formula - Formula Example
Excel Transpose Formula

Step 2: Type the Transpose Formula =TRANSPOSE( A1:B7) and than Press CTRL + SHIFT + ENTER.

Excel transpose Formula
Excel Transpose Result

How will you know whether the formula entered is an array formula or no? You can see in the formula bar that the formula is covered with a curly bracket. This indicates that this is an array formula.