Have you got a table in Google Sheets that's in the wrong direction? Whether you want to turn a vertical table horizontal, or the other way around, transposing is your best friend.
On some occasions, you realize that you've used the wrong structure only after you've finished creating a data table in Google Sheets and see the big picture. There's no need to break a sweat here, since you can easily switch the rows and columns by transposing them. Read on to find out how you can do this in Google Sheets!
What Is Transposing Data in Google Sheets?
Transposing data in Google Sheets means taking a vertical table and making it horizontal, or vice versa. When you transpose a data table in Google Sheets, the rows become columns, and the columns become rows.
As an example, take a look at the image in the beginning of this section. We have the name of some volunteers and their pay in columns. So in each row, is the name of one volunteer and their pay. Once we transpose this data, we have the name and pay in rows. So that next to each name is another name, and next to each pay is another pay.
Transposing is a time-saving feature. If you want to switch the rows and columns without transposing, you'd have no choice but to type them again, and this can get arduous in bigger tables or when you create grid charts in Google Sheets.
How to Transpose Data in Google Sheets With Copy and Paste
Copy and paste are dear old friends for anyone who has been using a computer, and closer than friends for people who work with computers. Fortunately, you can use this familiar tool in Google Sheets to transpose data.
As an example, we're going to transpose the data in the spreadsheet below. The goal is to have the names and pays in different rows.
- Select the entire data table. In this example, that's going to be A1 to B7.
- Copy the table by right-clicking it and selecting Copy. You can also press Ctrl + C on your keyboard.
- Select a cell where you want the data from the first cell to appear. In our table, this new position will be where the data from cell A1 will be placed.
- Right-click the cell.
- In the right-click menu, go to Paste special and select Transposed.
Viola! Now you have your data table with the columns and rows switched. Notice that the styling from the previous data table have been imported accordingly as well.
How to Transpose Data in Google Sheets With Formula
If you're in the i-only-use-formulas club, then we have something for you, too. In Google Sheets, you can also transpose data using the TRANSPOSE formula. This way, you don't have to waste clicks on selecting the data table. There is a catch, however. Since the output from this formula is an array in Google Sheets, you can't alter single cells in the output.
Another difference between the TRANSPOSE formula and Paste special is that the formula will ignore the styling. So the fonts, colors, etc., from the input table won't move to the output table. This can be both good and bad, depending on your perspective.
With that being said, let's move on to using the TRANSPOSE formula. The formula has the following syntax:
=TRANPOSE(input_table_address)
The cell where you enter the formula will be where the first cell appears, just like the copy and paste method. Now let's apply this formula to the same example.
- Select the cell where you want the transposed table to start.
-
Enter the formula below:
=TRANPOSE(A1:B7) - Press Enter.
There you have it! The table is now transposed. Notice that the styling hasn't been imported. Though, you can style the cells yourself.
Rows or Columns? Doesn’t Matter
It could be heartbreaking to look at a data table that took you hours to build just to realize that it would have been better if you had switched the rows and columns. At least that would have been the case if you didn't know how to use transpose in Google Sheets.
With transpose, you can switch the rows and columns in a matter of seconds. It's a piece of cake, and now you know how to do it! So, if you were wondering how you can fix your table without having to start from scratch, go on and transpose the data with a few clicks, or a single formula!