Due to the existence of various software systems, you often need to get numbers to match each others signs. You will therefore need to know the Excel formula to change negative to positive (and vica versa) in spreadsheets.
Table of contents
Simple formula to change signs
If you need to use a formula, you can create a new column or row and simply link to the cells with a – in front of the reference e.g. =–A5. If the number in cell A5 is positive it will change to negative and if it is negative it will change to positive.
NOTE: We recommend creating a separate column/ row as you never know when you need to go back to the raw data for whatever reason.
Change ONLY the negatives to positives
Perhaps your situation is different. You want to ONLY change the negative numbers to positive numbers and the positive numbers must stay the same.
First option would be an IF function.
Something like
=IF(A5<0, –A5, A5) (to learn about IF look at the free Excel Fundamentals Course).
You could also use the ABS function. This function removes the sign (and hence makes all numbers positive)
So the formula
=ABS(A5)
will convert a negative number to positive, and leave a positive number alone.
Switch signs on numbers with a tool (instead of a formula)
Sometimes you may just want to quickly change negative to positive numbers. We recommend you keep the original raw data so the ideas above are best, but perhaps this is just a once off and you need to do it quickly and in place with no new columns.
You have probably used the Paste Special function in Excel, but did you know that you can use it to overwrite numbers with themselves but with the opposite sign?
Below we want to change the red block to the opposite sign.
To do this you:
- Type -1 into any empty cell (E7 in this case)
- Copy it
- Highlight the cells you want to change (E51 to J51)
- Click on Paste Special
- Choose the ‘Multiply’ option
- Click OK
What you are telling Excel is to take the -1 and multiple it into every cell you have highlighted.
Different formula for positive and negative numbers (YouTube)
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.