Simple 2023 Guide to the Google Sheets Transpose Function

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:

1. Select the data that you want to transpose.
2. Copy the data (right-click and select Copy or use the keyboard shortcut Control + C)
3. Select the cell where you want to get the transposed data.
4. 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:

1. Click on the cell where you want your rows to start.
2. Go to Insert > Function

3. Go to Array and choose Transpose.

4. Select the range you want to transpose

5. 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:

• • VLOOKUP function in Google Sheets (Explained with Examples)
  • COUNTIF Function in Google Sheets
  • How to Count Cells If Not Blank in Google Sheets
  • Transposing Data in Excel
  • Pivot Table Google Sheets
  • How to Convert Text to Number in Google Sheets