How to Transpose Data in Google Sheets
Got a spreadsheet in Google Sheets pointing in the wrong direction? Whether you want to flip a vertical table horizontally or vice versa, transpose is your best friend.
Sometimes you only realize you’ve used the wrong structure after you’ve finished creating a data table in Google Sheets and see the big picture. No need to break a sweat here, as you can swap the rows and columns simply by transposing. Read on to find out how to do this in Google Sheets!
What transposes data in Google Sheets?
Transposing data in Google Sheets means taking a vertical table and making it horizontal or vice versa. When you transpose a table of data in Google Sheets, the rows become columns and the columns become rows.
See the image at the beginning of this section for an example. We have the names of some volunteers and their pay in columns. So in each row is the name of a volunteer and their payment. Once we transpose this data we have the name and number in rows. So next to each name is another name and next to each payment another payment.
Transposing is a time-saving feature. If you want to swap the rows and columns without transposition, you’ll have to retype them, which can get tedious in larger spreadsheets or when creating trellis charts in Google Sheets.
How to transpose data in Google Sheets using copy and paste
Copy and paste are good old friends for anyone who has used a computer, and closer than friends for people who work with computers. Luckily, you can use this familiar tool in Google Sheets to transpose data.
As an example, let’s transpose the data in the table below. The goal is to have the names and payments in different rows.
- Select the entire data table. In this example it will be A1 to B7.
- Copy the table by right-clicking on it and selecting Copy. You can also press ctrl + C on your keyboard.
- Select a cell in which to display the data from the first cell. In our spreadsheet, this new position is where the data from the cell is A1 will be placed.
- Right-click the cell.
- Go to in the right-click menu insert special and select transposed.
Viola! Now you have your data table with columns and rows swapped. Notice that the styling from the previous data table has also been imported accordingly.
How to transpose data in Google Sheets with formulas
If you’re in the I only use formulas club, then we’ve got something for you too. In Google Sheets, you can also transpose data using the TRANSPOSE formula. This way you don’t have to waste clicks to select the data table. However, there is a catch. Because the output of this formula is an array in Google Sheets, you cannot change individual cells in the output.
Another difference between the TRANSPOSE formula and Paste special is that the formula ignores styling. So the fonts, colors, etc. from the input table are not moved to the output table. This can be both good and bad, depending on your perspective.
With that said, let’s move on to using TRANSPOSE Formula. The formula has the following syntax:
=TRANPOSE(input_table_address) In the cell where you enter the formula, the first cell will appear, just like the copy and paste method. Now let’s apply this formula to the same example.
- Select the cell where you want the transposed table to start.
- Enter the following formula:
=TRANPOSE(A1:B7)change A1:B7 in the formula to the address of the table you want to transpose.
- Press Enter.
There you have it! The table is now transposed. Note that the styling has not been imported. However, you can design the cells yourself.
rows or columns? doesn’t matter
It can be heartbreaking to look at a table of data that you spent hours creating, only to realize that it would have been better if you had swapped the rows and columns. At least that would have been the case if you didn’t know how to use transpose in Google Sheets.
With Transpose you can swap the rows and columns in seconds. It’s a piece of cake, and now you know how! So if you were wondering how to fix your spreadsheet without having to start from scratch, go ahead and transpose the data in a few clicks or a single formula!