How to Change a Positive Number to Negative in Excel: 7 Best Ways

Are you tired of manually converting positive numbers to negative in Excel? Look no further! In this blog post, I’ll guide you on how to effortlessly transform positive values into their negative counterparts using Excel.

Whether you’re dealing with financial data, analyzing trends, or simply need to perform calculations, these handy techniques will save you time and effort. I’ll provide step-by-step instructions, accompanied by clear examples, to ensure a seamless process. With this valuable skill in your Excel toolbox, you’ll streamline your workflow and make data manipulation a breeze. Let’s dive in and learn how to change positive numbers to negative ones in Excel!

Also read: How To Convert 8 Digit Number To A Date In Excel

Reasons to Change Positive Numbers to Negative

Find below the cases where you can implement this Excel skill:

1. You need to convert positive values to negatives since the positive values in the raw data represent expenses, debts, losses, etc.
2. You’re working on a financial modeling project. There, you may need to convert thousands of cell values to negatives to accurately reflect the financial model scenarios for losses and expenses.
3. In variance analysis or different data set comparisons, you might need to convert a few columns of positive values to negative ones.
4. For standardization and consistency, you may also need to change positive vell values to negatives.
5. In budget reports, if there are huge columns of overspending or negative budget, you can use an automated process to convert these positive values to negatives.

Also read: How To Make Negative Percentages In Parentheses In Excel

Now, let’s find out all the efficient methods to change negatives to positives in Excel:

1. Using the ABS (Absolute Number) function

When converting a bunch of positive values to negative, the first method you can try is using the ABS function. Here’s how to use it in simple steps:

1. Select a cell where you want to convert positives to negatives.
2. Copy and paste the following formula into that cell:

=-ABS(C2)

3. Edit the cell range C2 to something appropriate according to your Excel worksheet.
4. Hit Enter to get the negative value in one cell.

5. Now, copy and paste the formula from the top to the bottom of the column to convert all the adjacent positive values to negative numbers.

2. Utilizing the Mathematical Operators

One of the basic mathematical concept is if you multiply any integer with negative 1 (-1) then the number becomes negative. So, you can apply this concept by following these steps:

1. Create a new column just to the right of the column under which you got all the positive values.
2. In the second cell of the new column, copy and paste this formula:

=C2*-1

3. Don’t miss on changing the cell reference C2 to the right reference according to your Excel table or dataset.
4. Hit Enter to get the negative value of C2 in the selected cell.

5. Now, drag the fill handle until the point of the last positive value on the adjacent column.
6. The fill handle will automatically convert positive numbers to negative numbers.

Another similar trick is deducting the positive value from zero (0) and the resulting value will be a negative figure. Find below the formula along with the steps to use it:

1. On a new column, select a cell and enter the following Excel formula:

=0-C2

2. You need to change the cell reference if the positive values aren’t under column C.
3. Now, hit Enter to get the negative value.
4. Use the fill handle to copy the same formula across the column to convert all other positive values.
5. You must hide the the existing column of positive values to make the dataset meaningful.

3. Using Paste Special

Both of the above methods changes positive numbers to negative in a new column. How about doing this on a column that originally hosts the positive figures. Here’s how you should proceed:

1. Select any cell outside your dataset on the Excel sheet.
2. Type -1 in that cell and press Ctrl + C together to copy this cell.
3. Now, select the first cell of the column of which you need to convert numbers to negative values.
4. Hit the Ctrl + Shift + Down Arrow keys altogether to highlight the entire column until the point where data exist.
5. Then, right-click and select the Paste Special button on the context menu that pops up.

6. On the Paste Special dialog box, click Multiply, and hit OK.
7. This should instantly make all the positive values negative in your Excel worksheet.

Use Go To Special Command

When you’re using the above Paste Special method to convert positives to negatives, if there are any blank cells, that’ll also get modified. So what if you don’t want to impact the blank cells while changing the positive numbers to negatives? In that scenario, you must use Go To Special command of Excel. Here’s how it’s done:

1. Type -1 on any cell outside the original data set.
2. Copy this cell and select the entire column that needs converting to negative values.

3. On the Home tab, inside the Editing block, click the Sort & Filter drop-down list.
4. Click Go To Special on the drop down menu.

