Pinterest Pixel

How to Transpose in Excel – 3 Useful Methods

John Michaloudis
Excel has a plethora of features that are useful for data analysis and manipulation.
One such feature that is often used is TRANSPOSE in Excel.
How to Transpose in Excel - 3 Useful Methods | MyExcelOnline How to Transpose in Excel - 3 Useful Methods | MyExcelOnline

Transposing data refers to rearranging the orientation of your data i.e. switching the rows to columns and vice versa. This can be a handy tool for reorganizing information to better suit your analysis, reporting, or presentation needs.

Let us look at each of these methods.

Download the Excel Workbook below to follow along and understand How to Transpose in Excel –
download excel workbookTranspose-in-Excel.xlsx

 

Method 1 – Paste Special

You can easily transpose data in Excel using the “Paste Special” option. Below is the sales data where –

  • The rows represent different smartphones (Apple, Samsung, OnePlus, Google).
  • The columns represent different months (January, February, March).

Transpose in Excel

Now, you want to reverse this i.e. you want rows to represent months and columns to represent smartphones. Follow the steps below to learn how to transpose and convert rows to columns and columns to rows

STEP 1: Highlight the sales data table and go to Home > Copy.

Transpose in Excel

STEP 2: Move to the cell where you intend to place the transposed data.

Transpose in Excel

STEP 3: Go to Home > Paste > Paste Special.

Transpose in Excel

STEP 4: In the Paste Special dialog box, select Transpose and Click OK.

Transpose in Excel

The transposed data will now be pasted in cell G1.

Transpose in Excel

This method is quick and easy but is best suited for smaller datasets. It is useful when you do not want the transposed data to be linked to the original data.

Method 2 – TRANSPOSE Function

In the previous method, the transposed data remains static i.e. any change you make in the original data will not affect the transposed data. If you use the TRANSPOSE function you can link the original data with the transposed data.

Follow the steps below to learn how to use the TRANSPOSE function in Excel –

STEP 1: Go to the cell where you intend to place the transposed data.

Transpose in Excel

STEP 2: Enter the TRANSPOSE function.

Transpose in Excel

STEP 3: Enter the argument i.e. range. Here, it is A1:E11.

Transpose in Excel

Your data has been transposed correctly.

Transpose in Excel

This method is useful when you want the transposed data to update dynamically with changes in the original dataset. Suppose, you change the value for Xiaomi sales for January to 1500 in the original data (i.e. B5), the transposed data will also be changed (i.e. K2).

Transpose in Excel

 

Method 3 – Power Query

Power Query is a powerful and flexible way to handle large datasets and can be used to transpose data easily. Here’s a step-by-step guide on how to transpose data using Power Query –

STEP 1: Select the sales data and go to Data > From Table/Range.

Transpose in Excel

STEP 2: In the dialog box, select OK.

Transpose in Excel

In the Power Query Editor, you’ll see a table representing your data.

Transpose in Excel

STEP 3: Go to Transform > Use Headers as First Row.

Transpose in Excel

STEP 4: Go to Transform > Transpose.

Transpose in Excel

The rows will be converted to columns and columns will be converted to rows.

Transpose in Excel

STEP 5: Go to Transform > Use First Row as Headers.

Transpose in Excel

The first row will now become the header.

Transpose in Excel

STEP 6: Go to Home > Close & Load.

Transpose in Excel

The transposed data will be displayed in the new worksheet.

Transpose in Excel

 

In conclusion, Excel’s TRANSPOSE feature is a valuable tool for data analysts and professionals working on data manipulation.

This article has provided a comprehensive overview of three methods for transposing data in Excel – quick and straightforward Paste Special option, the dynamic TRANSPOSE function, and the powerful and flexible Power Query.

Learn more about Power Query with our 50 Things You Can Do With Excel Power Query.

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  How to Freeze Second Row in 3 Easy Steps in Excel Fast

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...