Quickly Turn a Range on Its Side

September 14, 2021 - by Bill Jelen

Quickly Turn a Range on Its Side

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.

A list of departments in A1:A7 is selected and copied to the clipboard.
Figure 77. 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.

Use the Transpose icon in the Paste Drop-down.
Figure 78. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Liam Shaw on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.