How to Quickly Transpose Data in Google Sheets

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.

You May Also Like the Following Tutorials:


10 Google Sheets Tips that will save you hours every week

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Bar Sparkline in Google Sheets - rtl
Google Sheets

Sparkline in Google Sheets – The Only Guide You Need

Sparklines are miniature charts that you can create within a cell. These are quite useful when you’re creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers (maximum/minimum) visually. According

Google Sheets

How to Insert Timestamp in Google Sheets

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work in the sheet simultaneously. In this tutorial, you’ll learn various to insert timestamp in Google

Google Sheets Keyboard Shortcuts - Spreadsheets
Google Sheets

101 Google Sheets Keyboard Shortcuts

Google Sheets is a relatively new spreadsheet tool in the market. It has quickly gained a lot of fan following and I see a lot of people migrating from other spreadsheets applications to Google Sheets.