If you want to flip a range so that rows become columns and columns become rows, the TRANSPOSE function in Google Sheets does it in one step. You point it at a range and it returns the flipped version, which spills into the cells next to or below the formula.
In this article, I’ll show you how to use TRANSPOSE with a few practical examples, from flipping a single list to rotating a whole table.
TRANSPOSE Function Syntax in Google Sheets
Here is the syntax of the TRANSPOSE function.
=TRANSPOSE(array_or_range)
- arrayorrange – the range or array you want to flip. This can be a single row, a single column, or a full block of rows and columns.
TRANSPOSE takes just one argument, so there is not much to configure. It reads the shape of whatever you pass in and writes back the flipped shape.
When to Use TRANSPOSE Function
- You have data running down a column and you need it across a row instead (or the other way around).
- You pulled in a table that is oriented the wrong way and you want to rotate it without retyping everything.
- You want a live, formula-driven flip that updates automatically when the source data changes.
- You need to feed a row of values into a function that expects a column (or vice versa).
Example 1: Flip a Single Column Into a Row
Let’s start with the simplest case, turning a vertical list into a horizontal one.
Below is the dataset, a single column of fruit names in A2 to A4.
The goal is to lay those three names out across a row instead of down a column.
Here is the formula:
=TRANSPOSE(A2:A4)
TRANSPOSE reads the three values stacked in the column and writes them out left to right. The single formula in C2 spills the names Apple, Banana, and Cherry across C2, D2, and E2.
You only type the formula once. The values fill the row on their own.
Example 2: Flip a Single Row Into a Column
This one is the reverse of the first, taking a horizontal list and standing it upright.
Below is the dataset, three quarter labels sitting across the top row in A1 to C1.
The goal is to stack those labels down a single column.
Here is the formula:
=TRANSPOSE(A1:C1)
The formula in A3 takes the row of labels and writes them down the column, so Q1, Q2, and Q3 land in A3, A4, and A5.
It works exactly like Example 1, just rotated the other direction.
Pro Tip: Leave enough empty cells in the spill direction. If a cell that TRANSPOSE needs is already filled, you get a #REF! error instead of the result.
Example 3: Rotate a Two-Column Table
Now let’s flip a whole table, not just a single line.
Below is the dataset, a small table with weekday names in column A and a count for each day in column B, covering rows 2 to 5.
The goal is to rotate this two-column block so the days run across the top and their counts sit in the row beneath.
Here is the formula:
=TRANSPOSE(A2:B5)
TRANSPOSE flips the rows and columns together. The four-row, two-column block becomes a two-row, four-column block starting in D2.
The day names Mon, Tue, Wed, and Thu fill the first row, and their counts of 10, 15, 12, and 18 line up directly underneath.
Example 4: Sort First, Then Transpose
You can wrap TRANSPOSE around another function to clean up the data before flipping it.
Below is the dataset, the same weekday table from Example 3, with days in column A and counts in column B.
The goal is to sort the rows by their count from smallest to largest, then flip the sorted table sideways.
Here is the formula:
=TRANSPOSE(SORT(A2:B5, 2, TRUE))
How this formula works:
- The inner SORT sorts the table by the second column in ascending order, so the rows reorder by count.
- TRANSPOSE then flips that sorted result on its side.
- The days end up as Mon, Wed, Tue, Thu across the top, with their counts 10, 12, 15, and 18 underneath, already in order.
This pairing is handy when you want a horizontal summary that is also sorted. You can do both jobs in one formula.
Tips & Common Mistakes
- Clear the output area first. TRANSPOSE needs empty cells to spill into. If anything blocks the path, the whole formula returns a #REF! error.
- The result is live. Change a value in the source range and the transposed output updates on its own. There is no need to rerun anything.
- Don’t overwrite the spill. You can only edit the top-left cell that holds the formula. The rest are filled automatically, so typing over them breaks the result.
TRANSPOSE is one of those small functions that saves a lot of manual copy-paste. Once your data is flipped with a formula, it stays in sync with the source.
Try it on your own tables whenever the orientation is working against you.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: