Below, I’ll show you two ways to quickly transpose data in Google Sheets. What does that mean? Transposing the data means that you change the orientation where the rows become columns and the columns become the rows. I’ll show practical examples of this below.
In Google Sheets, there are two easy ways to transpose data (i.e., switch rows and columns):
- Using the Google Sheets TRANSPOSE function
- Using Paste Special
The method you choose to swap rows and columns will depend on whether you want the data to be static or dynamic (covered in detail in the sections later). This makes it much easier to move your data around.
Ready to transpose data? Let’s get started.
Table of Contents
How to Transpose in Google Sheets
I made a video that shows every step of the transposition process with one of my own spreadsheets.
[adthrive-in-post-video-player video-id=”cGUD5dKc” upload-date=”2021-08-02T15:12:17.000Z” name=”How to Transpose Data to Google Sheets” description=”My latest video shows how to transpose data to Google Sheets. I give practical examples from my own spreadsheet and share my tips to help avoid common issues.” player-type=”default” override-embed=”default”]
Basically, there’s a quick five-step process when you want to have Google Sheets transpose row to column:
- Select the entire dataset that you want to transpose (A1:B11 in our example)
- Copy the data (you can use the keyboard shortcutย Control + C or right-click and click on copy). For Mac we can use the keyboard shortcut Command + C
- Right-click on the cell in which you want to transpose the data
- Hover the cursor over the Paste special option.
- Click on Paste transposed.
I’ll break these down with screenshots, too.
What Is Transposing Data in Google Sheets?
Transposing data in Google Sheets is a way or rearranging the data. This is by switching the rows to columns and the columns to rows. This can come ini handy when you donโt have enough space for a vertical table.
By transposing, you are able to convert your data thatโs in a vertical table into aย horizontal table. For example, our table below is vertical:
If we transpose it, the rows and columns will switch as shown in our transposed table below:
All the data remains the same, and the only thing that changes when transposing is the orientation.
TRANSPOSE Function Google Sheets Syntax
The TRANSPOSE Google Sheets function only uses one argument and goes as follows:
TRANSPOSE(array_or_range)
- array_or_rangeย is simply the selection of cells you want to transpose.
Using the TRANSPOSE Function to Switch Columns and Rows in Google Sheets
One of the reasons I love Google Sheets because it has some useful functions that make life easy when working with data. And TRANSPOSE function is one of these.
Suppose you have the dataset as shown below:
If you want to transpose the above dataset, you can use the formula below:
=TRANSPOSE(A1:B11)
- Select the cell you want to transpose to.
- Type the formula =TRANSPOSE
- After the opening brackets, select the range you want to transpose.
- Click Enter.
I have entered the above formula in cell D1, so it transposes the data from D1:N2.
Note that you don’t need to worry about the exact range where the data would be transposed and placed, All you need to do is enter the formula in the cell where you want the transpose data to begin (D1 in our example) and Google Sheet will automatically transpose the entire data and give you the result.
The only thing you need to ensure is that there are empty cells to accommodate the transposed data. If there is any cell that has some text/number or even a space character, TRANSPOSE formula wouldn’t work and give you a reference error.
Also, when you hover the cursor over the error, it will tell you exactly which cell has data already so you can correct that.
When you use the TRANSPOSE function, it only gives you the values and not the formatting.
The result that you get from using the TRANSPOSE function is dynamic. This means that if you change anything in the original dataset, that change would automatically be reflected in the resulting transposed data.
Also, this is an array formula, so you can not delete any part of the resulting data. If you want to delete it, you need to delete the entire dataset.
In case you overwrite any of the cells, the entire resulting dataset would vanish and you will see the reference error in the top-left cell. This would automatically revert as soon as you clear the cell.
Using Paste Special to Transpose Google Sheets Data
Related Reading: How to Convert PDF to Google Sheets
Here is another way to learn how to transpose in Google Sheets.
Suppose you have the dataset below that you want to transpose.
Below are the steps to do this:
- Select the entire dataset that you want to transpose (A1:B11 in our example)
- Copy the data (you can use the keyboard shortcutย Control + C or right-click and click on copy) For Mac we can use the keyboard shortcut Command + C
- Right-click on the cell in which you want to transpose the data
- Hover the cursor over the Paste special option.
- Click on transposed
This will paste the transposed data starting with the cell which you selected while pasting.
Note that this technique copies and paste everything – including values, formulas, formatting, etc. If you only want to copy the values and not the formula or formatting, you need to first copy and paste the transposed data, then copy it again and paste as values only.
Since we are copying and pasting the data, it’s not dynamic (unlike the TRANSPOSE formula). This means that if you make any changes in the original dataset, the changes wouldn’t be reflected in the transposed data.
How to Convert Multiple Rows to Columns in Google Sheets
To convert rows to columns in Google Sheets, you can use the paste special method:
- Select ย the rows
- Right-click and choose Cut.
- Right click in the first cell of the column you want to make.
- Choose Paste Special.
- Select Transposed.
This method will convert your multiple rows to columns.
How to Transpose Data in Google Sheets FAQ
Does Google Sheets Have a Transpose Function?
Yes, you can use the TRANSPOSE function or Paste specialย from the paste menu to flip data in Google Sheets.
Where Is Google Sheets Transpose?
If you ever have trouble finding a function in Google Sheets, you can navigate to Insert > Functionย and search for the function.
How Do I Transpose Columns and Rows in Google Sheets?
Heres how to flip rows and columns in Google Sheets with the TRANSPOSE function:
- Type =TRANSPOSE( into the top left most cell you wish to transpose the data to
- Highlight the cells to transpose
- Hit Enter
How Do I Transpose Data From One Sheet to Another?
You just have to use a Sheet reference as the range argument in the TRANSPOSE function. For example, if you wanted to use the data range B1:D9 in Sheet 1, you could use the following formula.
=TRANSPOSE(‘Sheet 1’!B1:D9)
Wrapping up the Google Sheets Invert Rows and Columns Guide
So these are the two Google Sheets can swap rows and columns. It is a simple function and works across sheets using Sheet references as the range.
If you found this Google Sheets transpose guide useful, You can check out our guide to split cells in Google Sheets.
you may also like the following tutorials:
