Paste Special- convert negatives to positives

You can use Paste Special to change numbers from positive to negative or to add, for example 10% to all numbers.

To follow along go to 6.3

In this case we are going to use Paste Special to repeat a calculation on all highlighted cells. Below we want all the Cost of Sales numbers to be negative numbers. To achieve this we:

• Type -1 in any cell (you will eventually delete so any blank cell)
• Copy the cell with -1 in it
• Highlight the cells you want to change to negative
• Choose Paste Special
• Choose the multiply option.

When you click OK, all the numbers will change to negative. You will also notice that the format of the cells change to the format of the copied cell.

What Excel has done is

• take the -1,
• gone to cell C6 and put *-1 at the end of the number which calculates to -250.
• It then takes -1
• and puts *-1 at the end of cell C7 etc.

If you don’t want the formats to change you can do the paste special but tell the Paste to be ‘Values only’ as shown below.

You can use this technique to:

• Add 10% to all the numbers (put 1.1 in a cell and Paste Value Multiply)
• Remove 14% VAT from numbers (put 1.14 in a cell and Paste Value Divide)