Excel Tip: Quickly reverse the sign of a block of numbers

This week's tip is a nice simple one that I only discovered a few weeks back when a client needed to reverse the sign on a large block of entered data.

For those of you that might not get to the tip this week (believe it or not, Excel is not always at the top of everyone's priority list over the Christmas break!), I hope you have all had a great Christmas, and I wish you all a Happy and Productive 2014!

If you want to make 2014 even more productive, why not treat yourself to a year of Excel Email support for just £75 (or $112.50 if you prefer).

Festivities and shameless plug over! On with today's tip...

The client in question had a range of numbers in Excel entered as positive figures that to be consistent with other reports should have been negatives (it was the expenses section of a budget spreadsheet and all other group companies had set their budgets up with costs entered as negatives).

I was going to enter a formula in a blank area of the spreadsheet (e.g. =-A1, where the range to be reversed starts in cell A1) and copy this over a range the same size as the range in question. I could then copy this and paste as values over the original range. I then stopped myself and wondered if it could be done in one step. A quick look around in the Paste Special dialogue box confirmed it could.

When you select Paste, Paste Special you are presented with the following dialogue box:

It is the section entitled "Operation" that caught my eye. This takes the pasted number and applies the selected operation to that and the existing contents of the cell.

So all we need to do is:

• enter -1 in a blank cell
• copy this cell to the clipboard
• highlight the range of cells that we want to reverse
• click Paste Special
• Select Multiply under Operation and click OK

Nice and simple. It also has the added advantage that it will leave any formulae intact in the destination range (this would not have been the case in my first approach).

OK, back to the celebrations, see you in 2014!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".