4 Ways to TRANSPOSE in Excel – Shiksha Online

# 4 Ways to TRANSPOSE in Excel – Shiksha Online

clickHere
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:46 IST

Learn how to transpose data in Excel and quickly change the orientation of columns and rows in a spreadsheet.

TRANSPOSE function is categorized under Excel Lookup and Reference functions. It flips the direction of a given array or data range. TRANSPOSE in Excel changes or rotates data from the horizontal array to the vertical array. You can transpose data using different ways, including the TRANSPOSE function, Paste Special method, and direct references method. This article will show you how to TRANSPOSE data using the above-mentioned methods.

Content

## 1. Using TRANSPOSE Function

You can change the orientation of an array using the TRANSPOSE function from vertical to horizontal or vice versa. An array formula requires you to enter the function in a range with the same number of rows and columns as the source.

The syntax of the transpose function is:

#### =Transpose(array)

The following sample spreadsheet will transpose the array with employee names, departments, countries of origin, and payouts.

First, find out how many rows and columns are in your original array. After that, select the exact number of cells as the original range, but in the other direction.

For example, we have five rows and three columns, so we must select three rows and five columns of cells for the transposed data.

Most Popular and Powerful Formulas in Excel
The article discusses the most popular formulas in excel through practical examples of the use of formulas for Excel.
What is Conditional Formatting in Excel?
Learn what is Conditional Formatting in Excel and explore different presets of Conditional Formatting to analyze and present your data in the most effective and powerful way.
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel is widely used to simultaneously generate large amounts of documents while sticking to an agreed-upon or required format. Read the blog to learn more about mail...read more

In our case, we have five rows and four columns, so you need to select four rows and five columns.

Select a blank range of cells and enter the formula:

=TRANSPOSE(A3:D12)

Do not press the ‘Enter’ key. At this stage, your spreadsheet will look like this –

Now, press ‘Ctrl + Shift + Enter’, this will transpose the data. Remember, it’s a mandatory step.

An array formula must permanently be terminated by pressing CTRL + SHIFT + ENTER. This combination places a set of brackets around the formula in the formula bar. This ensures that the result is treated as an array of data and not a single cell value.

Note – While using the TRANSPOSE formula, count and select the given number of columns/rows only. You won’t be able to change that further (check below).

Excel’s TRANSPOSE function only copies data, not the formatting of the original data. Excel does not copy the original header formatting. It is a dynamic function. When its value changes in the original data, it reflects in the transposed data.

Explore MS Excel Courses

## 2. Paste Special Transpose

You can use the Paste Special option in Excel to paste the transposed data.

Select the new table array and go to Paste ⇒ Paste Special.

A window with different options to format your content will pop up before pasting it. Locate Transpose and check that option.

Your table data has now been transposed on the adjacent table array.

Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
Top MS Excel Interview Questions
Master your Excel interview with confidence using our curated list of top questions. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.
Introduction to MS Excel – A Beginner’s Guide
Let’s gets started with MS Excel, the most useful tool to crunch numbers and derive information for data analysts and scientists.

## 3.Transposition of Data with Direct References

In our third Excel data transposition method, we will use direct references. This method allows us to find and replace a reference with the data we want to display.

Click on an empty cell, type a reference, and then the location of the first cell we want to transpose. In this circumstance, I will use raA2.

Below raA2, write raB2.

Highlight both cells and drag the highlighted area out by clicking and dragging the green box at the bottom right of the selection.

Press Ctrl + H on your keyboard to open the “Find and Replace” menu. Go to Replace option and in the “Find what” field, type the selected prefix, “ra” (without quotes), and then “=” (without quotes) in the “Replace with” field. Now click “Replace all.”

You will see that the number of selected cells has the required information.

## 4. Transpose Data Using Power Query

Select the range of data that you want to transpose.

Go to the Data tab → Click on From Table.

Power Query Editor will open.

On the editor, go to –

Transform → Table → Use First Row as Headers → Use Headers as First Row.

By selecting this option, all the headers move to the first row.

Click Transpose to transpose the data.

Select – ‘Use First Row as Headers.’ to keep the first row of transposed data as a header.

Click File tab → Close & Load

## Points to remember

• TRANSPOSE in Excel changes columns to rows and vice versa.
• Enter the TRANSPOSE function as an array formula with the same number of cells as the array
• Press Ctrl + Shift + Enter to apply the formula, not just Ctrl.
• The new array must occupy the exact count of rows as the source array has columns and vice versa.

## Conclusion

To transpose data manually is a time-consuming task. Using Transpose in Excel will help you in data analysis and management. We hope this article helped you understand Transpose in Excel and how to transpose data using different methods.

clickHere