One thing should be kept in mind that custom formatting is connected with the formatting of data. You can use it to change the look of data. Custom formattingĬustom formatting is another amazing feature of Excel. This formula actually conducts operation similar to paste special. Using the fill handle same formula will apply to all cells below changing negative numbers to positives and vice versa. Take the same example of data as used above, instead of using ABS function in cell D2 write = – 1*C1 manually and press Enter. Reverse of ABS function?Īs we know now that that ABS function helps to convert negative numbers to positive, but it doesn’t convert positives to negatives. Using the fill handle same formula will apply to all cells below, converting the negatives to positives. Put =ABS(C2) formula in column D as shown below. Therefore, if we feed a negative number to the cell which is linked to ABS function then the outcome will be positive.įor example, we have budget difference data in column C consisting of negatives, positives and zeroes etc. We need an extra column or row for using this function.ĪBSOLUTE function follows the mathematics rule “always return a number as positive and ignores the sign”. With the help of Absolute (ABS) function, we can easily convert the negative values to positive. Changing negatives to positive using Absolute (ABS) function Click Replace All button.ĭoing this will instantly remove the negatives, making them positive. Leave the ‘replace with’ field totally empty. In ‘find what’ field and enter (-) negative sign. Select Replace or you can use a shortcut Ctrl+H to invoke replace dialogue. We cannot turn positive number to negative.īasically, we will find the negative sign in a range and replace it with nothing and thus the number will become positive.įor changing negative data to positive, select the data or range using mouse or keyboard. Using Find and Replace will only help us to convert negative numbers to positive. There is a reason why Paste Special is special! 2. Positives are now negatives and vice versa. If the selected range has blanks then we can select “Skip blanks” to keep them empty.
The below dialog box will appear:Īs it is visible in the above dialogue box under the paste option select “All” and under operation select “Multiply”. You can also use shortcut key Alt+Ctrl+V for doing this. Then click the drop down arrow under Paste button. You can either do it using mouse or keyboard. Select the range that which you want to change from positive to negative or negative to positive. 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. Type -1 in an empty cell in the worksheet or workbook.
Have you ever thought of changing signs of numbers just by pasting it the special way? Let me show you how to do it. 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. The function name itself says that it is special. Let’s begin with Paste Special technique.