7 Ways to Make Numbers Negative in Microsoft Excel

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.

Formula in first cell
Formula in first cell

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

=-A1
Use fill handle
Use fill handle

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.

Flash fill option
Flash fill option

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.

Copy negative 1
Copy negative 1

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

Multiplying in Paste Special
Multiplying in Paste Special

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.

Make numbers negative in Excel using Paste Special
Make numbers negative in Excel using Paste Special

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.

Show Format Cells
Show Format Cells

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.

Creating Custom number format
Creating Custom number format

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

Make numbers negative in Excel using Custom code

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

Convert existing dataset to negative using Custom
Convert existing dataset to negative using Custom

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:

Using a TEXT formula
Using a TEXT formula
= 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.

Using fill handle to copy formula
Using fill handle to copy formula

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.

Paste Special to convert to text
Paste Special to convert to text

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.

Convert to Number option
Convert to Number option

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.

Make numbers negative in Excel using TEXT
Make numbers negative in Excel using TEXT

Excel shall convert the text values to numbers instantly.

Using the IF Formula

Using IF to make numbers negative in Excel
Using IF to make numbers negative in Excel

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.

From Oracle Database
From Oracle Database

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.

Create Table
Create Table

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.

Transforming a database
Transforming a database

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.

Multiplying a dataset
Multiplying a dataset

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

Converted to negative in Power Query
Converted to negative in Power Query

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

Close & Load To
Close & Load To

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

Import Data
Import Data

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.

Make numbers negative with Power Query
Make numbers negative with Power Query

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:

VBA script to make number negative
VBA script to make number negative
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
Run VBA Macro
Run VBA Macro

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.

Input of target dataset
Input of target dataset

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.

Input for destination
Input for destination

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

Make numbers negative using VBA

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.

New Script
New Script

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.

Save script in Code Editor
Save script in Code Editor

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.

Run office scripts
Run office scripts

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

make numbers negative using Office Scripts
make numbers negative using Office Scripts

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