4 Ways to TRANSPOSE in Excel – Shiksha Online

4 Ways to TRANSPOSE in Excel – Shiksha Online

4 mins read1.8K Views Comment
clickHere
Rashmi
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.

2022_06_Transpose-in-Excel-2.jpg

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.

TRANSPOSE Function in Excel

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
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
What is Conditional Formatting in Excel?
What is Conditional Formatting in Excel?
Conditional Formatting is a popular tool in Excel that allows adding a different format to the cells per their conditions. This way, we can easily see if the value of...read more
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel is widely used to generate large numbers of documents simultaneously while sticking to an agreed-upon or required format. The unique information for each card or label...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 –

TRANSPOSE Function in Excel

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

TRANSPOSE Function in Excel

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).

TRANSPOSE Function in Excel

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. 

Create a table adjacent to your existing array and copy your data.

Paste Special Transpose

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

Paste Special Transpose

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

Paste Special Transpose

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

Paste Special Transpose
Average Function in Excel – Formula and Examples
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
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more

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.

Transposition of Data with Direct References

Below raA2, write raB2.

Transposition of Data with Direct References

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

Transposition of Data with Direct References

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.”

Transposition of Data with Direct References

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

Transposition of Data with Direct References

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. 

2022_06_Transpose-Data-Power-Query-1.jpg

Power Query Editor will open. 

On the editor, go to –

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

2022_06_Transpose-Data-Using-Power-Query-2.jpg

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

Click Transpose to transpose the data.

2022_06_Transpose-Data-Using-Power-Query-3.jpg

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

2022_06_Transpose-Data-Using-Power-Query-4.jpg

Click File tab → Close & Load

2022_06_Transpose-Data-Using-Power-Query-5.jpg

The transposed data will now be loaded onto the spreadsheet.

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.


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in ExcelConclusion

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments