Yes. I know this is a commonly used feature. In spite of that, I am saying that you should never use Paste Special Values in Excel. Read on to find out why.
Base data – before copy paste
Copy and Paste Special Values gives you unacceptable output
Many things happened. Titles are not bold, dates look like numbers, number decimal formatting got disturbed. The title issue is not that important. Other two are.
This is not a surprise to most of us. In fact, we know that this is going to happen and we are ready to take extra effort to repair all these issues manually.
The solution – Values with Number Formatting option
The Paste Special dialog has Values with Number Formatting option exactly for this purpose – to eliminate the need to manually repair lost formatting. That’s all. Just notice this option and start using it next time onwards. It does take some conscious effort to NOT use the Value option because we are so habituated to it.
In short change the keyboard shortcut from ALT E S V to ALT E S U
Even better: Right click and choose the correct option
Right click at the destination and choose the Values with Number Formatting option. In fact we get another extra option in the right click menu – Values with All Formatting.
While you are at it, check all the options in this menu. All of them are useful depending upon the paste context. Learn them and inculcate the habit of using them on a daily basis.
There is more: Ctrl Pause A
You must have noticed that the Paste Option icon which appears after pasting can also be invoked from keyboard by pressing the CTRL key. Just press and release the key. Do NOT keep it pressed. Now the options shown above appear. But wait, each option has a shortcut key which is shown in brackets. In this case the key is A. Of course it works.
But there is a catch. Don’t you remember another usage for CTRL A? Of course, it is the select region / select all key.
Now the problem is, CTRL A has two meanings. When you press it, which one will work? Sounds tricky… but has a simple answer. If you press CTRL and A together instantly, select all will happen. If you press CTRL and release it, pause for a second and then press A, the Values and Number formatting option will work. Why? Because only after you press and release CTRL key, the paste option menu appears. Try it out couple of times and you will get used to it.
Imagine the amount of effort Microsoft has put in to save even the smallest amount of inconvenience for us… This is called User Focus.