Google sheets extremely powerful that built with over 200+ reusable functions and shortcuts. It allows to create table and fill data combine with row and column. Sometimes you have to flip row and column data due to stakeholder matter instead of rewrapping data let check how to transpose data in google sheets clearly and easily.
Transpose data in google sheets simple definition is flipping data orientation from row-wise to column-wise or column-wise to row-wise in the sheets data, To quick implement,
- Open a spreadsheet in Google Sheets.
- Copy the data to which you want to flip (Ctrl + C or right-click > select copy in the dropdown).
- The select cell which you need to apply transpose data.
- On the top of navigation click-click Edit( middle of File and View ) > Paste Special then choose to paste transposed in the dropdown.
- Your data table will transpose(flip) base on row-wise and column-wise
In this article, we covered the most common ways (2 methods) to flip row and column without breakup you’re existing expression in a spreadsheet in google sheets.
There are two ways to transpose data in google sheets,
- Using Paste Special to Transposed option in data sheet.
- Using TRANSPOSE Function data in google sheets.
The above method may need to decide base on your existing datasheet arrangement. let’s check how to decide which method prefer with your datasheet.
Contents
Using Paste Special to Transposed Data in google sheets
This is the classic method of arranging transpose data in google sheets. When you copy data in the datasheet, google sheets offer an additional past option instead of regular data paste. paste transposed option is one of the data past options that can be used for flipping your copied data vertically and horizontally in the datasheets.
- Open a spreadsheet in Google Sheets.
- Select & Copy data set which you want to transpose(flip) – (Ctrl + C or right-click > select copy in the dropdown).
- In the select cell you need to apply a new data arrangement and On the top of the navigation click-click Edit( middle of File and View ) > Paste Special then choose to paste transposed in the dropdown.
- Your data set will transpose against row-wise and column-wise.
Limitation of Paste Special transpose option.
This is a shortcut way that changes your data set orientation from row-wise to column-wise or column-wise to row-wise, but there has the following limitation,
- Not suitable for the dynamic data set.
This option is preferred for static data set, which means if you plan to change orientation in dynamic data set, parent data set latest will not be applied with this transpose data which update by paste special transpose option. The reason is actually your new transpose data will not sync with the parent data set. It just a clone of your parent data set. - Not well working with merge cell.
As mention, this paste option will only consider changing the orientation of the row and column against of existing data set arrangement. It just read the existing cell pattern and updates the data, base on that. If you merge cell on column or row, paste special transpose option will flip all data set base on that cell pattern and it will continue till data cell in transpose data set.
In case you want the transposed data to be dynamic & prevent this merging cell pattern, use the TRANSPOSE function shown below.
Using TRANSPOSE Function in google sheets.
This is a special function that google sheets introduce for flipping data orientation from row-wise to column-wise or column-wise to row-wise base on an array or range of cells.
If you need to implement with external data range make sure that you have used the IMPORTRANGE function within the TRANSPOSE Function in google sheets.
About example
I created a simple datasheet, this data table contains 2018 to 2020 Toyota corolla vehicles import to the European market from Japan. All data arrange in vertically but I need to flip orientation to horizontally,
Download Sample Sheet
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
- Open a spreadsheet in Google Sheets.
- Identity data range which you want to transpose(flip)
You use name ranges in Google Sheets to keep better track of them and create cleaner formulas. Still don’t have any idea about How use named ranges in google sheets, we highly recommend reading How To Named Ranges in Google Sheets: Complete Guide Article.
- Then select cell which you want to execute transpose data in google sheets then type =TRANSPOSE(CEll_RANGE) base on the above example sheets select A18 then type =TRANSPOSE(A2:D14).
- Your given range will flip orientation base on row-wise and column-wise without braking cell pattern. But merged cell will reset because the TRANSPOSE function tries to return the best output without break up the existing cell pattern. So you have to remerge once again.
Also, your cell style will not apply to the TRANSPOSE function. You have to update that manually. Also, your transpose data table will update automatically based on parent datasheets.
Ok, I hope to cover fully part of the transpose data in google sheet, now you have an idea about flip data orientation using Using Paste Special option (shortcut option) or the TRANSPOSE function in google sheets. Enjoy!
Conclusion
Google sheets are extremely powerful now. A lot of freelancers and data analysis experts are recommending google sheets as their primary data management tool. When you work with large data set sometimes you have to change data set the orientation to the better arrangement or client request generally it call transpose data in google sheets. It very hard to do manually & not practical, so the google community offer a feature called TRANSPOSE function to manipulate this kind of matter easily.
