Excel: How to Paste All except formulas

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.

Base data for paste special. Multiple columns, containing text, date, number and calculation

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 …

Paste All except Formulas

Now notice the option right next to it.

Values and Source Formatting option. Character E is the shortcut key

Values and Source Formatting works like this: Everything is pasted, except the formulas.

Values and Source Formatting output. Cell formatting is also included.

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

  1. 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?

Queries | Comments | Suggestions | Wish list

%d bloggers like this: