Written by Jane Hames
Turn a positive number into a negative in Excel using Paste Special
During Excel training last week, I was asked how to turn a positive into a negative in Excel. The Excel user was calculating balances but the expenses came in form Sage as positive values, whereas they needed to be negative values.
The ABS function can be used to turn a negative into a positive (see how to do that here). To turn a positive into a negative you can use Paste Special. This is what we did:
- Starting with a sheet laid out a bit like this example, type -1 (in yellow here) somewhere on the sheet. This -1 will be used to multiply our positive numbers by minus 1.
- Highlight the cell with -1 in it.
- Select copy.
- Highlight the cells to be changed into negative values. (You should still have the dotted outline showing around the -1 cell to indicate that this cell has been copied.)
- Select Paste Special from the arrow on the Paste button on the Home tab.
- From the Paste Special box, select Multiply, then click on OK.
- Your positive numbers are now negative.
- I finished this off by calculating the sum of my incomings and outgoings to give me the balance.
Excel training is the most commonly requested subject at Glide Training. We have helped hundreds of people to become more efficient and to enjoy their Excel use.
