• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Transpose Data in Excel

You are here: Home / Excel Formulas / Transpose Data in Excel
Transpose data in Excel
January 27, 2016 by Mynda Treacy

There are 3 ways you can transpose data in Excel (not including VBA).

Download the workbook and follow along

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

1. Copy > Paste Special > Transpose

Transpose data in Excel with Paste Special Transpose

The downside of Paste Special > Transpose

Itโ€™s really only good for one-time use since itโ€™s not linked to the original data. That means if the original data is updated youโ€™ll have to do the whole Copy > Paste Special > Transpose all over again, and manual repetition is Excel blasphemy.

2. Use a formula like the built in TRANSPOSE Function

TRANSPOSE is a multi-cell array function and as such you enter it with CTRL+SHIFT+ENTER like any other array formula.

The TRANSPOSE function only has one argument:

=TRANSPOSE(array)

Where โ€˜arrayโ€™ is the range of cells you want to transpose.

First select the cells you want to place your transposed data in.

Tip: The trick here is knowing how big the range needs to be. I like to cheat a little and use the Copy > Paste Special > Transpose technique in step 1, and then with the pasted cells selected simply overwrite them with my TRANSPOSE formula.

Excel Transpose function

Donโ€™t forget to press CTRL+SHIFT+ENTER to complete the formula.

The downside of the TRANSPOSE function:

If the size of the source data expands or contracts you have to manually update the formula. Yawn.

3. Use Power Query to Transpose Data

Power Query is available in Excel 2010 onwards so this technique isnโ€™t for everyone (sorry 2007 users).

  1. Select your data > go to the Power Query tab (Excel 2016 see Get & Transform on the Data tab)
  2. Click the From Table icon
  3. Check Excel has correctly detected the range of your data (change if required) and uncheck the โ€œMy table has headersโ€ box
  4. Click OK.

transpose data with Excel Power Query

Note: If your data is already in an Excel Table you wonโ€™t see the above From Table dialog box.

Once you click OK the Power Query editor window will open and youโ€™ll be able to see a preview of your data:

Power Query editor

On the Transform tab in the Table group choose โ€˜Transposeโ€™:

Power Query transform tab

Then choose โ€˜Use First Row as Headersโ€™:

Power Query promote first row to headers

Now youโ€™re ready to load your data back into Excel. On the Home tab > click โ€˜Close & Load Toโ€™:

Power Query close and load

Choose where you want to put your data. Iโ€™ve put mine in the Excel worksheet beside my original data so you can see them together:

Power Query output

The downside of using Power Query

What? Thereโ€™s no downside ๐Ÿ˜‰

Ok, some might say itโ€™s more complicated but it took me 25.62 seconds to complete the steps above (I had my son time me with the stop watch Santa bought him for Christmas so it must be accurate). And the best thing about using Power Query for transposing data is:

  1. If your table expands or contracts a simple refresh of the query will update the transposed data to incorporate any changes.
  2. I can get data from almost anywhere and transpose it, as well as loads of other data cleansing tasks I might want to do. For example, merge or split columns, unpivot, filter out duplicates and more. MUCH more.

Click here for more information on what Power Query can do, what versions of Excel itโ€™s compatible with and information about our Power Query training.

Transpose data in Excel

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you donโ€™t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy theyโ€™re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.

More Power Query Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, โ€˜if orโ€™ and โ€˜if andโ€™, which are easier to write than their Excel counterparts.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you donโ€™t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy theyโ€™re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.


Category: Excel Formulas, Power Query
Previous Post:Multi-cell array formulasExcel Multi-cell Array Formulas
Next Post:Power Query Version Compatibility and InstallationExcel Power Query

Reader Interactions

