How to Put a Negative Number in Excel Formula (4 Easy Methods)

This tutorial will demonstrate the methods on how to put a negative number in an excel formula. Negative numbers are very important in case of temperatures below freezing point, penalties in a game, credit of money or negative scoring in the exam, etc. So, it is very important to learn how to put a negative number in an excel formula.

Download Practice Workbook

You can download the practice workbook from here.

4 Easy Methods to Put a Negative Number in Excel Formula

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Numbered in column B and Converted Numbers in Column C. We will use this dataset in the below methods. If you follow the methods correctly then you should learn how to put a negative number in an excel formula on your own. The methods are:

1. Applying IF Function

Our aim is to put a negative number in an excel formula by using the IF Function. The steps of this method are described below:

Steps:

• First, select any blank cell (in this case C5 cell).
• Then, insert the following formula in the selected cell.

• After pressing the Enter button, you will get the desired result.

• Next, use the Fill Handle option to apply the same formula to all cells.

• Finally, you will get the final result similar to the below image.

Read More: Excel Formula to Return Zero If Negative Value is Found

2. Use of ABS Function

We can put a negative number in an excel formula with the help of the ABS Function. The steps of this method are:

Steps:

• At first, select any blank cell (in this case C5 cell) and insert the following formula
  
  =ABS(B5)*-1
  

• Second, use the Fill Handle to apply this formula to all the cells.

• Last, you will get results like the below image.

Read More: How to Add Negative Numbers in Excel (4 Easy Ways)

3. Utilizing Excel’s Paste Special Feature

Another method to put a negative number in an excel formula is by utilizing the Paste Special Feature. The steps of this method are described below.

Steps:

• To begin with, paste the numbers you want to convert into the desired column.

• In addition, insert (-1) into any blank cell (in this case cell D5) and copy the cell.

• Furthermore, select the numbers you want to convert.

• Next, right-click on the column and choose the option Paste Special.

• After that, the Paste Special dialog box will open on the screen.
• Then, select All in the Paste section, the Multiply option in the Operation section, and press OK.

• Finally, you will get the desired result.

Read More: How to Show Negative Numbers in Excel (4 Quick Tricks)

4. Applying VBA Code

Applying VBA code is another method for putting a negative number in an excel formula. The steps of this method are.

Steps:

• To begin with, press Alt + F11 to open the VBA window.
• Then, go to the Insert option and select the Module option.

• In addition, insert the VBA code.

Sub Put_Negative_Number()
Dim wsheet As Worksheet
Dim rnge As Range
Dim rslt As Range
Set wsheet = Application.ActiveSheet
Set rnge = Application.Selection
For Each cell In rnge
If cell.Value > 0 Then
cell.Value = cell.Value * -1
End If
Next
End Sub

• Furthermore, go to the Developer option and use the Macros option to open the Macro tab.
• Next, select the macro and range from the worksheet and press the Run option.

• Finally, you will get results like the below image.

Read More: How to Count Negative Numbers in Excel (3 Easy Ways)

Highlighting Negative Numbers

Now, we want to highlight the negative numbers in our worksheet to identify the negative numbers briefly. This can be done by following the below steps.

Steps:

• At first, select the range of the cells you want to highlight negative numbers.

• Second, go to the Home tab and choose the Conditional Formatting option.
• Third, select the Less Than… option from the Highlight Cells Rules option.

• Fourth, put 0 in the Format cells that are Less THAN cells and also select the desired color in the Less Than dialog box and press OK.

• Last, you will get the result like the below image.

Things to Remember

• In the case of using the first two methods at first, you have listed the numbers you want to convert and then use the formulas accordingly.
• When using the Paste Special method, make sure to copy (-1) and select the range after that. Otherwise, you won’t get the desired result.
• If you have used the VBA code, make sure to save it in the Enabled Macro book. Otherwise, the code won’t run.

Conclusion

Henceforth, follow the above-described methods. Thus, you will learn how to put a negative number in an excel formula. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Add Brackets to Negative Numbers in Excel (3 Easy Ways)
• How to Change Positive Numbers to Negative in Excel (6 Ways)
• How to Apply Formula for Positive and Negative Numbers in Excel
• Excel Negative Numbers in Brackets and Red (2 Examples)
• [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)
• How to Move Negative Sign at End to Left of a Number in Excel