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.
How to Put a Negative Number in Excel Formula: 4 Easy Methods
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.
=IF(B5>0,B5*-1,B5)
- 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:Â How to Add Negative Numbers in Excel
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 Show Negative Numbers in Excel
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: [Fixed!] Excel Not Adding Negative Numbers Correctly
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 Make a Group of Cells Negative in Excel
Highlighting Negative Numbers
We can format negative numbers in Excel by making negative numbers red or add brackets to them. However, 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.
Download Practice Workbook
You can download the practice workbook from here.
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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
