Google Sheets is a great alternative for premium spreadsheet programs. The web-based spreadsheet has several features, including altering the data on your worksheet. Among these, Sheets has a special feature to convert rows to columns and vice-versa!
For instance, if your colleague switched the rows and columns during data entry, Google Sheets has the feature to convert rows to columns easily.
How to Convert Rows to Columns in Google Sheets?
There are two ways you can work your way through the issue. The Transpose feature is responsible for converting your rows to columns and vice versa in Google Sheets. You could apply the methods we’ve mentioned on Google Sheets across all devices.
The paste special is a distinct feature on Google Sheets. This feature is used to alter or extract only specific values from the pasted data. In our case, we use the paste special feature to use Transposed.
Refer to the steps mentioned below to use the paste special feature on both your desktop and mobile/tablet:
- On your browser and navigate to Google Sheets.
- Open your document.
- From the sheets, select the data you wish to change the alignment for.
- If you’re a Windows user, use the Ctrl + C command to copy the data. Mac users can use Command + C to copy the selection.
- Right-click on a different cell and select Paste special.
- From the fly-out menu, click on Transposed.
- Open the Sheets application on your device to open your document.
- Drag the anchor point to select the table you want to convert.
- Tap on the selected value and select Copy.
- Tap on a new cell you want to paste the value, then tap on it again to display the menu. Select Paste special from the tray.
- Select Paste transposed.
In both instances, Google Sheets will automatically paste the converted value on your sheet. If you wish to delete the old data table, select them as you did before.
- On a desktop, right-click on the selected cell and choose from the Delete options.
- For mobile, tap on the selected table and select Clear.
Google Sheets has a dedicated function to convert the alignment of a table. If you like using formulas to get something done on spreadsheets, using the TRANSPOSE function is your method.
You can use this function across all devices while using Google Sheets. The steps mentioned below are the same for all devices:
- On an open document, select the cell you want to paste the converted table from.
- Enter the formula with the TRANSPOSE function in this format:
To pass the range as an argument, you can simply select the table you want to convert. Sheets will automatically enter the selected cell locations inside the parameters.
Issues That May Arise While Converting Alignment
Some issues can arise when you try to convert alignments in Google Sheets. If the methods we mentioned above don’t work for you, you must’ve run across an issue. Below, we have listed issues you can encounter while applying the mentioned methods.
Sheets displays the #REF! error when it does not locate the cell you’ve referred to. If you move or delete the table after you’ve used the formula with the TRANSPOSED function, you will encounter this issue.
Remember, the value you get from using the formula uses the passed cells as its source. If you clear the value, the formula will have no value to refer to using the TRANSPOSE function.
If you just used deleted the table, you can undo the action by hitting the Ctrl + Z key combination.
If you come across the #NAME? error message in Google Sheets, you need to understand that there has been a typing error in the formula. Asides from the typing error, you can also encounter the #NAME? error with incorrect syntax.
Select the cell with the #NAME? error to review the formula from the formula bar. Check for any typing error in the name of the function. Also, check if you’ve correctly used the syntax.
‘Transposed’ Not Available
Did you not see Transposed from the paste special menu? This may be because the sheet is open or more than a single tab on the device you’re currently using. Close the other tabs that have the sheet open and try again to use the paste special feature.
How to Change Alignment Between Two Sheets?
If the table you want to convert the rows into columns is on a different sheet, you can again use the TRANSPOSE function to switch the alignment. Although the formula’s process remains the same, you need to be careful when passing arguments inside the function.
Use the formula in this format:
Inside the parentheses, you first need to enter the sheet name and then add the exclamation sign (!) after the name. Then, enter the range the table is located in the sheet. Remember, the only symbol separating the sheet name and the range is the exclamation mark (!)