EDUCBA

Home » Excel » Excel Resources » Lookup & Reference Functions in Excel » TRANSPOSE in Excel

Transpose in Excel

Excel TRANSPOSE Function (Table of Contents)

TRANSPOSE in Excel

Suppose you receive a spreadsheet with lots of data for working. Before getting started, you realize that you have to rearrange the data for the desired output. This means you have to display the results by changing the rows into columns or vice-versa. There are two ways of doing this:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

  • Copy and Paste
  • Transpose Function

Now we will see that when we have Copied and Paste feature of excel, what is the use of the Transpose function?

TRANSPOSE Formula in Excel

Below is the TRANSPOSE Formula in Excel

Transpose Formula

Copy and Paste Feature

It’s the simpler way of transposing the data. Let’s assume the below data, and we want to convert this data from columns into rows.

Transpose location

Select anywhere in the data and press CTRL+A for selecting whole data and press CTRL+C for copying that data. Then click on the cell where we want to see the result. Press Right-click, and you will see the PASTE OPTIONS segment. Under this option, click on the 4th option, “ Transpose”, as per the below screenshot:

Transpose (PASTE OPTIONS )

It will convert the data from columns into rows. Please check the below result like this:

Transpose ( convert the data)

When we have a simple set of data, this method is easy to transpose, but when working on a complex set of data like Tables or Function, this method is not preferred to use. Also, this feature has a drawback. If we make any changes in the source data, then it will not affect our result set. This means we need to make the changes accordingly in our result set too. The resulting data is not linked with the source data here.

Overcome to this problem, and the TRANSPOSE function comes into place.

How to Use the TRANSPOSE Function in Excel?

This TRANSPOSE function is very simple and easy to use. Let us now see how to use the TRANSPOSE Function with the help of some examples.

You can download this TRANSPOSE function Excel Template here – TRANSPOSE function Excel Template

The TRANSPOSE function is a built-in function. It takes a row and converts it into a column or a column and converts it into a row. In other words, we can say exchanging a row or a column.

Excel TRANSPOSE Function – Example #1

Let’s consider; we have an Electronic store sales data day wise:

Electronic store sales

Now we want to convert this data from rows into columns. First, we will copy and paste the Product name from column to row & Day name from rows to columns.

from rows to column

Now click on cell B28 and select the above blank area. Go to the formula bar and write the TRANSPOSE function. Select the data range from the source table for which we want to transpose as an argument and press F4.

=TRANSPOSE($B$20:$H$24)

transpose as an argument

Close the bracket and press CTRL+SHIFT+ENTER. It will display the data in the same format as you want.

bracket and press

Here, data is linked with the source table. Thus, If we make any changes in the source table, it will automatically reflect the result table.

Recommended Articles

This has been a guide to the TRANSPOSE Function. Here we discuss the TRANSPOSE Formula and how to use the TRANSPOSE Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. Excel FV Function
  2. EOMONTH Function Excel
  3. VBA Transpose
  4. WEEKDAY Function Excel
6 Shares
Share
Tweet
Share