To transpose in Google Sheets, copy your cells, click where you want them, then choose Edit, Paste special, Paste transposed. Rows become columns and columns become rows. If you want the flipped copy to update when the original changes, use the formula =TRANSPOSE(A1:E2) with your own range instead.
You've typed your months across the top and your services down the side, and now you realise the chart, the import or the client wants it the other way round. Retyping is not the answer. Transposing flips a block of cells so rows become columns, and Google Sheets gives you two ways to do it: a one-off paste, or a live formula.
Method 1: Paste transposed (a one-off flip)
- Select the cells you want to flip and copy them (Ctrl + C on Windows, Command + C on a Mac).
- Click an empty cell with enough room around it for the flipped data.
- Click Edit, then Paste special.
- Choose Paste transposed.
Google describes it neatly: if you copy a column of cells and paste transposed, they land in a row, and the other way round. The pasted copy is ordinary data, so you can edit it freely. It won't change if the original changes.
Method 2: The TRANSPOSE formula (a live flip)
- Click an empty cell where the top-left of the flipped data should go.
- Type =TRANSPOSE( and select the range you want to flip, for example A1:E2.
- Close the bracket so it reads =TRANSPOSE(A1:E2) and press Enter.
That example comes from Google's own tips: it turns rows 1 and 2 of columns A to E into two columns. The function's rule is simple. A range that's m rows by n columns comes out as n rows by m columns, and the value in the fourth row, second column moves to the second row, fourth column.
Which method to use
| Paste transposed | TRANSPOSE formula | |
|---|---|---|
| Updates when the source changes | No | Yes |
| Can you edit the flipped cells? | Yes, freely | No, edit the source instead |
| Brings formatting across | Yes | No, values come across plain |
| Best for | Cleaning up a layout once | Reports that should stay in sync |
The catch with Paste special
Google's help page has a limit that catches people out: paste special works only within a single spreadsheet. You can't copy cells from one spreadsheet and use paste special in a spreadsheet open in another tab. If that's what you're trying to do, paste normally into the second file first, then copy those cells again and use Paste transposed within that file.
The catch with the formula
TRANSPOSE fills a block of cells from one formula, so it needs empty space to spill into. If something is already sitting in the way, the formula shows an error instead of the data. Clear the area, or start the formula somewhere with more room.
You also can't type over a single cell inside the result. The values belong to the formula. If you need to tweak the flipped version, change the source cells, or turn the result into plain values: copy it, then use Edit, Paste special, Paste values only on top of itself.
Pick the destination with care
Before you paste, look at how big the flipped block will be. A table that's 3 rows deep and 10 columns wide becomes 10 rows deep and 3 columns wide, so it needs space going down, not across. I usually paste into a fresh tab: right-click the sheet tabs at the bottom, add a new sheet, and paste there. Nothing gets overwritten, and once you're happy with the result you can delete the old layout or keep both side by side.
If you do overwrite something by accident, press Ctrl + Z (Command + Z on a Mac) straight away to undo the paste.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Real jobs where transposing saves time
- Charts the wrong way round. If your chart shows months as separate lines when you wanted them along the bottom, flipping the table can fix it in one move.
- Imports. Tools that upload contacts or products often want one record per row. If your data runs across columns, paste it transposed first.
- Client reports. A wide table that runs off the page reads better as a tall one. Transpose, then tidy the headings.
- Survey or form results. Responses arrive one per row, but a summary often reads better with questions down the side.
Keep the formatting tidy
Paste transposed brings the formatting with it, so bold headers stay bold. The TRANSPOSE formula only brings values, so you'll need to format the result yourself. If the source has dates or percentages, apply the same number format to the flipped range so it doesn't show raw numbers.
My verdict
For a one-off layout fix, Paste transposed is quickest and leaves you with editable cells. For anything that feeds a report or dashboard, use TRANSPOSE so the flipped view stays in step with the original. Just leave room for it to spill. Once your table is the right way round, a pivot table in Google Sheets is the next tool for summarising it, and merging cells helps tidy the new headings.
Frequently asked questions
How do I transpose data in Google Sheets?
Copy the cells, click an empty cell, then choose Edit, Paste special, Paste transposed. Rows become columns and columns become rows.
What does the TRANSPOSE function do in Google Sheets?
It swaps the rows and columns of a range. A range of m rows by n columns comes out as n rows by m columns, and it updates when the source changes.
Why is Paste special not working between two spreadsheets?
Google says paste special only works within a single spreadsheet. Paste normally into the second file, then use paste special there.
Why does my TRANSPOSE formula show an error?
Usually there's data in the cells it needs to fill. Clear the space or move the formula somewhere with enough room.
Can I edit cells created by TRANSPOSE?
Not directly. Edit the source cells, or copy the result and use Paste values only to turn it into ordinary data.
