Today's #TipTuesday is an Excel tip. There are some funky Paste Special options beyond the basics like Paste Values, Paste Formats etc. I find if I were to ask most Excel users what Paste Special is, the majority would answer "pasting values instead of formulas". While that's 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.
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 I'm 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 I 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.
