This effortless Excel tutorial shall show you how to make numbers negative in Excel step by step with real worksheet images.

It’s often necessary to input negative numbers in Microsoft Excel for financial analysis, data manipulation, error handling, and mathematical operations. The conventional wisdom says you type the subtraction operator (the minus sign) followed by a value. However, this method is pretty basic and highly time-consuming.

I’ve gathered below all the intuitive and automated methods to convert positive numbers to negative in Excel. Let’s get started!

Make Numbers Negative Using Flash Fill

Suppose, you’ve got a column (column A) filled with positive numbers that need conversion into negative numbers.

Go to the adjacent column and select the first cell. There, enter the following formula and hit Enter:

=-A1

Now, select the first cell with a negative number and drag the fill handle down the column to fill the rest of the cells with negative numbers of the corresponding cells in column A.

If the above method generates incorrect values in the helper column, which is column B, you can use the Flash Fill tool to correct the wrong entries.

After you’ve dragged the fill handle from B1 until B10, you should see a tiny cell icon in the lower right corner of the range selection.

Click on that to open the Auto Fill Options menu. There, select the Flash Fill option to correct automatically filled negative numbers.

Make Numbers Negative Using Paste Special

The Paste Special tool allows you to perform basic mathematical operations on the selected dataset in a few clicks. Therefore, you can use this method to multiply a selected cell range with -1 to achieve the result you’ve been looking for.

Go to the target worksheet and select any blank cell. There, type -1. Now, press Ctrl + C to copy this cell.

Now, highlight the dataset you want to convert to negative numbers. Press Ctrl + Alt + V to call the Paste Special dialog box.

Select the Multiply option below the Operation section and hit the OK button.

Excel shall convert the input dataset to negative numbers in a flash.

Make Numbers Negative Using Custom Formatting

Suppose, you’d like to create a data-entry cell range that automatically converts positive numbers to negative upon entering the cell value. In this scenario, you can make use of the Custom number format coding feature in Excel.

Highlight the cell range in your worksheet where you expect that the data entry operators would enter positive numbers either whole numbers or fractions in decimal places.

Press Ctrl + 1 to bring up the Format Cells dialog. Go to the Custom category on the left-side column.

Inside the Type field, enter -General and click OK to save the changes.

Now, if you enter a positive number in a cell in this formatted cell range, Excel shall display it as a negative number.

You can also use this method to convert an existing dataset of positive numbers to negative numbers. Simply highlight the target dataset and follow the steps mentioned earlier.

Make Numbers Negative Using Excel Functions

You can use various Excel formulas to convert positive numbers to negative in Excel. Find below the most commonly used functions:

Using a TEXT Formula

Navigate to your worksheet where you’ve got a column of cell range with positive values. Highlight a blank cell to the immediate right or left of the first cell in your dataset and enter the following TEXT formula in it:

= TEXT(A1, "-0.00")

Hit Enter to calculate the first cell in the series. make sure you adjust the cell reference according to your own worksheet.

Now, select this cell and drag the fill handle down the column to get the negative values for the rest of the cells in the target dataset.

You shall see the converted negative numbers in the helper column.

The dataset you get in this method is in text format. If you need the data in number formatting, copy the output to another column by pressing Ctrl + Alt + V and choosing Values in the Paste Special dialog.

Select any cell in the new column and click on the yellow warning sign. Choose the Convert to Number option in the context menu that appears.

Excel shall convert the text values to numbers instantly.

Using the IF Formula

If you don’t want to use the TEXT formula mentioned above, you can use the following IF formula:

=IF(A1>0,A1*-1,"N/A")

The above formula multiplies the cell A1 with a negative 1 if its original value is above 0, which is a positive number. If the original cell value isn’t more than 0, you get N/A text.

Before using the formula, change the cell reference A1 to an appropriate cell address from your worksheet.

The IF formula is better than the TEXT formula because its outputs are always in number format. You don’t need to perform additional steps to convert TEXT to Number formatting.

Make Numbers Negative in Power Query

Often you import a large dataset from an external database to Excel. If you need to convert this dataset to negative numbers, don’t wait to get the database to an Excel worksheet and then transform it. Instead, you can temporarily import the database to Power Query, convert the values to negative using the Multiply feature, and send the transformed dataset to your destination Excel worksheet.

To import a database to Power Query from a third-party source, go to the Data tab on your Excel worksheet and click on the Get Data command.

Hover the cursor over the From Database menu and click on the source from a new context menu. For example, you can choose From Oracle Database, From Microsoft Access, etc.

If the input dataset is already in your workbook, navigate to it, select the cell range, and click on From Table/Range in the Data tab of the Excel ribbon menu.

Click OK on the Create Table dialog to export the dataset to Power Query.