Comments

  1. Sandeep Kothari

    June 2, 2018 at 8:08 pm

    You should also mention the VBA option!

    Reply
    • Mynda Treacy

      June 3, 2018 at 8:33 am

      Hi Sandeep,

      Everything that’s built into Excel has a VBA option, but I don’t think we should use VBA unless it’s the last resort, only option.

      Mynda

      Reply
  2. Sally

    December 24, 2017 at 11:18 pm

    Hi Mynda,

    2. Use a formula like the built-in TRANSPOSE Function

    How do I add transpose in one cell at the same time highlighting the whole table?

    Thank you

    Reply
    • Mynda Treacy

      December 26, 2017 at 11:43 am

      Hi Sally,

      You can’t transpose a range of cells into a single cell. Perhaps you can post your question with a sample Excel file on our forum so we can see what you mean in context and help you from there.

      Mynda

      Reply
  3. Jerald

    March 8, 2016 at 12:26 pm

    Aloha again.
    Using Power Query this time, copied the table lower in the worksheet and added a new row to my data table (tblRegMon) and added region numbers. When I double clicked on the query, the Power Query window showed the expanded table. I was expecting the transposed data in the original worksheet to be updated (TRANSPOSE Function), but it wasn’t. And in the Power Query window, although the table was expanded with the added month, the “Close & Load” showed “Close and Load To” option grayed out, and the “Close and Load” option did not result in the transposed data set’s being expanded and loaded to the original worksheet.

    in short, “If your table expands or contracts a simple refresh of the query will update the transposed data to incorporate any changes.” — how does the refresh update the transposed data in the original (i.e., not Power Query window) worksheet?

    Mahalo (thanks)

    Reply
    • Mynda Treacy

      March 8, 2016 at 12:51 pm

      Hi Jerald,

      The sheet called ‘TRANSPOSE Function’ is not related/connected to the sheet called ‘Power Query Transpose’. The first one is for the formula example, and the second is for the Power Query example. If you copied the table (not sure which table you mean), then it may not be connected to the query at all.

      Also note; Power Query doesn’t overwrite the original data/table. It creates its own table. Otherwise it would be in a never ending loop.

      Close and Load ‘To’ is only available the first time you close and load the query. After that the ‘to’ option is only accessible by right-clicking the query in the ‘Workbook Queries’ pane > Load To.

      Hope that makes sense, but if not please send me your file so I can see which table you copied and how it’s connected to Power Query.

      Kind regards,

      Mynda

      Reply
  4. J Plett

    March 8, 2016 at 11:55 am

    Aloha. With the transpose array formula, you cannot turn the original data into a table (tblRegion) and then in the array formula use: transpose(tblRegion) ? When I do this, all I get transposed is “Jan” . . . I was hoping using a table as the data source would be more dynamic, but it seems not to be so.

    Reply
    • Mynda Treacy

      March 8, 2016 at 12:44 pm

      Hi Jerald,

      Good idea, but the Table structured references either refer to the headers, column(s) or row(s). There isn’t a structured reference for the entire range of the table (including the headers), which is why it won’t work with the TRANSPOSE function.

      Mynda

      Reply
  5. Stephan

    February 21, 2016 at 3:28 am

    Transpose Formula article great time saver, as Excel 2003 doesn’t include Transpose paste option.
    Useful for rearranging DATA from Vertical to Horizontal for the purposes of Axis for creating CHARTS.
    Excel doesn’t recognise Vertical Data for Plotting CHARTs, unless that’s just Excel 2003?
    All your articles are clear & concise in purpose & formula.

    Reply
    • Mynda Treacy

      February 21, 2016 at 8:31 am

      Thanks, Stephan. Shame you still have Excel 2003.

      Reply
  6. maggie pont

    February 5, 2016 at 10:20 am

    Just wonderful as always.

    Hoping to do the PowerQuery course.

    Kind regards
    Marg Pont

    Reply
    • Mynda Treacy

      February 5, 2016 at 10:22 am

      Thanks, Marg.

      I’m sure you will love Power Query.

      Mynda

      Reply
  7. Kevin McLogan

    February 5, 2016 at 2:26 am

    I do a lot of transposing of data that may or may not be of the same number of cells, so I wrote a short VBA that transposes whatever data I copy and pastes only the values:

    Sub TransVal()
    ‘ TransVal Macro
    ‘ Paste Transposed Values
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    End Sub

    I put up an icon on the Quick Access Toolbar, or if I am using it often in a file, place a button above the frozen pane.
    Enjoy!

    Reply
    • Mynda Treacy

      February 5, 2016 at 11:14 am

      Thanks for sharing, Kevin ๐Ÿ™‚

      Reply
  8. Inna

    January 28, 2016 at 12:39 am

    It’s not a comment but a question – is there a way to subtract a corresponding row or column from range of cells using Transpose formula?
    E.g., Cells A1:E1 have values ins them. In cells A2:E2 I need ={Transpose(F1:F5)} minus a corresponding values in row 1. When I input =Transpose(F1:F5) – A1, the formula subtracts A1 from each cell, while I need it to be A1 from A2, B1 from B2, etc.
    Appreciate your help

    Reply
    • Mynda Treacy

      January 28, 2016 at 8:58 am

      Hi Inna,

      Yes, but your formula would be:

      =TRANSPOSE(F1:F5)-(A1:E1)

      Entered with CTRL+SHIFT+ENTER as it’s an array formula.

      Kind regards,

      Mynda

      Reply
  9. jim

    January 27, 2016 at 10:43 pm

    For us poor relations still using 2007, there is another way, somewhere between 2 and 3:

    Convert original data to a table and create a pivot table based upon it
    It may not look as pretty without additional formatting and need refreshing to incorporate new data (but set refresh on open and you’re almost there)
    New rows of data will be added on refresh but new columns will need dragging in

    This was done in less than 10 seconds (I timed myself!)
    But, of course, the reformatting would take several hours until you get it just right ๐Ÿ˜‰

    Reply
    • Mynda Treacy

      January 28, 2016 at 8:52 am

      Hi Jim,

      Of course, you can use Excel Tables as your PivotTable source and as new data is added to the Table a simple refresh will pick up the new data and incorporate it into the PivotTable.

      Just note that the data has to be in a tabular format for the PivotTable to work with it.

      Kind regards,

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.