How to Transpose Columns & Rows in Excel

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Transposing columns and rows in Excel is a simple but very useful tool. Learn how to flip flop data in Excel and transpose data using both the right-click and ribbon commands. Updated: 10/26/2021

Flip Flop Data in Excel

I think the feature should be called 'Flip & Flop.' It would make more sense. But the command is actually called 'Transpose.' It's probably in the top five easiest tools to use in Excel. Transpose simply means to switch your columns to rows and your rows to columns.

In this lesson, we will review an example of using the Transpose feature, and look at what the tool does and learn how to switch your columns and rows of data.

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Creating & Modifying Named Ranges in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Flip Flop Data in Excel
  • 0:30 Transpose Using the…
  • 2:42 Transpose Using the…
  • 3:40 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Transpose Using the Right-Click

The Transpose tool in Excel helps to eliminate the need to re-key data. Imagine you just spent the last couple of hours working on a worksheet that displays financial information. The worksheet shows your company's quarterly revenue results for the last year. You happen to be the owner of a small, but successful, sporting goods store. You have five departments: apparel, fishing, hunting, camping and sports.

In your worksheet, you set up the departments across the top as the column titles and each quarter is displayed by row (quarter 1, quarter 2 and so on). You decide that the information would work better if the column and the rows were switched. In other words, you want the columns to be the rows and the rows to be the columns. No problem! Ready, set, switch!

Let me start with a note. The Transpose feature uses the copy/paste commands. Copy areas and paste areas cannot overlap. You can transpose the data to the same worksheet or another worksheet in the workbook, but the paste area must fall outside the area from which you copied the data. So here are the steps:

  1. Highlight or select the worksheet data you want to switch (you can select any cell of data and press Control + A on your keyboard).
  2. Right-click on the selected area and choose Copy from the drop-down menu.
  3. Click your mouse in the first cell of the area you want to paste your rearranged data (either on the same worksheet or a new worksheet in the workbook).
  4. Right-click and choose Paste Special from the drop-down menu.
  5. Add a check to the box next to Transpose.
  6. Click OK.

Check it out!

Worksheet after rows and columns have been transposed
spreadsheet after rows and columns transposed

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it now
Create an account to start this course today
Used by over 30 million students worldwide
Create an account