Knowing how to transpose data in Google Sheets is useful if you want to switch the rows and columns of an array or data range.
Meaning, the orientation of your list of data changes from vertical to horizontal and vice versa.
Table of Contents
In Google Sheets, there are two convenient ways to transpose data:
- Using the TRANSPOSE function, which we tackled in our previous article.
- Using Paste special, which is a feature under the Edit menu.
The rules for transposing data in Google Sheets are as follows:
- Using the TRANSPOSE function gives you the values, but not the formatting, whereas the Paste Special method includes the formatting.
- The results of the TRANSPOSE function are dynamic, which means any changes to the original dataset will reflect on the transposed data. On the other hand, the results of the Paste Special option are static, which means changes to the original dataset will not reflect on the transposed data.
- In using the TRANSPOSE function, you cannot delete any part of the transposed data. As for using Paste Special, changes can be applied to transposed data without altering the original dataset.
- If you overwrite any cell that has the transposed data, the function would return a reference error. Overwriting any cell that has the transposed data using Paste Special will alter the information permanently.
Let’s take an example.
Irene, a school librarian, needs to update the record template that she’s using. Please take a look at her old record below:
The format is changed from the vertical to the horizontal orientation. Instead of using the copy-paste method to switch the orientation of her table, Irine used the TRANSPOSE function to make her life easier. Please see her new table below:
Pretty convenient, right?
However, notice that the transposed data doesn’t have the same format as the original dataset. So, Irene would need extra steps to either replicate the format or create new formatting for her new table.
Luckily, Irene knows how to use the Format Painter menu in Google Sheets. So, it’s an easy fix for her.
On the other hand, Jacob, Irene’s colleague, used a different method to change the format of his record template. See his old template below:
Instead of using the Transpose function, he switched his dataset orientation using the Paste Special option in Google Sheets.
Unlike Irene, Jacob didn’t have to format his transposed data.
Why is this so?
Well, that’s because the Paste Special menu transposed the data together with the formatting of the original dataset.
Watch out for a more advanced tutorial and examples on how you can transpose data in the coming weeks. Be sure to subscribe to be notified.
Awesome! Let’s begin getting to know more about the process and steps in transposing data in Google Sheets.
The Anatomy of the TRANSPOSE Function and Paste Special method in transposing data
So the syntax (the way we write) the TRANSPOSE function is as follows:
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 dataset, array, or the reference to the range of columns and rows you want to swap or transpose.
On the other hand, the Paste Special menu can be found under the Edit menu in Google Sheets:
It’s like the copy-paste method. But, instead of the ‘paste’ step, you click the Edit > Paste special > Paste transposed. Alternatively, after copying the dataset, you can access Paste special by Right click > Paste special > Paste transposed.
A Real Example of Transposing Data
Take a look at our record table example below to see how to transpose data in Google Sheets:
Like what we’ve discussed in our previous article regarding the TRANSPOSE function, the function takes the array of information and switches them to their opposite orientation. In the sample above, since the argument passed is in the vertical orientation (B3:C8), the Transpose function switched the array into horizontal orientation (B11:H12).
Please note that this method transposed the data without the formatting of the original dataset.
The same logic applies if you want to switch from the horizontal orientation to the vertical orientation. See sample below:
See how our Transpose function was able to switch the orientation of its dataset. The only minor issue is that it leaves the formatting in the original dataset.
What if we need to copy the formatting as well? Do we need to use the Transpose function and replicate the formatting in the original set? What if there are hundreds of tables, with different formatting, that we need to change the orientation?
Worry no more! That’s when the Paste special will come in handy.
Take a look at the same table below:
Using the Paste special method, the final table should look like this:
To summarize, both methods have the same purpose, and that’s to transpose data. They can be very useful. It only depends on how or what you want the results to be.
You may make a copy of the spreadsheet using the link I have attached below.
How to Transpose Data in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting B11, where I want to show my result.
- 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).
- Type open parenthesis ‘(‘ or simply hit the Tab key to let you use that function.
- Now the exciting part! Let’s give our function its argument, the array or range of your dataset. Click cell B2 and drag it until cell C8. Alternatively, you can type in B2:C8.
- Finally, hit your Enter or Tab key. Unformatted, cell B11:H12 will now show you the transposed dataset.
- 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 apply a new format.
- Alternatively, we can use the Paste special functionality of Google Sheets to be able to attain the same results.
- Make your entire dataset active by clicking on it.
- Copy the values by clicking Edit > Copy or simply click Control + C on your keyboard.
- You’ll know that the values were copied in the clipboard when the table borders become broken lines.
- Click on the cell where you want the transposed data to appear. In this case, I used B11.
- Now, another fun part! Click Paste special under the Edit menu. Then, click Paste transposed.
- B11:H12 will now show you the transposed dataset, including the format of the original dataset.
That’s pretty much it. You can now transpose data in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.