In the earlier article we saw that Excel Paste Special – Values and Number Formatting is the recommended option. But there is another option which can be even better in some cases.
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.
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.