In the earlier article we saw that Excel Paste Special – Values and Number Formatting is the recommended option when you want to paste all except formulas. But there is another option which can be even better in some cases. It even comes with a handy keyboard shortcut in case you need it frequently. Here’s how you do it (Estimated reading time: 3 minutes).
The Need: Copy everything including formatting but not the formula
This is the base data. The last column contains a formula. Received on and Amount columns contain formatting.
Now I want to paste this entire thing – including the formatting (light and dark blue bands as well) to the destination. The only thing I don’t want copied are the formulas – those should be pasted as values. This option does not exist in the Paste Special dialog. That is why most of us do not know about it.
The Solution: Values and Source Formatting
Copy as usual, go to the destination and just do a default paste (CTRL V). Don’t worry that the output is not what you wanted. Look at the small Paste Options Icon which appears and click on it. Lots of options will be shown – it is worth knowing about all these options. Very useful in day to day work. Let us see what happens when we choose Values and Number Formatting …
Now notice the option right next to it.
Values and Source Formatting works like this: Everything is pasted, except the formulas.
Paste All Except Formulas Keyboard Shortcut: CTRL (pause) E
Here is a faster way of choosing this option. Paste as usual. Press and Release the CTRL key. Now the Paste Options menu appears. Now type the character E. That’s it.
Do try it and let me know your feedbacks. Thank you.
Dr. Nitin Paranjape
***
2 Responses
Thank you very much Doc, for a really useful productivity hack! As usual, you’re right about bad habits – I had never bothered to explore the additional options that pop up after pasting. In fact, I found it a nuisance, so I spent 20 minutes to find a way to actually DISABLE the paste dialog popup! And after reading your article, I had to spend another 10 minutes remembering what I had done and enable it again.
Now here’s my tough ask… I want to paste values with source formatting (as described above), PLUS retain the column width. Is this possible?
I tried pressing the ctrl key a third time – the option is still available, but if I press “W”, then the source formatting goes away. Alternatively, if I press “E” first and then “W”, then the width goes away.
Any solution?
Here is a better solution: Alt E S W – paste widths first and then Alt E S U to paste values and number formatting. Works fine.