Google Sheets - Flipping Your Rows & Columns

Let's say you have put together a sheet with information you THINK is how you want it.  And after using it for awhile, you realize you REALLY want the columns and rows switched.  Can you easily remedy this without re-entering all the data?  The answer is yes! 

Before walking through how to easily do this, let me introduce you to a Google Sheets formula you may not know - it's called TRANSPOSE and it's purpose is to transpose the rows and columns in an array or range.  So, here is some data I have in cells A1-E2: 

If I want to flip the columns and rows, I add the formula:
=TRANSPOSE(A1:E2)

in the cell I want to start the transposed information into.  And...viola!  My data is flipped:
It needs to be noted that you CANNOT change data in the transposed cells.  As soon as you try to change the data in the transposed area you will get a REF! error because you have data in a cell it need to do the transposing.  The data is dynamic - if you change your original data set it will change it in the transposed data as well.

Now that you have been introduced to the formula and know about it's limitation, how can we swap it in a way it is editable?  It actually will only take you 4 steps!  Here's another spreadsheet of data I have:
To transpose it in an editable format,
  1. highlight the data (or the entire sheet by clicking on the box next to Column A)
  2. copy (either go to Edit -> Copy, right-click and select Copy, or use Control +C on your keyboard)
  3. Go to a new sheet
  4. Paste Special
Paste, you ask?  Yes, if you check out the Paste Special options, one of them is Paste Transpose:
By choosing this, it will transpose your data as you paste it into the new sheet.  In my case I end up with:
Note that I am in A1 and the formula bar shows nothing (no =TRANSPOSE formula) as when it transposed it, but also copied over the values so it isn't pulling the data from the other sheet. This means the data is editable in the new sheet - I can even delete the sheet with it in the other order - but it also means it is not dynamic.  If I change it in my original data set it will NOT change it on my new sheet.




Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Sign-in - New & Updated

2017 MACUL Conference - Adding Pre-Conference Sessions to your Registration

Introduction to the NEW Template Gallery in Google Docs, Spreadsheets & Slides