1

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?

Improve this question
0

2 Answers 2

Reset to default
0

The simple and effective way is to paste transposed, then click on the paste format icon under the pasted section and select values.

Improve this answer
-1

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]
Improve this answer
4
  • 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
    – Manuel Ramirez
    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
    – Manuel Ramirez
    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.
    – kviLL
    Oct 1, 2020 at 14:44
  • Right, I missread.
    – Manuel Ramirez
    Oct 5, 2020 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.