How to Use TRANSPOSE Function in Google Sheets

The TRANSPOSE function in Google Sheets is useful if you want to swap the rows and columns of an array or data range.

Meaning, the TRANSPOSE function changes the orientation of your list of data from vertical to horizontal and vice versa.

The rules for using the TRANSPOSE function in Google Sheets are as follows:

  • The function gives you the values, but not the formatting. Nonetheless, we will be providing guides, on a separate article, on how to transpose data together with the formatting in the coming weeks. So, watch out and be sure to subscribe to be notified.
  • The results of the function are dynamic, which means any changes to the original dataset will reflect on the transposed data.
  • The TRANSPOSE function is an array formula, so you cannot delete any part of the transposed data. Should you like to delete it, you need to delete the entire dataset.
  • Make sure that there are enough cells to accommodate the transposed data. Otherwise, the TRANSPOSE function would return a reference error.
  • Similarly, if you overwrite any cell that has the transposed data, the function would return a reference error.

Let’s take an example.

Greg, a professor in senior high, decided that it would be much organized if he put his students’ grades horizontally in Google Sheets. Please take a look at his table below:

Instead of using the copy-paste method to switch the orientation of his table, Greg used the TRANSPOSE function to make his life easier. Please see his new table below:

Pretty convenient, right?

What if Greg decides to revert to his initial table setup?

While I’m sure our Greg here kept a copy of his old file for reference, switching from the horizontal orientation of your dataset to vertical would be an easy fix. The same TRANSPOSE function is applicable in this scenario. Please see the sample below:

The TRANSPOSE function is also very helpful if you’re trying to copy values from different resources other than Google Sheets. Sometimes, these other resources aren’t aligned with how Google Sheets treat dataset.

Let’s take a look at the sample below:

Racquel is an experienced virtual assistant and her day-to-day responsibility revolves around gathering information from the Internet. One of her tasks is to copy a set of email addresses of possible prospects. See the information below:

For better data management, she needs to copy this information to Google Sheets. However, pasting the values above resulted in having them in one cell:

Racquel used the simple trick she knows using 2 different functions. First, she used the SPLIT Function to split the email addresses. See the step 1 result below:

Next, she used the TRANSPOSE function to switch the orientation of her dataset to vertical:

Racquel could have just copied the email address one by one and pasted them individually into each row. However, can you imagine doing that to thousands of email addresses? It’s tedious and time-consuming. Good thing our amazing Racquel here has subscribed and has been following us for recent Google Sheets articles and updates.

Watch out for a more advanced tutorial and examples on how you can use the TRANSPOSE function in the coming weeks. Be sure to subscribe to be notified. 

Awesome! Let’s begin getting to know more about our TRANSPOSE function in Google Sheets.

The Anatomy of the TRANSPOSE Function

So the syntax (the way we write) the TRANSPOSE function is as follows:

=TRANSPOSE(array_or_range)

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
  • TRANSPOSE() this is our TRANSPOSE function. It will take an argument and switch the orientation from horizontal to vertical or vice versa.
  • array_or_range is the array or the reference to the range of columns and rows you want to swap or transpose.

 

A Real Example of Using TRANSPOSE Function

Take a look at our score table example below to see how the TRANSPOSE function is used in Google Sheets.

The TRANSPOSE function takes the array of information and switches them to their opposite orientation. In this case, since the argument passed is in the vertical orientation (B2:C10), the TRANSPOSE function switched the array into horizontal orientation (F2:N3).

Notice that we provided a two-dimensional array (column B and C) as the argument to our TRANSPOSE function and it converts it to the same two-dimensional array. 

What if you just need the first column to be switched to horizontal orientation? Will the TRANSPOSE function allow that?

Definitely, yes! You just need to let our function know that you only want the first column to be transposed. See our sample below:

 

You may make a copy of the spreadsheet using the link I have attached below.

How to Use TRANSPOSE Function in Google Sheets

  1. Click on any cell to make it the active cell. For this guide, I will be selecting E2, where I want to show my result.

 

  1. Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘transpose‘ (or ‘TRANSPOSE‘, not case sensitive like our other functions).

 

  1. Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.

 

  1. Now the exciting part! Let’s give our function its argument, the array or range of your dataset. Click the cell B2 and drag it until cell C7. Alternatively, you can type in B2:C7.

 

  1. Finally, hit your Enter or Tab key. Cell E2:J3 will now show you the transposed dataset.

 

  1. We mentioned earlier that the TRANSPOSE function doesn’t give you the same formatting as the source of the dataset. So, you need to manually apply or copy the formatting of your original dataset. Or better yet, apply a different format to your transposed dataset. In this example, I will just manually copy the format.

 

That’s pretty much it. You can now use the TRANSPOSE function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like