Excel Paste Special - Operations

Today’s #TipTuesday is an Excel tip. Did you know there are some funky Paste Special options beyond the basics like Paste Values, Paste Formats etc.? I find if you were to ask most Excel users what Paste Special is, the majority will answer “pasting values instead of formulas”. While that’s definitely true, there are also Paste Operations.

There are random circumstances where I’ve found this to be useful in a pinch and today was one of them. I was working on some Fixed Assets stuff, and my data had asset cost and accumulated amortization both as positive values. I wanted to do some Pivot Table analysis with it, to validate some Net Book Value info, without fussing with formulas. Paste Special Operations to the rescue!

How does it work?

First, here’s some sample data as an example. In my simple dataset here, I want to flip the AA values to negatives.

Asset listing with Cost, AA and NBV

What I want to specifically do is multiply each of these by -1, without doing it in another column and then copy/paste values overtop of the original. To do this with Paste Special Operations, I simply type -1 in any cell (it will not need to stay there when you’re done). Then, I copy that cell.

Now, I want to highlight where I’d like to “paste” that to multiply it to specific values. After highlighting my AA values, I opened up Paste Special and selected Multiply in the Operation section. Then, click OK.

That’s it! Well, the formatting leaves a bit to be desired when you paste this way, but here is what it looks like after I re-formatted my numbers the way I wanted.

Do I use this every day? No, but from time to time it’s handy to know how to do it.

comments powered by Disqus
Built with Hugo
Theme Stack designed by Jimmy