In this article:

How to Transpose Data in Google Sheets (Best way!)

May 8, 2024

Transpose Data with Paste Special

Google has included advanced paste functions in Google Sheets that you can access through right-clicking on a cell in the target sheet. One of these functions include pasting data transposed to the target sheet. Here are the steps:

Step 1: Copy the data (you can right-click or use keyboard shortcut Ctrl+C)

Step 2: Right click on the sheet where you want to paste the data

Step 3: Select Paste special

Step 4: Select Transposed

You can check the example screencap below to see it in action:


Transpose with Google Sheets TRANSPOSE function

What if the sheet containing the target data in Google Sheets is regularly updated? Using the Paste special method will not let you automatically update the data; you have to manually copy the data from the source sheet and paste it through Paste special to the target sheet. 

If the selected data range is automatically updated, you can use the TRANSPOSE formula. 

If you use this formula, Google will automatically update the data whenever a change occurs in the source sheet. 

TRANSPOSE Function Syntax

The syntax of TRANSPOSE is straightforward:


Where range is the range of data that you want to transpose. 

Using the TRANSPOSE function

Using the function


Transpose Every n rows

The TRANSPOSE of Google Sheets does not have enough functionality to help you modify how the data is transposed. If you want to convert a long column to a set of rows with n cells each, you can use the following formula:


Where column_range specifies the column you want to convert, first_cell the first cell in the column, and N is the number of cells per row.  You should insert dollar signs to the column name in the column_range. You can see it in our example below, where we transpose column A every 3 rows:


The example is shown below:

The array is not automatically generated; you have to drag the cell containing formula to occupy the adjacent cells and display the data:


Transpose from another sheet

If you want to transpose the data from another sheet, you just need to add the sheet name to the range. For example, if you want to transpose A5:E7 from Sheet2, then the range will be


You can see this in action in the example below:


Google Sheets has two ways to transpose the data: via the Paste special and the formula TRANSPOSE. The Paste special is portable and straightforward to use, while TRANSPOSE formula allows you to automatically update the data without manually copying and pasting the new data. If the data sheet is regularly updated, you can use the TRANSPOSE formula to get the data transposed when loaded to the target cells. 

We hope this article has helped you and given you a better understanding of how to transpose data in Google Sheets. You might also like our articles on how to use the Google Sheets ISBLANK function and how to concatenate in Google Sheets.

To optimize your workflow, we recommend reading our guide on how to copy our free contact list template for Google Sheets and trying our software to help you track renewals.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->