How to use the TRANSPOSE Google Sheets formula

In Google Sheets, if you ever need to flip the columns and rows of an array or a data range, TRANSPOSE Google Sheets function is the one to go with. It swaps — or transposes — the rows and columns, changing the orientation of your spreadsheet data from horizontal to vertical or vice versa.

This can be useful for presentation purposes or to make your table easier to read, for example if your dataset has grown to a large size and the number of columns means it’s very wide, or very long. TRANSPOSE is a handy alternative to copy-pasting, allowing you to switch rows and columns in Google Sheets instantly without errors.

This guide shows how to turn rows into columns, or columns into rows, with the TRANSPOSE function. It also covers how to paste data transposed with Paste special, and how to combine TRANSPOSE with UNIQUE and SPLIT.

TRANSPOSE syntax

TRANSPOSE(array_or_range)

array_or_range – the array or the reference to the range of columns and rows you want to swap or transpose.

How to use TRANSPOSE Google Sheets function

This is one of the simplest formulas in Google Sheets. Take a look at the following examples:

My sample data fills cells A1 to C7. At present, my list of Employee ID numbers (Emp ID#) runs vertically along the left hand side of spreadsheet, in a column. Instead, I want to see my Employee ID list horizontally, along the top of my spreadsheet.

To do this, I enter a TRANSPOSE formula into cell D1: 

You can see that my original data range has been inverted. The rows and columns from the input array_or_range have been swapped and the new (flipped) dataset starts in cell D1. 

Remember to leave enough space in your spreadsheet for the new table of data. Otherwise, the formula returns #REF! error:

Another option is to enter an array instead. Here’s an example:

When entering the array, use semi-colons to separate each individual row value in the input range. For individual column values, use commas. As before, you need to ensure that there is enough empty space in your spreadsheet for the new, transposed table of data.

How to replace the old table

If you want to delete the original table of data and replace it with the transposed data

1. Copy the new table (the data you have transposed).
2. Go to Edit > Paste special > Values only.
3. Paste directly over the original table.

Transpose and keep formatting

You might notice that your formatting is lost when you transpose the data in Google Sheets. In this case, use the copy-paste method:

1. Select the cells you want to transpose.
2. Copy the cells.
3. Click inside the cell where you want the new table of inverted data to start.
4. Go to Edit > Paste special > Transposed. The steps with screenshots are in the paste special section below.

How to paste transposed data in Google Sheets

The TRANSPOSE formula keeps the flipped table linked to the original, so it updates whenever the source data changes. When you only need a one-off copy, paste the data transposed instead. The pasted table is static: it keeps the original formatting, and you can edit or sort it like any other range.

Use paste special to transpose

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

3. Select the cell where you want the transposed data. Go to Edit > Paste special > Transposed, or right-click the cell and choose Paste special > Transposed.

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

Use a keyboard shortcut to paste transposed data

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. Select the cell where you want the data, ensuring there are enough empty cells below and to the right.

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.

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. Type the TRANSPOSE function in an empty cell, followed by the SPLIT function.

2. Select the cell or cells containing the data you want to split and transpose. After a comma, type the delimiter between quotation marks.

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

Work more efficiently in Google Sheets

That’s how to quickly switch your data from vertical to horizontal, or rows to columns. Looking for more time-saving tips and tricks in Google Sheets? Learn how to filter Google Sheets without affecting other users or discover how to connect Google Sheets automatically.