How to Convert Column to Row in Google Sheets (2 Simple Ways)

For formatting or analysis, we often need to convert columns to rows in Google Sheets. For this purpose, we can use the built-in option Paste Special or the TRANSPOSE function.  In this article, we will explain both of these methods.

The Paste Special is a built-in option in Google Sheets that allows us to perform a bunch of different types of paste, including transposing to convert a column to a row. This option also preserves the original formatting of your data.

To explain the procedure, we will use the sample dataset below. It contains some random items and the quantities in two separate columns.

We’ll now convert these two columns into rows. It means the items will be now shown in row 1 and the quantities in row 2.

Steps:

➤ First, select all the data, i.e., select A1:B6. Remember to select the headers too.
➤ Now, press  Ctrl  +  C  for Windows or press  Command  +  C  if you are using a Mac.

➤ Click on cell C1 and name it as Converted data.
➤ Then, select cell C2 and right-click on it. A new pop-up will open.
➤ Now, move your cursor to Paste special, and another list of options will open up.
➤ Select Transposed from the list.

➤ Finally, you will see that the columns are converted into rows.

The TRANSPOSE function is a dynamic function in Google Sheets that not only allows us to convert columns to rows, but also updates the transposed values accordingly if our original data changes. You can also use it to convert rows to columns in Google Sheets.

Steps:

➤ First, select C1 and give it a name.
➤ Then, click on cell C2 and insert this formula:

➤ Then, press Enter, and you will see that all the columns have been converted into rows.

Frequently Asked Questions

Will Converting a Column to a Row in Google Sheets Remove Formulas?

If you use the Transpose function to convert the column to a row in Google Sheets, it will only show the values without the formulas. However, if you change the formula in your original data, it will automatically update the value. On the contrary, the Paste Special option creates a static version of your dataset. So, though it shows the formula, it changes the formula and is not recommended for converting column that contains formula to rows.

Can I Convert Columns with Blank Values to Rows in Google Sheets?

Yes, you can. With the Transpose function or the Paste Special option, you can easily convert columns with blank values to rows. These methods do not change the values; that is, the blank cells will remain the same after the conversion.

Can I Convert a Dataset in Google Sheets that Has Columns with Comments to Rows?

Unfortunately, the Paste Special option does not preserve the comments after converting the columns to rows. However, you can use an Apps Script or manually copy and paste each entry with the comments.

Does the Paste Special Option Convert a Dataset with Hidden Columns to Rows?

No, the Paste Special option ignores the hidden columns or any hidden values. So, you need to unhide the columns or values before using the Paste Special > Transposed option. Alternatively, you can simply use the Transpose function. As this function directly refers to the cells, it can convert even hidden columns or values to rows.

Wrapping Up

With this article, we have learned to convert columns to rows using two different methods, one is using the Google Sheets built-in option Paste Special and the other is writing a formula with the Transpose function. Hopefully, these methods will help you convert any column to a row. Also, reach out in case you have any queries.