5. Select the Constants option inside the Go To Special dialog box.
6. Hit the OK button and this should automatically highlight all non-blank cells in the selected data column.
7. Now, follow all the steps from Step 5 of the above method to transform positives to negative using Paste Special.

4. Make a Positive Number Negative Using an If Function

The IF function enables you to create logical comparison between what you expect and the value. Using such logical calculations, you can also get negative values from positive numbers in Excel. Here’s how the function works in this scenario:

1. Create a new column beside the existing column of positive values and give the new column a name.
2. Now, select the first cell below the new column’s header and enter the following IF function:

=IF(C2>0,C2*-1,C2)

3. In the above formula, you must modify the cell references above according to your own dataset.
4. Now, hit the Enter key to convert the referred cell to negative vale into the new cell.
5. Copy and paste the same formula across the new column to get negative values of the adjacent column.
6. Hide the original column with positive values to declutter the dataset or data table.

5. Using a VBA Script

Do you want to transform positive values to negative ones in a single click on Excel? You must follow these instructions to create and implement this simple VBA script:

1. Save your Excel workbook as a Macro–Enabled workbook.
2. To do this, click File and then choose Save As.
3. Select Browse and pick a folder in the local storage on the Save As dialog box.
4. There, also click the Save as type drop-down list and choose the macro-enabled option.
5. Hit the Save button to complete the process.
6. Now, press Alt + F11 together to go to the VBA Editor tool.

7. There, click the Insert menu and then choose Module.
8. On the new module, copy and paste this VBA script:

Sub PositiveToNegative()
For Each cell In Selection
If cell.Value > 0 Then
cell.Value = cell.Value * -1
End If
Next cell
End Sub

9. Click the Save button on the VBA Editor and close the tool.

10. Now, highlight the cell ranges that contain positive values and needs converting.
11. Press Alt + F8 keys to go to the Macro dialog box.
12. Now, select the PositiveToNegative macro and hit the Run button.

Excel will automatically convert the selected cell range to negative values. The method will only work if the selected cells contain values more than zero (0).

6. Use This Secret Format Cells Code

Another way to directly convert the existing columns of positive cell values into negative ones is by using a custom Format Cells code. Here’s the code and how to apply it on your data:

1. Select the cell ranges on which you want to apply this custom formatting code.
2. Hit the Ctrl + 1 keys together to open the Format Cells dialog box.
3. Under the Category section, click Custom.
4. Below the Type section on the right, enter the following code:

-General

5. Under the Sample section, you must already see the negative output format of cell values.
6. Click OK to change the entire selection of positive values to negative.

7. Excel Automate to Convert Positives Numbers to Negatives

Do you need to use Office Scripts on Excel Automate to make the whole process automatic? Are you using Excel on the web and unable to use VBA scripts to automate tasks? Then, you should try the following instructions on a Microsoft 365 subscription-based Excel web or desktop app:

1. Go to the Automate tab and click New Script option within the Scripting Tools block.

2. Into the Code Editor shown on the right, copy and paste this script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range D2 on selectedSheet
	selectedSheet.getRange("D2").setFormulaLocal("=IF(C2>0,C2*-1,C2)");
	// Paste to range D3:D8 on selectedSheet from range D2 on selectedSheet
	selectedSheet.getRange("D3:D8").copyFrom(selectedSheet.getRange("D2"), ExcelScript.RangeCopyType.all, false, false);
}

3. Make necessary changes in the following code elements to direct the script to the appropriate datasets:
   • Set range D2: the first cell to apply a formula
   • ("=IF(C2>0,C2*-1,C2)"): must be changed according to the existing datasets
   • getRange("D3:D8"): must also be changed accordingly
4. You can increase the cell range from D3:D8 to D3:D100 or more if needed.
5. Click Save script and then select Run to convert existing values to negative numbers.

3. Also, hide the original column of positive numbers to avoid confusion.

Conclusion

So, these are all popular and regularly used Excel functions to convert a positive number to a negative. You need to choose a method depending on the Excel worksheet. If there are very few positive cells that need converting, go with the manual processes like mathematical operators, Paste Special, Go To Special, IF function, and ABS function.

Contrarily, if you’re looking for automation opportunities, choose the VBA Scripts and Office Scripts-based methods. Whichever methods you choose, don’t forget to share your comments after trying them.