We often need to paste transposed values in Google sheets. But sometimes it doesn’t work because of some common problems. In this article, I’ll show 4 possible solutions when Paste Transpose is not working in Google Sheets with clear steps and images.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
What Is Paste Transpose in Google Sheets?
Paste Transpose is a tool in Google Sheets that can transpose any column into a row or vice versa. Paste Transpose can be done through 2 methods, using the Paste Special Command or the TRANSPOSE function. This function transposes any column to a row or any row to a column very easily.
4 Solutions When Paste Transpose Is Not Working in Google Sheets
We have our dataset below. Here we have some products in Column B and their prices in Column C. We want to transpose these columns into rows. But Paste Transpose is not working. Below you’ll find 4 solutions when Paste Transpose is not working in Google Sheets.
Solution 1: Remove Values from Pasting Range
If we have any value, text, or number in our pasting range then the TRANSPOSE function won’t work. We have to remove the value first.
- Firstly, we have applied the following formula in Cell B11–
- But it gives an error message as shown below because we have a value inserted in Cell E11.
- When we remove the value from Cell E11, the formula just works fine like below.
Solution 2: Delete Space from Pasting Range
Besides having any values we might have some spaces in our pasting range. Then also our formula won’t work. We have to remove that space. We’ll find the position of the space from the error message.
- First of all, we have put the formula in Cell B11–
- An error message occurs showing that we have some values (spaces) in Cell E11.
- When we click on Cell E11 we’ll see that there are spaces in it.
- After removing space, the function will give the desired result very fast.
Solution 3: Paste as Values after Using TRANSPOSE Function
Sometimes we need to remove the original dataset from where we transpose the values. But removing the original dataset will remove the transposed values also if we transpose values using the TRANSPOSE function. Let’s see how to solve that.
- In the beginning, we inserted the next formula in Cell B10–
- We are getting the values transposed but also the original dataset is intake.
- Now when we remove the original dataset the transposed values also get removed automatically as shown in the picture.
- To solve this issue after applying the formula copy the whole transposed values by pressing Ctrl+C.
- Next, select Cell B10 and give Right-Click on the mouse.
- After that go to Paste Special > Values Only.
- Finally, we’ll get our desired result.
- Now if we remove the original dataset our transposed values won’t get removed.
Solution 4: Transpose According to Available Cell Capacity
Google Sheets has some limitations. If your data has over 16384 rows then it won’t be transposed. Because then it will be greater than the maximum number of columns available. Besides, when you transpose your data, the cell size should be the same. If there are any merged cells in the pasting region then the values won’t get transposed. So always transpose according to the available cell capacity.
That’s all for now. Thank you for reading this article. In this article, I have discussed 4 possible solutions when Paste Transpose is not working in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.