How To Use The Transpose Google Sheets Formula: 3 Easy Methods in 2024

Prefer watching a video tutorial? View our “How to transpose Google Sheets “video here:

If you’ve been working hard on a spreadsheet, what if you don’t like the structure of your data? You can flip your rows into columns using the TRANSPOSE Google Sheets formula.

With step-by-step tutorials and screenshots, I’ll show you how to transpose your data in Google Sheets using 3 easy methods. Let’s get started!

Quick Guide to Using the Transpose Google Sheets Formula

You can easily flip rows and columns with the Paste Special feature in Google Sheets. Here’s how:

1. Select the data (rows and columns) you want to transpose.
2. Copy the data.
3. Click on the cell you want to get the transposed data.
4. Right-click and hover on Paste Special. In the resulting menu, click Transposed.

What Is Transpose in Google Sheets?

In Google Sheets, transposing is simply swapping your columns to rows and your rows to columns. Essentially, you’re flipping your table from vertical to horizontal (or vice-versa). In the example below, the table converts from vertical to horizontal.

Why Would I Use Transposing in Google Sheets?

Transposing eliminates the need to manually re-type everything in your Google Sheet. You also avoid the trouble of copy-pasting rows and columns one by one.

1. How to Use the TRANSPOSE Google Sheets Formula

Before discussing using the TRANSPOSE function, let’s first look at the function’s syntax.

​​The function’s syntax is as follows:

=TRANSPOSE(array_or_range)

• array_or_range is just the array or range of cells you want to transpose.

You can use the TRANSPOSE Google Sheets function to transpose multiple rows and columns.

Say that you want to flip the rows and columns. Here’s how to do it:

1. Click on the cell where you want the transposed data. I’ve placed the example below in cell D1.

2. Enter the formula: =TRANSPOSE(A1:B6).
   • I started at A1 because that’s the first cell in the range, and I ended at B6 (the last cell in the range).

3. Hit “Enter” for the data to instantly transpose.

As you can see, Google Sheets automatically identifies the number of rows and columns in the dataset. When transposed, the data will take up the same number of rows and columns but in reverse.

Avoiding Errors When Using the TRANSPOSE Google Sheets Formula

To avoid issues when performing the transpose Google Sheets function, ensure the cells you’re transposing to are empty! Otherwise, you’ll get an #REF! Error. In the screenshot below, some of the cells have “Test” texts.

When you hit “Enter,” you will see an error:

Things to Know About the TRANSPOSE Google Sheets Formula Method

The transposed data is dynamic. Therefore, if you change anything in the original data set, the transposed dataset will reflect the changes. Thus, you can easily make any edits without a problem.

However, you cannot delete any part of the transposed data unless you delete the full transposed dataset.

Want to be a master in using all Google Sheets features and formulas? Check out this online tutorial to become an expert in spreadsheet automation.

2. How to Transpose Data in Google Sheets Using Paste Special

You can also use the Paste Special function to transpose data without the formula. Here’s how to paste vertical data horizontally in Google Sheets (and vice versa).

1. Highlight the data you want to transpose.

2. Copy the data by right-clicking > copy.
   • Press Control + C (or Command + C for Mac users) on your keyboard.

3. Select the cell to which your data will be transposed. Ensure that the cells are empty.
   • Right-click, then go to “Paste Special” > “Transposed.”

4. That’s it — your data is now transposed!

Things to Know About the Paste Special Method

When you use the above steps, the data will only be transposed, not the formatting.

The transposed data is static when using this method. If you change any data in the original dataset, you must delete the transposed data and repeat this entire paste process.

Note: If compatible shortcuts are enabled, you can use the paste transpose shortcut in Google Sheets.

3. Transpose Data in Google Sheets Using the Insert Menu

Who doesn’t love new Google Sheets tips and tricks? This one uses the Insert menu to transpose your data. Here’s how:

1. Click on the cell from which you want your transposed data to start. In this example, we use cell D1.

