How to Transpose in Google Sheets (+ Function)

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.
   

1. 2. Select the range of cells you want to transpose and close the parenthesis.
   

1. 3. Press ‘Enter’ to see the result: the rows are now columns, and the columns are rows.

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.
   

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).
   

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’.
   

1. 4. That’s it. The rows and columns in the table have been transposed.
   

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).

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.

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.
   

1. 3. Close both parentheses and press ‘Enter’ to see the result.

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.
   

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.
   

1. 3. Close both parentheses and press ‘Enter’ to see the result.
   

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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:

• Google Sheets FILTER Function: How-To & Examples
• How to Sort in Google Sheets (+ Examples)
• Google Sheets QUERY Function (Complete Guide)
• TEXTJOIN in Google Sheets (Easy Guide)
• How To Use IMPORTRANGE Function In Google Sheets?
• How to Use Google Sheets ARRAYFORMULA (+ Examples)
• 23 Must-Know Google Sheets Formulas