When organizing data in Google Sheets, you may realize that the information you meticulously typed into vertical columns would actually make much more sense displayed horizontally across rows (or vice versa). Re-typing all that data manually is tedious and prone to errors. Fortunately, Google Sheets provides built-in tools to “transpose” data, instantly rotating your dataset 90 degrees without losing any information.
You can transpose data in two ways: using a quick copy-and-paste command for static data, or using the TRANSPOSE function for dynamic data that automatically updates if the original data changes.
Method 1: Paste Special (Best for Static Data)
If you simply want to rotate your data once and do not need it to remain linked to the original cells, the “Paste Special” method is the fastest approach.
- Highlight the entire range of data you want to transpose. Ensure you include the header row/column if you have one.
- Copy the highlighted data. You can press Ctrl + C (Windows) or Cmd + C (Mac), or right-click and select Copy. You should see a dashed line appear around your selection.
- Click on a new, completely empty cell. This cell will become the top-left corner of your new, rotated dataset. (Ensure there is enough empty space below and to the right of this cell to accommodate the transposed data, otherwise you will overwrite existing information).
- Right-click on this new empty cell.
- Hover your mouse over Paste special in the menu.
- Click on Transposed at the bottom of the sub-menu.
Your data will instantly paste in the new orientation. Rows are now columns, and columns are now rows. You can now highlight the original, pre-transposed data and press the Delete key to remove it.
Method 2: The TRANSPOSE Function (Best for Dynamic Data)
The Paste Special method is great, but it is a one-time action. If you go back and change a number in the original dataset, the pasted, transposed data will not update. If you need the rotated data to automatically reflect any changes made to the original data source, you must use a formula.
- Click on an empty cell that will serve as the top-left corner of your new dataset.
- Type the following formula:
=TRANSPOSE( - Now, use your mouse to click and drag over the original range of data you want to rotate. The formula will update to include that range (for example:
=TRANSPOSE(A1:B10). - Type a closing parenthesis
)and press Enter.
The transposed data will instantly populate the surrounding cells. Because this data is generated by a formula, you cannot edit individual cells within the new transposed range. If you try, the formula will break and display an error. To edit the information, you must change the data in the original source cells, and the transposed version will automatically update in real time.