Transpose Excel Rows and Columns [Quick Tip + Video]


Share on facebook
Share on twitter
Share on linkedin

Transpose Excel Rows and Columns - HowtoTransposing rows and columns in a table is one of the most useful tricks when you are pasting data. Yet, it always surprises me that very few people actually know this. So here it is,

To transpose a table of data from rows to columns (or columns to rows)

  1. Select the table, press CTRL+C
  2. Goto an empty cell
  3. Right click and select Paste Special (alternatively press ALT+E followed by S)
  4. Transpose Excel Rows and Columns - using Paste Special

  5. Select “Transpose” check box, it is above the OK button.
  6. Click Ok. Transposed your data is.

I have made a 15 second tutorial on this. See it below:

(Watch the tutorial on youtube, you can also see it in HD)

15 more tips on pasting data in excel | Transpose Charts | More Quick Tips | More Excel Video Tutorials

Share on facebook
Share on twitter
Share on linkedin

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Weighted average formula in excel

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

8 Responses to “Transpose Excel Rows and Columns [Quick Tip + Video]”

  1. dave says:

    Just pressing the underlined letter for each of the Paste Special options will select it. (Some of them will cancel out others, like add or subtract.)

    So you can just do:
    - Select
    - Ctrl-C
    - move
    - Alt-E, S, E, Enter

    And you're done.

    A super snappy way to copy a whole tab to values is along similar lines:

    - Ctrl-A
    - Ctrl-C
    - Alt-E, V, Enter

    And Boom! You've just copied everything on the current page as a value on top of itself. Useful if you just want to give someone the values, but not the underlying formulae.

  2. Jason says:

    Very nice! Your explanation was short and to the point. However, the video was a little too fast.

  3. Nilesh Karhade says:

    Hey, bt wht if u wnt trnasposing using formula?
    I mean somtims u hav couple of formulae attachd & u need transposing automatically, then how to do it?

  4. Steve LeLaurin says:

    Ever had a big row of formulas that you want to transpose into a column? But if you use the Copy Paste Special Transpose function, all of the formulas adjust in the process unless they are all locked into their cell references. I've found a way to do it, but it isn't very elegant. A more elegant solution would be welcome. Basically, my solution is to covert the formulas to "number" (highlight cells in row, Edit Replace, put equals sign = in "Find what" box, put # in "Replace with" box, then "Replace All"). Then, copy that cell range to the destination, selecting Transpose in the Paste Special box. Finally, replace the # sign with = just like at the beginning. This will convert the "numbers" back to formulas.

    Do you have a better method?

  5. Hui... says:

    Before you copy the cells select them
    Do a Search/Replace and replace = with '=
    Then do your copy, paste special, transpose
    Do a Search/Replace and replace '= with =

  6. [...] Long time Chandoo.org readers already know this. Excel has a built-in feature that lets you transpose data with a single click. [...]

  7. [...] it is a one time process, my friend can use Paste Special > Transpose feature and be done. But this is no one time business. So lets understand which formula helps us do [...]

Leave a Reply