2. In the top menu bar, go to “Insert” > “Function” > “Array” > “TRANSPOSE.”

3. Clicking “TRANSPOSE” will automatically input the formula into your selected cell (D1).

4. Use your mouse to select the cells you want transposed. Hit “Enter.”

5. Enjoy your transposed data!

How To Convert Rows to Columns from One Google Sheet to Another

You can move columns around in Google Sheets. You can even copy rows to another Google Sheet.

But can you convert rows to columns from one spreadsheet tab to another? Absolutely!

1. To add a new spreadsheet tab to your Sheets file, click the plus button (+) in the bottom-left corner of the screen.

2. Select a cell in the new sheet. Type =TRANSPOSE(

3. Click on your first sheet in the bottom navigation bar (Sheet 1).
   • Next, select the range you want to transpose (A1:B6).

4. You’ll notice that the transpose formula is still visible on your screen, even in Sheet 1.
   • Add a closing parenthesis to finish your formula once you’ve highlighted the cells you want to transpose.
   • The complete formula is =TRANSPOSE(‘Sheet 1’!A1:B6)

5. Hit “Enter.” Your data should now be transposed in Sheet 2.

The transpose formula Google Sheets that we used in this example is:

=TRANSPOSE('Sheet 1'!A1:B6)

You can see that it contains the sheet name that references the data range in the first sheet. Google Sheets will flip data from the first sheet to the second sheet.

How Do You Transpose Data In Google Sheets Every N Rows

What can you do if you have a list of items in a column and want to transpose every N row to N columns at a time? By N, I mean any number. I’ll elaborate:

In the example table above, you want to transpose every 2 rows (rows 1, 3, 5). This may be necessary because you only want to transpose a subset of your data.

Whatever your reason, you must first use the INDEX formula to extract the rows before transposing the data.

=INDEX($A:$B,ROW(A1)*N-N+COLUMN(A1))

In the above formula, $A:$B is our reference data (our source rows and columns), and N is the number of skips or jumps you want to make.

You can extract the data by following the steps below:

1. Following the above situation and formula, if you want to transpose every other row, you can replace “N” with 2:

=INDEX($A:$B,ROW(A1)*2-2+COLUMN(A1))

When you enter this formula into a new cell in your spreadsheet, it will look like this:

2. Press “Enter” to see a result for only the first row. In this case, it will be “Name” and “Grade.”

3. To see the rest of the results, simultaneously highlight the Name and Grade.
   • Click and drag the dot downwards from the bottom-right cell to view the rest of the results.

You have now extracted rows 1, 3, and 5 from your original table. Now, it’s time to transpose!

Use the transpose formula, the Paste Special function, or the insert menu trick. In the example below, I’ll be using the transpose formula.

1. Click on an empty cell. In this example, I’ve chosen D5.

2. Enter your TRANSPOSE formula. In this case, I’m transposing the extracted data (the cells D1 to E3).
   • Your formula should be =TRANSPOSE(D1:E3)

3. Hit “Enter,” and you’ll see your results.
   • It can be tedious, but this is one way to transpose every N row in Google Sheets.

Frequently Asked Questions

What Does the TRANSPOSE Google Sheets Function Do?

Google’s TRANSPOSE function transposes columns and rows of an array/range of cells. It converts horizontal tables into vertical ones (and vice versa). When you transpose a table in Google Sheets, you’ll convert your rows into columns and your columns into rows.

Why Is Transpose Not Working In Google Sheets?

If the TRANSPOSE function is not working in your Google spreadsheet, there might be a few reasons why:

• You don’t have the correct permissions to edit the spreadsheet file
• The cells might be locked/be uneditable
• You might have hit the Google Sheets cell limit
• Your formula may be incorrect
• Your reference cells may be incorrect

Conclusion

I hope that this guide has helped you better understand the TRANSPOSE Google Sheets function! These three methods make swapping rows and columns easy, so try them to see which works best for you!

If you’d like to learn how to style the text in your spreadsheet, check out our guide on making text vertical in Google Sheets!