How to Change Positive Numbers to Negative in Excel (6 Ways)

The sample dataset contains Months and Loss columns, where we will change Loss to Profit. To do that, we will change the positive numbers to negative because negative profit is the loss.

Method 1 – Using Mathematical Operation to Change Positive Numbers to Negative in Excel

Steps:

Select the cell with the positive number you want to change to negative. Here, we selected cell C5.
Enter the following formula in cell C5.

=C5*-1

In the example, we multiplied the value in cell C5 by -1. The formula will not change the value. It will only change the number from positive to negative and return the negative value as a result.

Press ENTER to change the number to negative.

Drag the Fill Handle to copy the formula to the other cells.

The formula will be copied to all the other cells and change all positive numbers to negative.

Method 2 – Applying Paste Special Feature in Excel

In this sample dataset, Expenses are written as positive numbers here. We will change these positive numbers to negative.

Steps:

Type -1 in any cell outside your dataset. We wrote it in cell B11.
Copy cell B11.

Select the range where you want to change positive numbers to negative.

Right-click on the selected data.
Select Paste Special.

In the Paste Special dialog box, select Values from Paste.
Select Multiply from Operation.
Click OK.

The positive numbers will change to negative numbers.

You can now delete the -1 you wrote in cell B11 and get your desired dataset.

Method 3 – Use of ABS Function to Change Positive Numbers to Negative

Here, the Expenses column has a mix of positive and negative numbers. We will change the positive numbers to negative and keep the negative numbers as it is.

Steps:

Select the cell where you want to change the positive number to negative. We selected cell D5.
In cell D5 write the following formula.

=ABS(C5)*-1

Formula Breakdown

Here, in the ABS function, we selected C5 as a number. The ABS function will return the absolute value of the number in cell C5.
Multiplied the absolute value by -1. Which will change the sign of the number to negative without changing the value.

Hit ENTER to get the result.

Drag the Fill Handle to copy the formula to other cells.

The formula will be copied to all the other cells and change positive numbers to negative.

Method 4 – Employing IF Function in Excel

Steps:

Select the cell where you want to change positive number to negative. We selected cell D5.
Enter the following formula in cell D5

=IF(C5<0,C5,C5*-1)

Here, in the IF function, we selected C5<0 as logicat_test, C5 as value_if_true, and C5*-1 as value_if_false. The IF function will check if the value in cell C5 is Less than 0. If it is true, the formula will return the value in cell C5 as result. Otherwise, it will multiply the value in cell C5 by -1 and will return it as result.

Press ENTER to get the result.

Drag the Fill Handle to copy the formula to the other cells.

The formula will be copied to all the other cells and change positive numbers to negative.

Method 5 – Using Go to Special Command in Non-blank Cells

This sample dataset includes some empty cells. We will only change the numbers in non-blank cells.

Steps:

Type -1 in any cell outside your dataset. We wrote it in cell B11.
Copy cell B11.

Select the range where you want to change positive numbers to negative.

Go to the Home tab.
Select Find & Select.

Select Go To Special.

A dialog box named Go To Special will appear.

Select Constants from Select.
Click OK.

You can see that only the non-blank cells are selected.

Right-click on any of the selected cells.
Select Paste Special.

The Paste Special dialog box named will appear.

Select Values from Paste.
Select Multiply from Operation.
Click OK.

The positive numbers will change to negative numbers in the non-blank cells.

Method 6 – Applying VBA to Change Positive Numbers to Negative in Excel

Steps:

Go to the Developer tab
Select Visual Basic.

The Visual Basic window will open.

Go to the Insert tab.
Select the Module option.

In the Module enter the following code.

Sub Positive_to_Negative()
Dim current_sheet As Worksheet
Dim selected_range As Range
Dim Result As Range

Set current_sheet = Application.ActiveSheet
Set selected_range = Application.Selection

For Each cell In selected_range
    If cell.Value > 0 Then
    cell.Value = cell.Value * -1
    End If
Next
End Sub

Code Breakdown

We have created a Sub Procedure named Positive_to_Negative.
We declared a Variable named current_sheet as Worksheet, and another Variable named selected_range as Range. We also declared a Variable named Result as Range.
We used the Set statement to assign the Application.ActiveSheet to current_sheet and Application.Selection to selected_range.
We used a For Next Loop that will check each cell in the selected_range.
In For Next Loop, we used an If Statement. This will check if the cell value in the selected range is greater than 0. If it is then, it will multiply the cell value by -1 and return it as a new cell value.
Ended the If statement.
Ended the Sub Procedure.

Save the code and go back to your worksheet.

Select the range where you want to change positive numbers to negative.
Go to the Developer tab.
Select Macros.

In the Macro dialog box, select the Sub Procedure you created in your VBA as the Macro name. Here, we selected the Macro named Positive_to_Negative.
Click Run.

The numbers will change from positive to negative in the selected range.

Things to Remember

It should be noted that Method-1, Method-2, and Method-5 can only be used when all the numbers are positive. Otherwise, it will change the negative numbers to positive while applying these three methods.

Download Practice Workbook

Changing Positive Numbers to Negative.xlsm