Let’s discuss ways to convert values from positives to negatives and vice versa by using Paste Special, Find and Replace, ABS Function and Custom Formatting.
Let’s begin with Paste Special technique.
1. Paste Special Technique
The function name itself says that it is special. The reason why it is so special is that it can be used for copying and pasting formats, values, formulas… and the list goes on.
Have you ever thought of changing signs of numbers just by pasting it the special way? Let me show you how to do it.
Type -1 in an empty cell in the worksheet or workbook. Copy that cell either by clicking the copy button under the home tab or using shortcut key Ctrl+C or right clicking that cell and selecting copy, whatever is convenient.
Select the range that which you want to change from positive to negative or negative to positive. You can either do it using mouse or keyboard.
Go to Clipboard group in Home tab. Then click the drop down arrow under Paste button. Select Paste Special.
You can also use shortcut key Alt+Ctrl+V for doing this. The below dialog box will appear:
As it is visible in the above dialogue box under the paste option select “All” and under operation select “Multiply”.
If the selected range has blanks then we can select “Skip blanks” to keep them empty. Press enter or click OK.
Where did all the negative numbers go?
Now all our numbers are inverted. Positives are now negatives and vice versa. There is a reason why Paste Special is special!
2. Find and Replace negatives
Using Find and Replace will only help us to convert negative numbers to positive. We cannot turn positive number to negative.
Basically, we will find the negative sign in a range and replace it with nothing and thus the number will become positive.
For changing negative data to positive, select the data or range using mouse or keyboard.
Go to Editing group in Home tab. Select Replace or you can use a shortcut Ctrl+H to invoke replace dialogue.
In ‘find what’ field and enter (-) negative sign. Leave the ‘replace with’ field totally empty. Click Replace All button.
Doing this will instantly remove the negatives, making them positive.
3. Changing negatives to positive using Absolute (ABS) function
With the help of Absolute (ABS) function, we can easily convert the negative values to positive. We need an extra column or row for using this function.
ABSOLUTE function follows the mathematics rule “always return a number as positive and ignores the sign”.
Therefore, if we feed a negative number to the cell which is linked to ABS function then the outcome will be positive.
For example, we have budget difference data in column C consisting of negatives, positives and zeroes etc. Put =ABS(C2) formula in column D as shown below.
Using the fill handle same formula will apply to all cells below, converting the negatives to positives. Positives and zeros remain unchanged.
4. Reverse of ABS function?
As we know now that that ABS function helps to convert negative numbers to positive, but it doesn’t convert positives to negatives.
Take the same example of data as used above, instead of using ABS function in cell D2 write = – 1*C1 manually and press Enter. See image below:
Using the fill handle same formula will apply to all cells below changing negative numbers to positives and vice versa. This formula actually conducts operation similar to paste special.
5. Custom formatting
Custom formatting is another amazing feature of Excel. It is just like a magician’s wardrobe. You can use it to change the look of data.
One thing should be kept in mind that custom formatting is connected with the formatting of data. It only changes appearance and does not affect it in reality.
From the above options 1 to 4, we have applied changes to the entire data and the effect of it is permanent.
Sometimes we need to show only negatives values as positives values and not change them in reality.
Custom formatting is used in cases where we want to use data for further calculation. In this scenario, we will need the signs to stay intact and want negative signs to be invisible on the screen.
Changing of negative to positive and positive to negative cannot be done together. There is a different way for each operation.
Display all numbers positive using Custom Formatting
Select the data using a mouse or a keyboard and press Ctrl+1 to open the number formatting dialogue box.
Select ‘custom’ from the list on the left and put 0;0;0 format value in the type field.
Apply this custom format to data by clicking OK button. This will keep all the positive number as positive and will hide the negative sign off from negative numbers.
Keep in mind one thing that this will only display negatives value as positive values and will not really convert them to positives as we did in earlier cases.
Display all numbers negative using Custom Formatting
Select the data using a mouse or a keyboard and press Ctrl+1 to open the number formatting dialogue box.
Select ‘custom’ from the list on the left and put -0;-0;0 format value in the type field.
Apply this custom format to data by clicking OK button. This will change all the positive number to negative excluding O.
Again the same thing as above that this will only display positive value as negative values and will not really convert them to negatives.
If you have any query please comment below, and I will be happy to help you.
