Quickly Switch the Columns and Rows of a Data Set in Excel

Add to Favorites
Author:

How to switch a data set in Excel so that the columns become rows and the rows become columns.

This will save you hours when you need to "flip" a set of data in Excel.

Below, I'll show you the steps involved; don't forget to download the accompanying workbook so you can follow along with this tutorial.

Sections:

Desired Result

Steps to Switch the Rows/Columns of a Data Set in Excel

Quickly Transpose Data with Keyboard Shortcuts

Notes

Desired Result:

Go From:

To this:

Steps to Switch the Rows/Columns of a Data Set in Excel

  1. Select the data set that you want to switch or reverse:
  2. Hit Ctrl + C to copy the data. (It will not work if you cut the data instead of copy it)
  3. Go to where you want the new data to be located and select the upper left most cell where that data should be placed:
  4. Right-click over the desired cell and click Paste Special:
  5. In the window that opens, select the Transpose option, which is located above the OK button; then, hit the OK button.
  6. There you go!

The original data will still exist, so, if you want to remove it, go ahead and do that.  The new data is in no way linked to the original set of data.

Quickly Transpose Data with Keyboard Shortcuts

If you want to perform the above steps to transpose or switch your data using keyboard shortcuts, that can be done in just a few steps.

  1. Select the original Data and hit Ctrl + C
  2. Go to where you want the data to be and select the upper left most cell where the data will go and use this keyboard shortcut: Alt + E + S + E and hit Enter.

This will take a few times to learn before you can do it from memory but it will save you a lot of time.

Notes

You can't paste the new data over the old data like you can with copy-paste-special.

The new data is in no way linked to the old data; this means that if you change the original data, the new data set will not update.

If you do this with cells that contain formulas, make sure to double-check the formulas after transposing the data to ensure everything copied correctly.  This is usually only a problem if you are using relative cell references and did not transpose all of the data referenced in the formulas.

Don't forget to download the accompanying workbook so you can follow this tutorial and see the before and after within Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
Macro: This free Excel macro filters a data set to display the bottom 10 percent of the data set ...
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the data ...
Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel - UDF
Macro: This free Excel UDF (user defined function) allows you display the filter criteria tha...
Display Filter Arrows in a Table or Data Set in Excel - AutoFilter
Macro: This free Excel macro allows you to apply filter settings to a data set, list, or range of...
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
Macro: This free Excel macro filters data to display the bottom X percent of the data set in Exce...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course