Every so often, I need to reverse the sign of a range of numbers. This might be required, for example, when a downloaded trial balance shows the P&L account values as the negative of their normal selves because credits have been represented as negatives.
If the number of cells is very small then I might simply edit each cell individually and add or remove a minus sign. Of course, this does not scale well to handling alot of numbers. I really wouldn’t want to edit hundreds of cells individually. Fortunately, I found a very simple and quick way to make the change all at once and here it is:
Find a blank cell anywhere and enter into it the value of -1- Copy that cell to the clipboard
- Select the range of cells having values that need to be reversed
- Choose Edit | Paste Special. In the Paste Special dialog box, select Paste Values and Operation Multiply before clicking OK.
- Delete the cell containing the -1.
That does the trick! A nice thing about the Multiply operation of Paste Special is that it is non-destructive on formulae: a target cell containing a formula is not replaced by a value, instead the formula is extended with the required factor. So, a cell originally containing “=SUM(C2:C16)” results in “=(SUM(C2:C16))*-1” after the Paste Special - Multiply.
This is a cracking tip, although I much prefer the paste special subtract, twice approach!
Hi Alastair,
Thanks for the alternative. It’s frequently the case that there is more than one way of doing things and I’m sure each person chooses the method that suits them best. Which reminds me, it can be very worthwhile watching someone else using a PC to see some alternative styles of use. I’ve learnt some great tips that way.
Have a look at a free add-in “ASAP Utilities” (www.asap-utilities.com). This adds a new menu item to Excel which has a very wide range of additional functionality, some of which you may find very useful. In particular with reference to this tip, you can apply a formula to selected cells (not necessarily a continuous range) without having to enter the formula such as “-1″ into the spreadsheet. If all you want to do is change the sign on a range of numbers (not formulae), either from positive to negative or vice versa, there is a special menu item for this.
The add-in is worth a look for all sorts of other reasons. I have often found it helps me achieve something that Excel cannot do in a straightforward way..
Thanks for the lead, Roger. I’ve never really bothered with utilities Add-Ins. I think that’s because they tend to look like a list of one-trick pony’s and I don’t like clutter. If I’ve a laborious task to do then I tend to quickly capture what I need in VBA. However, asap-utilities’ website inspires some confidence, so perhaps I’ll give their Add-In a go.
Jim