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

We’ll use a sample dataset with random numbers in one column and will then convert them to negative numbers in the other one by applying negative numbers in formulas.

Methods to Put a Negative Number


Method 1 – Applying the IF Function

Steps:

  • Select C5.
  • Insert the following formula in the selected cell.
=IF(B5>0,B5*-1,B5)

Methods to Put a Negative Number

  • Press the Enter button to get the result.

Methods to Put a Negative Number

  • Use the Fill Handle option to apply the formula to all cells in the column.

Methods to Put a Negative Number

  • You will get the final result similar to the below image.

Methods to Put a Negative Number

Read More: How to Add Negative Numbers in Excel


Method 2 – Use the ABS Function

Steps:

  • Select C5 and insert the following formula
    =ABS(B5)*-1

Methods to Put a Negative Number

  • Use the Fill Handle to apply this formula to all the cells.

Methods to Put a Negative Number

  • Here are the results.

Methods to Put a Negative Number

Read More: How to Show Negative Numbers in Excel


Method 3 – Utilizing Excel’s Paste Special Feature

Steps:

  • Paste the numbers you want to convert into the desired column.

Methods to Put a Negative Number

  • Insert -1 into any blank cell (in this case cell D5) and copy the cell.

Methods to Put a Negative Number

  • Select the numbers you want to convert.

Methods to Put a Negative Number

  • Right-click on the column and choose the option Paste Special.

Methods to Put a Negative Number

  • The Paste Special dialog box will open.
  • Select All in the Paste section and the Multiply option in the Operation section.
  • Press OK.

  • You will get the desired result.

Read More: [Fixed!] Excel Not Adding Negative Numbers Correctly


Method 4 – Applying VBA Code

Steps:

  • Press Alt + F11 to open the VBA window.
  • Go to Insert and select the Module option.

  • Insert the following 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

  • Save the file as an .xlsm and go back to the sheet.
  • Go to the Developer tab and use the Macros option to open the Macro tab.
  • Select the macro and a range from the worksheet and press the Run option.

  • Here are the results.

Read More: How to Make a Group of Cells Negative in Excel


Highlighting Negative Numbers

Steps:

  • Select the range of cells where you want to highlight negative numbers.

  • Go to the Home tab and choose the Conditional Formatting option.
  • Select the Less Than… option from the Highlight Cells Rules drop-down.

  • Put 0 in Format cells that are Less THAN, select the desired formatting from the drop-down, and press OK.

  • Here is the sample result.


Download the Practice Workbook


Related Articles


<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo