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.
***
3 Responses
For once, I was about to strongly disagree with you, because I am a habitual user of paste special – values. In fact, the keyboard shortcut has become part of my muscle memory.
Then I read the full article. Even then, I was a bit skeptical – maybe it worked only in Excel 2013 (whereas I use 2010).
Of course, I had to check in Excel 2010 and sure enough, the option you recommended is very much there. I was delighted to be proved wrong.
Thanks Doc!
Yes Jay. I am not surprised at your initial reaction. But when I write something, I take that into account 🙂
Just because it works does not mean it is the best way. In this case it is simple – Change from Alt E S V to Alt E S U
Hello Sir,
I have been a fan of all your wonderful articles and the efficiency that all these articles bring in my work is undeniable.
I just have a question that, no doubt what you mentioned in today’s article is a wonderful stuff but in that case why did Microsoft give that value option in first place? Definitely there is something that probably we (Myself) may not know about this function. Can you kindly highlight how better can we use paste special value function.
I know this is contradicting the title of the article but was bit curious to know some better ways/instances of using Paste Special Value function post reading this article.
Thanks Sir.