Google Sheets – How to Paste Values Transposed

copy/pastegoogle sheets

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?

Best Answer

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

  1. Select the values you want to copy elsewhere
  2. Press Ctrl+C to copy
  3. Right click on the top left cell of the range you wish to paste into
  4. Choose Paste Special > Paste Transposed (or press e after hovering over paste special)
  5. The newly-pasted cells should be selected. Press Ctrl+C to copy them again in this new orientation
  6. Press Ctrl+Z to undo the pasting and formatting overwrite
  7. 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

  1. Select the top left destination cell
  2. Type =transpose(
  3. Select the range of cells you wish to copy and press enter
  4. Select the values that appeared from the formula
  5. Press Ctrl+C followed by Ctrl+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]
Related Topic