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

By

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.

Transpose Google Sheets—Example of a transposed table in Google Sheets, 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.

Google Sheets example table with 2 columns and 6 rows

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.
Example Google spreadsheet with cell D1 selected
  1. 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).
Example of transpose formula in Google spreadsheet
  1. Hit “Enter” for the data to instantly transpose.
example of transposed data in google sheets

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.

Example of transpose formula in a cell range that has data

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

transpose function error in google sheets

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.
A highlighted range of cells in Google Sheets
  1. Copy the data by right-clicking > copy.
    • Press Control + C (or Command + C for Mac users) on your keyboard.
  1. Select the cell to which your data will be transposed. Ensure that the cells are empty.
    • Right-click, then go to “Paste Special” > “Transposed.”
Google sheets paste special menu and transposed option
  1. That’s it — your data is now transposed!
example of transposed data in google sheets

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.
Example Google spreadsheet with cell D1 selected
  1. In the top menu bar, go to “Insert” > “Function” > “Array” > “TRANSPOSE.”
Google Sheets insert menu, going into function, then array, then transpose
  1. Clicking “TRANSPOSE” will automatically input the formula into your selected cell (D1).
Example cell with TRANSPOSE formula
  1. Use your mouse to select the cells you want transposed. Hit “Enter.”
Transpose formula with highlighted data cells
  1. Enjoy your transposed data!
example of transposed data in google sheets

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.
Plus button (+) to add a new spreadsheet tab in Google Sheets
  1. Select a cell in the new sheet. Type =TRANSPOSE(
=TRANPOSE( typed in cell
  1. Click on your first sheet in the bottom navigation bar (Sheet 1).
    • Next, select the range you want to transpose (A1:B6).
Cells A1 to B6 highlighted in Sheet 1
  1. 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)
Cell with finished TRANSPOSE formula
  1. Hit “Enter.” Your data should now be transposed in Sheet 2.
Transposed data 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:

Example Google Sheets table

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:

Index formula entered into a Google Sheets cell
  1. Press “Enter” to see a result for only the first row. In this case, it will be “Name” and “Grade.”
Initial result of the index formula
  1. 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.
Completed results of the index formula

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.
Google Sheet with cell d5 selected
  1. 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)
Transpose formula entered in Google Sheets cell
  1. 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.
Transposed cells after using the index formula

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!

Fact Checked By Cindy Wong

Popular Posts

2 thoughts on “How To Use The Transpose Google Sheets Formula: 3 Easy Methods in 2024”

  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.