Negative numbers in Excel can be frustrating, especially when you’re trying to perform calculations. Fortunately, If you need to remove the negative sign from numbers in Excel, there’s a quick and easy way to remove negative signs from numbers Excel. Here’s how:
- Apply the ABS Function.
- Using Find and Replace Command.
- Reverse Negative Sine to Positive Using Paste Special Method.
- Using IF Function.
- Adding By Custom Formatting.
- By Flash Filling Cell
Applying those six methods, you can remove negative numbers in excel. Let’s see all methods and how to work.
Contents
1. Apply the ABS Function
To remove negative numbers in Excel, you can use the ABS function. This function returns the absolute value of a number, meaning the number without its sign. So, if you use ABS to remove negative numbers, any positive numbers will remain unchanged, while negative numbers will become positive.
Example:
Here we have a list of negative and positive numbers in B4:B10. Here we apply the ABS function formula for cell C5. So, our formula format is =ABS(B5).
Here you can see the result. B5 has a negative number of -7. But after applying the ABS function, we can easily remove the negative sign from the number in excel. Now drag on below to use the formula for all cells.
2. Using Find and Replace Command
Excel’s Find and Replace command can be used to remove negative numbers from a set. This is useful when you want to concentrate on positive values within a data set or when you need weak data points removed that could skew your results.
Example:
Here we choose the same excel sheet for the example. You can see there have three negative numbers.
Go to the Find & Select tab and click on the Replace section.
After opening the Find and Replace window, select the Replace section, put a minus sign (-) in the Find What input box, and leave the Replace With input box empty. Click the Replace All button.
As Per our data, here is a message from Microsoft Excel- All done. We made three replacements. Just click the OK button.
And see the result. All negative signs are removed from our sheet.
3. Reverse Negative Sine to Positive Using Paste Special Method
You can use the Paste Special method to reverse a negative sine to a positive value. This is a quick and easy way to change the sign of a number without having to re-enter the data.
Example:
Here is the sheet, and we have to put -1 in any cell on our sheet. In cell C5, we put -1.
Press Ctrl+C to copy the -1 or C5 and select B5:B10 by dragging our mouse.
Right-click on any selected cells and click Paste Special.
Here open the Paste Special window. In the Paste section, select the Values, and in the Operation section, select Multiply. Next click Ok.
We will convert all the negative sine values to positive values.
4. Using IF Function
If you need to remove negative numbers from your data set in Excel, you can do so by using the IF function. This function will allow you to specify what should happen to values that meet specific criteria – in this case, values that are less than zero.
Example:
Here we apply the IF Function in cell C5. Our formula is- logical_test = B5<0, [value_if_true] = -B5, and [value_if_false] = B5.
Check if B5 is less than zero or not. If B5 is less than zero, that converts positive.
B5 is not less than zero, then
Keep the value as it.
5. Adding By Custom Formatting
If you’re working with Excel data containing negative numbers, you may want to remove them for clarity. Luckily, this is easy to do with a bit of custom formatting.
Example:
In our old sheet, select the cells B5:B10.
To open the Format Cells window, press Ctrl+1. On the Number section, select the Custom and also choose the type of format #,###;#,###. Click OK.
Now you can see the result without any negative signs.
6. By Flash Filling Cell
Using the Flash Fill feature, you can quickly remove negative numbers from a range of cells.
Example:
Open our sheet.
Put the positive number 7(C5) of cell B5.
Now select cell C6 and press Ctrl+E. And drag down to set the format of other cells. Here show a small icon on the right and click on the Accept Suggestions. Now see the numbers without negative signs.
We now know how to remove negative Excel signs. These methods should encourage you to use them in your calculations with more confidence.
