Javascript required
Skip to content Skip to sidebar Skip to footer
Home / How to Turn Rows Into Columns in Google Sheets

How to Turn Rows Into Columns in Google Sheets

Watch Video – How to Transpose Data in Google Sheets (Using Formula and Paste Special)

In this tutorial, I will show two ways to quickly transpose data in Google Sheets.

Transposing the data means that you change the orientation where the rows become columns and the columns become the rows (as shown below).

In Google Sheets, there are two easy ways to transpose data (i.e., switch rows and columns):

  • Using the TRANSPOSE function
  • Using Paste Special

The method you choose to swap rows and columns will depend on whether you want the data to be static or dynamic (covered in detail in the sections later).

So let's get started!

Using the TRANSPOSE Function

One of the reasons I love Google Sheets because it has some useful functions that make life easy when working with data. And TRANSPOSE function is one of these.

Suppose you have the dataset as shown below:

Data to Transpose in Google Sheets

If you want to transpose the above dataset, you can use the formula below:

          =          TRANSPOSE          (          A1:B11          )

I have entered the above formula in cell D1, so it transposes the data from D1:N2.

Transpose data using the function in Google Sheets

Note that you don't need to worry about the exact range where the data would be transposed and placed, All you need to do is enter the formula in the cell where you want the transpose data to begin (D1 in our example) and Google Sheet will automatically transpose the entire data and give you the result.

The only thing you need to ensure is that there are empty cells to accommodate the transposed data. If there is any cell that has some text/number or even a space character, TRANSPOSE formula wouldn't work and give you a reference error.

REF Error when there is some data in cell already

Also, when you hover the cursor over the error, it will tell you exactly which cell has data already so you can correct that.

When you use the TRANSPOSE function, it only gives you the values and not the formatting.

The result that you get from using the TRANSPOSE function is dynamic. This means that if you change anything in the original dataset, that change would automatically be reflected in the resulting transposed data.

Also, this is an array formula, so you can not delete any part of the resulting data. If you want to delete it, you need to delete the entire dataset.

In case you overwrite any of the cells, the entire resulting dataset would vanish and you will see the reference error in the top-left cell. This would automatically revert as soon as you clear the cell.

Using Paste Special to Transpose the Data

Another quick method of transposing data in Google Sheets is using the Paste Special feature.

Suppose you have the dataset below that you want to transpose.

Below are the steps to do this:

  1. Select the entire dataset that you want to transpose (A1:B11 in our example)
  2. Copy the data (you can use the keyboard shortcut Control + C or right-click and click on copy)
  3. Right-click on the cell in which you want to transpose the data
  4. Hover the cursor over the Paste special option.
  5. Click on Paste transposed

Click on Paste Transpose

This will paste the transposed data starting with the cell which you selected while pasting.

Note that this technique copies and paste everything – including values, formulas, formatting, etc. If you only want to copy the values and not the formula or formatting, you need to first copy and paste the transposed data, then copy it again and paste as values only.

Since we are copying and pasting the data, it's not dynamic (unlike the TRANSPOSE formula). This means that if you make any changes in the original dataset, the changes wouldn't be reflected in the transposed data.

So these are the two ways you can use to transpose data in Google Sheets (i.e., switch rows and column in Google Sheets).

Hope you found this tutorial useful.

You may also like the following Google Sheets tutorials:

  • How to Delete Empty Rows in Google Sheets
  • Convert Formulas to Values in Google Sheets
  • How to Indent Text in Google Sheets
  • How to Change Text Case in Google Sheets (Upper, Lower, Proper, or Sentence)
  • How to Get the Word Count in Google Sheets
  • How to Filter By Color in Google Sheets (Using a Formula)

Related posts:

How to Turn Rows Into Columns in Google Sheets

Source: https://spreadsheetpoint.com/transpose-google-sheets/