Changing numbers Sign in Excel – From Negative to Positive and Vice versa

Another question to answer and very interesting one indeed. Again this question came from our vibrant facebook community.  The requirement is to change number signs that is change negative numbers to positive and positive to negatives.

Although this is hardly the case in many situations that you really want an additive inverse for each and every value as most of the time users want all the values to be positive or all the values to be zero but in real life all sorts of things happen. Therefore, I will discuss one easy way to achieve this quickly i.e. converting positives to negatives and vice versa and after that I will discuss several other ways to achieve almost the same requirement that you can apply in your daily Excel adventures.

Paste Special – Another reason why it is so special

Don’t think you can change signs of numbers just by pasting the special way? Well let me help you in doing it.

Step 1: Type -1 in any empty cell anywhere in the worksheet or workbook. Press Enter and copy that cell by clicking the copy button under home tab or using shortcut key Ctrl+C or right clicking that cell and selecting copy.

Step 2: Now go to that range which you want to invert from positive to negative or negative to positive and select it using mouse or keyboard.

Step 3: Go to Home tab > Clipboard group and click the drop down arrow under Paste button and select Paste Special. You can also use shortcut key Alt+Ctrl+V

Step 4: From the dialogue box under the paste option select “Value” radio button and under operation select “Multiply”. If your select has blanks then you can select “Skip blanks” to keep them empty and click OK.

BINGO!!! Now your numbers are inverted i.e. positives are now negatives and negatives are now positives. Told ya! There is a reason why it is special!

Find and Replace negatives

First things first, this trick will help you convert negative numbers to positive but not the otherwise. What we are basically planning to do is to find the negative sign in a range and replace it with nothing and thus the number will become positive. To do this following steps will help:

Step 1: Select the data or range using mouse or keyboard

Step 2: Go to Home tab > Editing group > Find & Select drop down > Replace. You can also use a shortcut Ctrl+H to invoke replace dialogue. [Remember Ctrl+F invokes Find dialogue]

Step 3: Once replace dialogue appears in the ‘find what’ field type dash (-) or negative from the keyboard and leave the ‘replace with’ field empty. Click Replace All button and it will instantly remove the negatives from every number and thus making them positive.

As a result you will have all the values positive.

Make negatives positive using Absolute (ABS) function

If your data is arranged in a row or a column then with the help of a vacant column and ABSOLUTE function we can convert the negative values to positive. Basically ABSOLUTE function does the same which it does in mathematics i.e. it always return a number as positive and ignores the sign. Therefore, if you feed a negative number to it then outcome will be positive.

For example your data runs down in column C from Row 1 to 1,000 comprising of negatives, positives and zeroes etc. In column D put this formula and press Enter:

=ABS(C1)

Double click the fill handle and the same formula will populate down to 1,000 rows automatically converting the negatives to positives. Positives will remain positive.

Bonus Tip – Common Sense is Excellent!

We learnt that ABS function help us convert negative numbers to positive. But it does not convert positives to negatives. Using the same example as we mentioned in ABS case above, instead of using a dedicated Excel function in cell D1 put the following and press Enter key on the keyboard:

=-1*C1

Double click the fill handle and the same formula will populate down the whole range. This formula actually conducts the same operation as paste special we learnt above and this will get you negative numbers converted to positives and positives being converted to negatives.

Custom formatting – “Cloaking”

Custom formatting is just one amazing feature Excel has. This is literally a magician’s wardrobe which you can use to change the look of data. But remember, this is connected with the formatting of data and it only changes its appearance and does not affect it in reality.

In all of the above examples we changed the data itself and the effect is permanent. But sometimes you only want to show negatives as positives and not to change them literally as positives. The reason is that you later want to use data for calculation so you want signs to stay intact but don’t want dashes and negative signs to pollute the data.

If this is the case that you just want to change the appearance then following examples can help you learn how to go around it using Excel’s custom formatting.

Making all numbers appear positive

Step 1: Select the data and hit Ctrl+1 to bring the number formatting dialogue box up.

Step 2: From the list at left select ‘custom’ and in the type field insert:

0;0;0

Click OK button. This will keep the positive numbers as positive however, strip the negative sign off from negative numbers. Remember this will only show negatives as positive and will not really convert them to positives. Highlight any number that you remember as negative and you will see that in formula bar it still has negative sign with it.

Making all numbers appear negative

Step 1: Select the data and invoke number formatting dialogue box using a shortcut key Ctrl+1

Step 2: Select custom from the list at left and in the type field insert:

-0;-0;0

Click OK button and now all the numbers will appear with a negative sign irrespective of the number itself whether it is positive or negative in nature.

Making positive to negative and negative to positive

Step 1: Select the data and hit shortcut Ctrl+1 to bring formatting dialogue box up.

Step 2: Select custom from the list at left of the dialogue box and in the type field insert:

-0;0;0

Click OK button and now positives will appear as negative and negatives will appear as positive.