When working in spreadsheets, there may be times when you want to convert rows into columns or turn columns into rows.
In spreadsheet programs like Google Sheets and Excel, this is called transposing your data. There are built-in ways to transpose in these programs so you can easily invert your sheet if you need to.
In this tutorial, I will show you two different methods to transpose data in Google Sheets. You can use these methods to transpose single rows and columns or multiple rows and columns.
Table of Contents
Transpose Data Using Paste Special
The fastest way to quickly transpose your data is often copying your data and using paste special to transpose it.
To show you how this is done, I will transpose this example table:
After I transpose this data, the colors will be in one row, and sales will be in another row, essentially converting my current rows into columns.
Here are the steps to do this:
1. The first thing you need to do is highlight the area that you want to transpose
2. Next you need to copy. You can either press CTRL + C on your keyboard to copy or you can right-click on your mouse and select Copy
3. Next select a cell where you want to paste your data and right-click. Select Paste special
4. Select Paste transposed
5. This will paste your data so that it is flipped, with columns and rows swapped from the original table
Using the TRANSPOSE Function
Another way to do this in Google Sheets is by using the TRANSPOSE function. This method has the benefit of being dynamic. If your data changes, so will your transposed values. The other method is static, so you will have to continue to do it each time your data changes.
The syntax of the TRANSPOSE function is:
=TRANSPOSE(array_or_range)
- array_or_range – this means the range of data or array that you want to swap rows and columns for.
To show you how to use this function, I will perform it on the same dataset we used in the previous example.
Here’s how it is done:
1. Select the cell where you want your transposed data to display and Enter the equals sign (=) and type “transpose”. Press Tab when you are done to enter the formula
2. Select the range of cells that you want to perform this function on, then add your closing parenthesis to the formula “)”
3. Press Enter on your keyboard and your data will be transposed
Closing Thoughts
Understanding how to transpose your data can be useful as you continue to work with spreadsheets.
We have shown 2 methods in this tutorial, and each method has its pros and cons.
Paste special transpose has the benefit of being able to also paste formatting, but this method is static. You will need to repeat the process if your data changes.
The TRANSPOSE function does not copy formatting, however, this is a dynamic formula, so you only need to set it up once and your returned table will change as your input values change.
More Google Sheets Tutorials:
How to Insert Rows
How to Delete Empty Rows
How to Create a Dependent Drop Down List
How to Reverse the Order of Rows