IS there a way to paste transposed but also just values? I want to paste values only, not formulas, keep the destination format and also transpose the data pasted. Is there a way to do this?
2 Answers
The simple and effective way is to paste transposed, then click on the paste format icon under the pasted section and select values.
I don't know of a simple button you can press to paste transposed values only, but here's a couple workarounds I found. In general, it depends on why you are seeking to paste values only. If you're doing this to avoid a formula recalculating (particularly something like =randarray()
) your options are more limited than if you're doing it to avoid copying cell borders, text formatting, colors, etc.
If you are pasting fixed values with custom formatting you don't want to copy
- Select the values you want to copy elsewhere
- Press
Ctrl+C
to copy - Right click on the top left cell of the range you wish to paste into
- Choose
Paste Special > Paste Transposed
(or presse
after hovering over paste special) - The newly-pasted cells should be selected. Press
Ctrl+C
to copy them again in this new orientation - Press
Ctrl+Z
to undo the pasting and formatting overwrite - Press
Ctrl+Shift+V
to paste the transposed cells in the clipboard as values only
If you are trying to reposition data calculated by formula(s) as transposed values only
- Select the top left destination cell
- Type
=transpose(
- Select the range of cells you wish to copy and press
enter
- Select the values that appeared from the formula
- Press
Ctrl+C
followed byCtrl+Shift+V
to overwrite the formula with values only
Admittedly neither of these solutions are great and you might be aware of them. If you're just looking for something fast, maybe the following key sequence based on the first solution, which could be automated for even better results: (Assuming the original range is copied to clipboard and destination cell selected, here's the shortest key sequence I found)
[alt]+[/] [ste tr][enter] [ctrl]+[c] [ctrl]+[z] [ctrl]+[shift]+[v]
or to do it in reverse order (values only, then transpose)
[ctrl]+[shift]+[v] [ctrl]+[c] [ctrl]+[z] [alt]+[/] [ste tr][enter]
-
Shame, so there is no quick way as in excel where you chose tranposed, skip blank, values, etc. =transpose() + copy-paste.values should do the trick. Thanks Jul 22, 2020 at 14:51
-
The respositioning suggestion won't work. When you copy a formula in another location the formulas point to different cells and the results will fail unless all cells are locked in all the formulas. Not very useful answer because if you don't know this you wouldn't know why the solution gaven by an expert is not working Sep 30, 2020 at 12:50
-
@ManuelRamirez try following the steps exactly and you'll see it does work. It never tells you to copy a formula from one place to another, actually. You simply use the
=transpose()
function at the destination cell (with the source cell range in the input of the function), and then replace the output with values only to remove the transpose function and leave the data. If you don't know what you're doing you should be following the directions "given by an expert" exactly, not copy and pasting a formula from one place to another.– kviLLOct 1, 2020 at 14:44 -