Excel Convert Positive Number To Negative And Negative Number To Positive

In this post we will see how we could invert number in a cell in excel from positive to negative and from negative to positive.

We will use a small excel vba macro code to convert numbers from positive to negative and negative to positive.

Below in column A we could see there are some positive and negative numbers and the final output that we want is in column B i.e. positive number is converted to negative and vice versa.

inverting cell value in excel

Step 1




Press Alt+F11 keyboard shortcut to open VB editor in excel or you can click on the VB editor icon in the developer tab in excel.

changing positive numbers to negative and negative numbers to positive in excel

Step 2

Double click on “ThisWorkbook ” to open the module where we will write the VBA code to convert numbers from positive to negative and vice versa.

opening vb editor to write the code

Step 3

In this VB editor paste the code below, this code, the code below has three parts , first we have defined the range in which we have the numbers  that we want to invert and the second part is looping through each cell in that range and at the last we are checking the value whether they are positive or negative and accordingly changing the cell value from positive to negative and negative to positive.

I have defined the range here as A1:A7, since we have the data from A1 to A7, you can define the range as per you requirement.




Sub InvertNumbers()

Set myrange = Range(“A1:A7”) ‘Set range here

For Each cell In myrange

   If cell.Value > 0 Then

              cell.Value = -cell.Value

                  Else

              cell.Value = -cell.Value

   End If

    Next cell

End Sub

code to change the sign of numbers in excel

Step 4

Now run the program by pressing the green Play button as shown in the fig below.

running code to invert values in excel

Step 5

Now after you have run the program, check the column A where we had the original data and now all the positive numbers have converted to negative and negative numbers have been converted to positive.

output showing inverted excel values




Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply