How to convert positive number into negative in excel - Awsome trick

Convert positive number into negative in excel


This tutorial is about converting positive numbers to negative and vise versa without using any tool. Also you can total negative number or positive number when they are all together in on row.

Let’s start tutorial -


Below you can find negative numbers as well as positive in which we want to convert positive numbers into negative and negative into positive. Best and easy way to convert them by using Paste Special. Don’t you believe it ?? Let’s convert them now -

You just want to write -1 in any blank cell. In our example I have written -1 in cell E3. Then copy that cell which contain -1 and Select range of numbers where you want to convert numbers to positive. In our example it is C3:C12.

Then Go to Paste Special by right clicking or go to Paste option from Home tab. From Paste Special window you have to choose option Multiply from Operation menu  to Convert positive number into negative. See below screenshot - 

Now hit OK. You can find number sign have been converted to opposite site. Means Positive numbers are now negative and negative numbers are in positive.

Adding Positive numbers and Negative numbers separately

Let’s see first total of positive numbers. Positive numbers are those which are greater than ZERO. So we can use SUMIF formula to add number which are greater than zero. I have use same example and write formula in Cell C15. We need 3 values in SUMIF formula

1. Range – Select range in which you want to add positive number. Here C3:C13
2. Criteria – Criteria of positive number is greater than zero. Means “>0”. Do not forget to write  criteria in double inverted commas.
3. Sum Range should be same as Range. C3:C13.

So our formula is  “=SUMIF(C3:C13,">0",C3:C13)” and the answer is 35.

Hope you understand the trick now you can easily add up negative value by changing just greater than sign to less than sign.


Tutorial Video - 

Thank you !! Enjoy Excelling !! Have a Nice Day !!