Prefer watching a video tutorial? View ours here:
While working with data, sometimes you may realize that the structure you have is inconvenient. If you have your data in rows, you may have realized that it is more frustrating to navigate.
Google Sheets has a lot of features to help you structure your sheets such as rotating text. In this case, you may need to transpose data in Google Sheets. But you don’t need to do this manually.
In this tutorial, I will also show you two great ways to transpose data in Google Sheets:
- Using Paste Special.
- Using the Transpose function.
How to Transpose in Google Sheets- Quick Guide
Here is how to transpose data in Google Sheets:
- Select the data that you want to transpose.
- Copy the data (right-click and select Copy or use the keyboard shortcut Control + C)
- Select the cell where you want to get the transposed data.
- Right-click and within Paste Special, click on Transpose.
What Is Transposing Data in Google Sheets
Transposing is a way to switch columns and rows in Google Sheets. This will rotate your data so that if you have data in rows 1 and 2, the data in row 1, column A, and data in row 2 will move to column B.
This eliminates the problem of having to retype your spreadsheet again or copy-pasting rows one by one. You can have Google Sheets invert rows and columns either by using copy-paste or the TRANSPOSE function.
The function’s syntax is as follows:
=TRANSPOSE(array_or_range)
- array_or_range is just the array or range of cells that you want to transpose.
To learn how to use this function, you can skip to the section “Transpose Data Using TRANSPOSE Function” in this tutorial.
Let’s look at some examples of how to flip rows and columns in Google Sheets.
Transpose Data Using Paste Special
Suppose you have a dataset as shown below:
Transposing this data would mean that you would have the names in one row and the score below it in another row.
Here are the steps to transpose data in Google Sheets:
Step 1: Select the data that you want to transpose.
Step 2: Copy the data (right-click and select copy or use the keyboard shortcut Control + C)
Step 3: Select the cell where you want to get the transposed data.
Step 4: Right-click and within Paste Special, click on Transposed.
That’s it!
This will transpose the dataset. This is just one of the tips and tricks that you can use in Google Sheets to make your work easier.
Note that when you use the above steps, it only transposes the data, but does not carry the formatting with it. You will have to copy the formatting separately.
Also, the transposed data is static. This means that in case the data changes, then you will have to repeat these above steps again to get the new transposed data.
In case you want the transposed data to be dynamic, use the TRANSPOSE function as shown below.
Transpose Data Using TRANSPOSE Function
Again, let’s suppose we have the same data (as shown below):
Here are the steps to transpose data using the TRANSPOSE Google Sheets function:
Step 1: Click on the cell where you want the data to be transposed.
Step 2: Enter the following formula:
=TRANSPOSE(A1:B11)
Step 3: Hit Enter.
This would instantly transpose the data.
Google Sheets automatically identifies the number of rows/columns in the data set and the transposed data takes up the corresponding number of rows/columns in the transposed data. In case there is already some data in the cells that are to be used by the TRANSPOSE function, you will get a #REF! error.
Also, note that you can not delete a part of the array of the transposed data. If you want to delete the transposed data, you will have to delete the entire data set.
Transpose Using the Insert Menu
One nifty trick on Google Sheets is that you can access some functions from the Insert menu in the toolbar.
Here’s how:
- Click on the cell where you want your rows to start.
- Go to Insert > Function
- Go to Array and choose Transpose.
- Select the range you want to transpose
- Click Enter.
Your columns will be transposed to the rows starting from the cell with the formula.
How to Convert Rows to Columns from One Google Sheet to Another
Using the transpose function in Google Sheets, you can also swap rows to columns from one sheet to another.
Let’s look at how to do this with our example sheet
Step 1: Add a new sheet
Step 2: In the new sheet type in =TRANSPOSE
Step 3: Go to the previous sheet and select the range you want to transpose.
Step 4: Close the brackets and click enter.
The transpose formula Google Sheets that we used in this example is:
=TRANSPOSE(Sheet1!A1:K2)
You can see that it contains the sheet name to reference the data range in the first sheet.
This will have Google Sheets flip data from the first sheet to the second sheet.
Frequently Asked Questions
How Do You Transpose Data in Google Sheets Every n Rows?
If you have a list of items in a column, say column A and you want to transpose every n rows of it to n columns at a time, you can use the following formula:
=INDEX($A:$A,ROW(A1)*N-N+COLUMN(A1))
Simply replace the “N” in the above formula with the number of rows you want to transpose at a time. So if you want to transpose every 3 rows, replace “N” with 3, so your formula will be:
=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))
Copy this formula to the first cell of your transposed table and then drag it out to copy it to the rest of the cells.
What Does the TRANSPOSE Function Do in Google Sheets?
The transpose function simply transposes the rows and columns of an array or range of cells. In other words, it swaps the rows and columns from a vertical to horizontal orientation and vice-versa. Google Sheets invert rows and columns
Why is Transpose Not Working in Google Sheets?
If transpose isn’t working in your Google spreadsheet, here are a couple of reasons why that may be:
- You may not have permission to edit the file
- The cells may be locked.
- There may not be enough space for the cell you want to transpose since Google Sheets usually has a limit of cells.
Conclusion
In this Google Sheets transpose guide, we have shown two methods of transposing data. The first method is using paste special, and the second one is using the TRANSPOSE function. Hopefully, now you can swap your rows and columns comfortably in Google Sheets.
If you found this article useful, you can also check out how to split text to columns in Google Sheets. Alternatively, you may want to learn more about the entire G Suite instead of just Google Sheets. In this case, we’d recommend a comprehensive G Suite course.
Related:
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
2 thoughts on “Simple 2023 Guide to the Google Sheets Transpose Function”
I’m trying to learn how to transpose data from one sheet to another. And to transpose the dynamic cell. I’m having an issue trying to go between two sheets.
Real-time issue – I’m tracking Covid cases and deaths. I copy and paste daily data into a row.
I would like to take the data across many row – and FUTURE rows – into a sheet that list them in one column, and updates as I get future data. I am able to transpose with the “paste special” and “paste transposed” – but it only paste up to the data that I have today. The following day’s data doesn’t update.
Is that possible?
Share the solution with us if you have figured it out…Thanks..
Comments are closed.