Paste Special Operations

I'm going to take a brief interlude from the date series. I am in the middle of moving, and want to be able to spend the appropriate time to put together the next two series. Today I'm going to share a quick tip that most people should find some use for!

If you go to Edit -> Paste Special, you
will notice there are some lesser used options at the bottom "Operations".

Lets say you wanted to quickly transform this data into thousands.

In an used cell, we'll type the number 1000. Then click copy (ctrl + c...stop using that mouse).

Next, highlight the numbers you want to divide by 1000.

Next, go to Edit - Paste Special (Alt, E, S) The click on divide ( press I), and select okay (press enter). (All together now, you press Alt, E, S, I, Enter...that's a LOT easier than moving your mouse all over!)

Your numbers will now be divided by 1000!

Oh look at that - That's not expected (2009 is divided by 1m!)...Lets look at what happened.

The first number divided by 1000 as expected. HOWEVER (THIS IS IMPORTANT). If you have a formula, it will NOT hardcode your formula, but simply add the /1000 to the end. So in my example, I took the first number divided by 1000, then each time, divided it by 1000 AGAIN. Be careful using this function when you're working with formulas and not values.

Make sure you keep an eye on what you are trying to modify so you don't get unexpected results.

Multiply, Add, and Subtract all work in the same manner. Play around with it and see what you can do.

(Tip: A great way to convert "text" numbers into real numbers is to multiply the text by 1 with this function. They will all instantly revert to their appropriate numbers.

Enjoy