Don’t forget to share this post

How you structure data in your spreadsheets depends on many factors, including convenience, analytical intent, and personal preference. In fact, sometimes you need a dataset’s records to be in rows, and sometimes you need them to be in columns. Fortunately, transposing data in Google Sheets is very easy. In just a few seconds, you can turn rows into columns and columns into rows.

In this guide, you will learn three ways to transpose data in Google Sheets. First, you will learn how to use the function, which returns a transposed and linked version of the original data. This allows you to keep your transposed version updated with any changes to the original data. Second, you will learn to transpose data using the special paste option. Third, you will learn how to use a compatible shortcut to get to the ‘transposed’ option really quickly. Finally, you have step-by-step instructions on how to combine TRANSPOSED with other functions: UNIQUE and SPLIT.

How to Transpose in Google Sheets?

There are two main ways to transpose data in Google Sheets: the special paste option and the TRANSPOSE function. Unfortunately, there’s no specific shortcut to transpose data when pasting it. However, if you’re a fan of keyboard shortcuts and have enabled compatible shortcuts, you can get to the transpose option very quickly.

Use the TRANSPOSE Function in Google Sheets

Follow the steps below to transpose data with the TRANSPOSE function.

  1. 1. In an empty cell, type the TRANSPOSE function. Check that there are enough empty cells around it for the data you are transposing.
How to Transpose in Google Sheets Function TRANSPOSE Function
How to Transpose in Google Sheets (+ Function) - TRANSPOSE Function
  1. 2. Select the range of cells you want to transpose and close the parenthesis.
How to Transpose in Google Sheets Function Select Range of Cells
How to Transpose in Google Sheets (+ Function) - Select Range of Cells
  1. 3. Press ‘Enter’ to see the result: the rows are now columns, and the columns are rows.
How to Transpose in Google Sheets Function Transposed with Function
How to Transpose in Google Sheets (+ Function) - Transposed with Function
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Use Paste Special to Transpose in Google Sheets

Follow the instructions below to transpose your data when pasting into a new cell.

  1. 1. Select the range of cells you want to transpose.
How to Transpose in Google Sheets Function Select Range of Cells 2
How to Transpose in Google Sheets (+ Function) - Select Range of Cells
  1. 2. To copy the cells, you can go to Edit > Copy, right-click on your selection, and choose copy, or use the shortcut: Ctrl + c (Windows) or Cmd + c (Mac).
How to Transpose in Google Sheets Function Copy Selection
How to Transpose in Google Sheets (+ Function) - Copy Selection
  1. 3. Select the cell where you want the transposed data and go to the Edit menu or right-click on the cell where you want to paste. Under ‘Paste special’, select the option to paste ‘transposed’.
How to Transpose in Google Sheets Function Paste Transposed
How to Transpose in Google Sheets (+ Function) - Paste Transposed
  1. 4. That’s it. The rows and columns in the table have been transposed.
How to Transpose in Google Sheets Function Transposed Result
How to Transpose in Google Sheets (+ Function) - Transposed Result

Use Shortcut to Transpose in Google Sheets

There is no specific keyboard shortcut for pasting transposed data. However, if you have enabled compatible shortcuts, you can use a shortcut to select this option quickly. After copying the cells you want, click on an empty cell and use the following shortcut to transpose and paste: Alt + e, s, e (Windows) or Ctrl + Option + e, s, e (Mac).

How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Use TRANSPOSE with Other Functions

While the function is very useful on its own, you can manipulate your data with other functions before using it. By nesting functions, you can transpose the results of your formulas. Below, you have examples of how to combine TRANSPOSE with two other functions: UNIQUE and SPLIT.

UNIQUE & TRANSPOSE

Imagine you have a table with sales for different teams. You want to create a table with summary data for each team, with the team name as the column header. By using TRANSPOSE and UNIQUE on the sales team column, you can get the unique team names in a row.

  1. 1. Select the cell where you want the data, ensuring there are enough empty cells below and to the right.
How to Transpose in Google Sheets Function Select Cell
How to Transpose in Google Sheets (+ Function) - Select Cell
  1. 2. Type the TRANSPOSE function, then the UNIQUE function, and select the range of cells you want. In this case, select the data in the ‘sales_team’ column.
How to Transpose in Google Sheets Function Type TRANSPOSE UNIQUE Functions
How to Transpose in Google Sheets (+ Function) - Type TRANSPOSE & UNIQUE Functions
  1. 3. Close both parentheses and press ‘Enter’ to see the result.
How to Transpose in Google Sheets Function Unique List Transposed
How to Transpose in Google Sheets (+ Function) - Unique List Transposed

SPLIT & TRANSPOSE

Imagine you have a cell containing a list of items separated by semicolons. Follow the steps below to split these items into separate rows instead of columns.

  1. 1. Type the TRANSPOSE function in an empty cell, followed by the SPLIT function.
How to Transpose in Google Sheets Function Type TRANSPOSE SPLIT Functions
How to Transpose in Google Sheets (+ Function) - Type TRANSPOSE & SPLIT Functions
  1. 2. Select the cell or cells containing the data you want to split and transpose. After a comma, type the delimiter between quotation marks.
How to Transpose in Google Sheets Function Select Cell to Split Transpose
How to Transpose in Google Sheets (+ Function) - Select Cell to Split & Transpose
  1. 3. Close both parentheses and press ‘Enter’ to see the result.
How to Transpose in Google Sheets Function List Split Transposed
How to Transpose in Google Sheets (+ Function) - List Split & Transposed

Conclusion

It’s easy to transpose data in Google Sheets. In a few seconds, you can turn a row into a column or vice versa and even switch the rows and columns of a whole table. Since the data is linked, changes to the original data will be reflected immediately in the transposed version. You can also combine TRANSPOSE with other functions to manipulate the data before transposing.

You now know how to transpose data using the function, the special paste option, or a compatible shortcut. Additionally, you have examples of how to combine TRANSPOSE with other functions. You have step-by-step instructions on how to use it with UNIQUE to filter a list to unique values before transposing. Finally, you have step-by-step instructions on how to SPLIT a cell’s contents before transposing.

To learn more about functions you can combine with TRANSPOSE in Google Sheets, check out our guides on:

Maria Del Olmo
Originally published Mar 18 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets