How to Quickly Transpose Data in Google Sheets

Prefer watching a video tutorial? View ours here:

While working with data, sometimes you may need to transpose data in Google Sheets.

But you don’t need to do this manually.

In this tutorial, I will show you two great ways to transpose data in Google Sheets:

  • Using Paste Special.
  • Using Transpose function.

Transpose Data Using Paste Special

Suppose you have a dataset as shown below:

Data set - transpose data in Google Sheets

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:

  • 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 Paste Transpose.Use the Paste Transpose Option

That’s it!

This will transpose the dataset.

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):

Data set that need to be transposed in Google Sheets

Here are the steps to transpose data using the TRANSPOSE function:

  • Select the cells where you want the data to be transposed.
  • Enter the following formula: =TRANSPOSE(A1:B11)
  • Hit Enter.

This would instantly transpose the data.

Transpose formula in Google Sheets

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.

FAQs on How to Quickly Transpose Data in Google Sheets

How to 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:


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:


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.

It’s syntax is as follows:


Here, 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.

How do I TRANSPOSE data from one sheet to another?

To transpose data from a different sheet, you can use the TRANSPOSE function, and specify the name of the sheet from where you want to fetch your data. So if you have your data to be transposed in the cell range A2:B5 in Sheet1, then you can fetch this data and transpose it as follows:



You May Also Like the Following Tutorials:

Spreadsheet Expert at Productivity Spot | + posts

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.

Popular Posts

2 thoughts on “How to Quickly Transpose Data in Google Sheets”

  1. 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?

Comments are closed.

You May Also Like