Your database shows up on Power Query in the above format. There, click on the Transform tab and choose Multiply from the Standard drop-down menu.

You shall now see the Multiply wizard. Enter -1 into the Value field and click OK to apply.

Power query shall transform the input dataset to negative numbers as shown above in the screenshot.

Click the File tab on the Power Query ribbon and choose Close & Load To from the context menu.

You shall see your Excel worksheet. There, you’ll also find the Import Data dialog. Click on the Existing worksheet option and highlight the destination cell range on your worksheet. Click OK to complete the import process.

That’s it! You’ve transformed the positive numbers into negative ones in Excel using Power Query.

Make Numbers Negative Using Excel VBA

If you find the methods mentioned so far a bit manual and time-consuming, no worries! I can help you automate the process of making numbers negative programmatically. In this technique, you’ll be using Excel VBA to create custom scripts to command Excel to perform tasks for you.

The method involves two parts. First, you need to create a VBA macro using the script I’ve provided. The task is truly simple if you check out this article:

📒 Read More: How To Use The VBA Code You Find Online

Here’s the script that you need to use when creating a VBA macro in your Excel workbook:

Sub ConvertToNegative()
    Dim TargetRange As Range
    Dim DestinationRange As Range
    Dim Cell As Range
    Dim i As Integer

    ' Prompt the user to select the target range
    On Error Resume Next
    Set TargetRange = Application.InputBox("Select the target range", Type:=8)
    On Error GoTo 0

    ' Prompt the user to select the destination range
    On Error Resume Next
    Set DestinationRange = Application.InputBox("Select the destination range", Type:=8)
    On Error GoTo 0

    ' Check if ranges are set
    If TargetRange Is Nothing Or DestinationRange Is Nothing Then
        MsgBox "Please select both target and destination ranges", vbInformation
        Exit Sub
    End If

    ' Check if ranges have the same size
    If TargetRange.Cells.Count <> DestinationRange.Cells.Count Then
        MsgBox "Target and destination ranges should have the same size", vbInformation
        Exit Sub
    End If

    ' Convert positive numbers to negative
    i = 1
    For Each Cell In TargetRange
        If IsNumeric(Cell.Value) And Cell.Value > 0 Then
            DestinationRange.Cells(i).Value = -Cell.Value
        Else
            DestinationRange.Cells(i).Value = Cell.Value
        End If
        i = i + 1
    Next Cell

    MsgBox "Conversion completed", vbInformation
End Sub

Considering you’ve successfully created the VBA macro, press Alt + F8 to show the Macro dialog.

There, select the ConvertToNegative macro and hit the Run button.

The macro shall show you an input box where you need to input the target dataset either by typing or by selecting a cell range using the mouse.

Then, another prompt will ask you to select the destination cell range.

As soon as you comply with these prompts, Excel VBA shall convert the given positive numbers to negative numbers.

⚠️ Warning: Create a backup copy of your original Excel workbook before using the above VBA macro. You won’t be able to use the Excel undo feature in your worksheet after running a VBA script.

Make Numbers Negative Using Office Scripts

Since Excel VBA doesn’t work in Excel for the web, you can use Office Scripts to automate tasks in an online Excel workbook. Besides the online app, Office Scripts also works in Excel for the Microsft 365 desktop app.

To use Office Scripts to make numbers negative by executing a simple script, go to the Automate tab on the Excel ribbon menu. There, click on the New Script command button inside the Scripting Tools block.

You shall now see the Code Editor console on the right side border of the Excel web or desktop app. There, copy and paste the following script and hit the Save script button to save the script with a unique name.

function main(workbook: ExcelScript.Workbook) {
    // Get the current selected range.
    let selectedRange = workbook.getSelectedRange();

    // Get the values in the selected range.
    let values = selectedRange.getValues();

    // Iterate over the array and convert all numbers to negative.
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            // Check if the value is a number.
            if (typeof values[i][j] === "number") {
                // Convert the number to negative.
                values[i][j] = -Math.abs(values[i][j]);
            }
        }
    }

    // Set the values back to the selected range.
    selectedRange.setValues(values);
}

Make sure you’ve completed the above steps in the worksheet containing the input dataset.

Highlight the input dataset using the mouse cursor and then click on the Run button of the Code Editor.

Excel shall transform positive values to negative in the selected cell range. The script replaces the existing dataset so create a backup of the original workbook before executing this Office Scripts program. Like Excel VBA, changes made in the worksheet by Office Scripts are irreversible.

Conclusions

So far, you’ve gone through various tried and tested methods to make numbers negative in Excel. Try all the methods mentioned above and comment below to let us know the one you like the most. Also, share your feedback and suggestions in the comment box.