In this tutorial, you will learn how to convert columns to rows in Google Sheets
How To Convert Columns To Rows in Google Sheets
There may be times when you have data in a format that is more convenient for one type of analysis, but you need to convert it for a different type of analysis.
One common example of this is when you are given data in a columnar format, but you need to convert it into row format.
In this guide, we will show you how to convert columns to rows in Google Sheets. We will also show you two different ways to do this operation so you can choose the method that works best for your data.
How To Transpose Data in Google Sheets
Here’s how to transpose data in Google Sheets.
First, select the cell range you want to transpose. You can quickly do this by selecting any cell in the range and typing the keyboard shortcut Ctrl + A.
In this example, we want to transpose our table so that each row corresponds with a product and each column is a specific month.
Type Ctrl + C to copy the selected range.
Right-click on a new cell and select the Transposed option under Paste special.
Make sure that you have enough empty space around the target cell to avoid overwriting any existing data.
The Transposed option will return the copied range in an altered format, converting columns to rows and vice versa.
A downside of this option is that the pasted table is not dynamic. This means that any changes made to the original table will not reflect in the new transposed table.
We can create a dynamic transposed table by using the TRANSPOSE function.
You may either type the cell range yourself or use your cursor to select the range you want to transpose.
Hit the Enter key to evaluate the TRANSPOSE function.
Do note that the transposed output will not copy cell formatting such as bold text or background color.
Any number formatting applied to the original formatting will still be retained. For example, values formatted in dollars will still be formatted appropriately after being transposed.
This guide should be everything you need to convert columns to rows in Google Sheets.
You may make a copy of this example spreadsheet to test out the different methods to transpose values in a spreadsheet.