Excel: Quickly Turn a Range on Its Side

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem; I have a column that contains 20 department names going down a column. I need to build a worksheet with those names going across row 1.

  1. Turn this data sideways.

Strategy: Copy the data. Select a new blank cell. Do a Paste Transpose. Here's how:

  1. Highlight the department names in column A.
  2. Ctrl+C to copy the cells to the Clipboard.
  3. Move the cell pointer to a blank area of the worksheet. Your paste region can not start in the same cell as your copy region.
  4. Open the Paste dropdown. Click the Transpose icon.

  1. Use this icon to turn the pasted data sideways.

    Gotcha: The columns you paste to will not automatically resize to fit the data. To fix this problem, you can select the appropriate range (in this case, C1:Z1) and then choose Home, Format, AutoFit Column Width.

    Additional Details: You can use Transpose to convert a horizontal row of numbers into a column. In addition, you can use it to turn a rectangular range on its side.