10 Ways to Make a Negative Number Positive in Microsoft Excel

This Microsoft Excel tutorial helps you to learn how to make a negative number positive in Excel.

When working with numerical values in Excel, you often need to convert one number format to another, like positive numbers to negative, whole numbers to decimals, and so on. Among these, one of the most popular is making negative numbers positive in Excel.

You often exercise this in financial analysis where the overall topic is negative, like account payables, expenses, liabilities, losses, credit balances, depreciation, adjustments, and more. In such scenarios, negative values might create confusion among the audience.

In this guide, I’ve discussed a range of methods to convert negative numbers to positive values so you can choose one or two based on your worksheet dataset and Excel expertise.

Negative number formats
Negative number formats

Before you begin, you must understand how Excel represents negative numbers. There are two formats. The first one is the numerical values have a negative sign. The second format is the numerical cell value within a parenthesis and is mostly seen if you’re working with currency data.

📒 Read More: 7 Ways to Make Numbers Negative in Microsoft Excel

Make Negative Numbers Positive Using Flash Fill

Are you using Excel 2013 or a newer edition on your computer? If yes, you can use the Flash Fill tool to convert negative numbers to positive ones automatically. However, you must show Excel how to convert the first negative value in the target column. This method involves the use of a helper column on the right side of the target dataset.

Manually convert the first cell
Manually convert the first cell

Select a blank cell to the right of the first value of the dataset. Manually enter the positive format of the negative number in the source data column. Also, select this cell and all other blank cells in the helper column.

Using Flash Fill tool
Using Flash Fill tool

Now, go to the Data tab on the Excel ribbon menu and click on the Flash Fill command button inside the Data Tools block.

Make negative numbers positive using Flash Fill
Make negative numbers positive using Flash Fill

Excel shall instantly make negative numbers positive by analyzing the pattern of the input data.

📒 Read More: 7 Ways to Convert Text to Numbers in Microsoft Excel

Make Negative Numbers Positive Using Multiplication

You can also multiply the negative numbers in a column using the negative one value to get positive numbers. The method becomes quick and accurate if you use the Paste Special tool.

Negative one in a cell
Negative one in a cell

Type -1 in a blank cell on the worksheet. Select the cell and press Ctrl + C to copy it.

Using Paste Special
Using Paste Special

Now, go to the source data column and highlight all the cells that need conversion.

Press Ctrl + Alt + V to bring up the Paste Special dialog.

There, click the Multiply option below the Operation section and click OK.

Negative to positive using Paste Special
Negative to positive using Paste Special

Excel shall make negative numbers positive in the same column.

📒 Read More: 7 Ways to Get the Number of Days in a Month in Microsoft Excel

Make Negative Numbers Positive Using Division

You get a positive number if you divide a negative number with a negative one.

Divide in Paste Special
Divide in Paste Special

To try this method, type -1 in a blank cell and copy it.

Use the steps mentioned in the previous method to convert the values to positive using Paste Special.

However, this time, you must choose Divide in the Operation section of Paste Special after highlighting the source dataset.

Making negative number positive using division
Making negative numbers positive using division

You click OK and Excel does the negative to positive conversion in a flash.

Make Negative Numbers Positive Using Conditional Formatting

Conditional Formatting allows you to format a cell if the cell meets a requirement created by any of the Conditional Formatting rules. In this scenario, you also need to use a custom number formatting code.

More Rules
More Rules

Highlight the target dataset using your mouse and click on the Conditional Formatting button to show the rules drop-down menu.

There, hover the cursor over the Highlight Cells Rules and choose More Rules in the overflow context menu.

New Formatting Rule
New Formatting Rule

The New Formatting Rule dialog shall open. Click on the Format only cells that contain option and configure the rules as outlined below:

  • The first drop-down should be selected to Cell Value
  • The second drop-down should be selected to less than
  • In the third field, enter 0
Format Cells
Format Cells

Click the Format button on the same dialog box and go to the Number tab of the Format Cells dialog.

Go to the Custom format category on the left and enter the code 0;0;0;@ into the Type field on the right.

Conditional Formatting Preview
Conditional Formatting Preview

Click OK to confirm the cell formatting on the Format Cells dialog. Again, click OK on the New Formatting Rule dialog.

Made negative numbers positive
Made negative numbers positive

Excel shall convert all negative numbers in the highlighted column to positive values if the cells meet the Conditional Formatting rules you have just created.

Make Negative Numbers Positive Using Find and Replace

The Find and Replace is another easy-to-use and intuitive tool to convert negative values to positive values in Excel.

Launch Find and Replace
Launch Find and Replace

Go to your worksheet that contains the negative values and press Ctrl + H to bring up the Find and Replace tool.

Replace All
Replace All

In the Find what field, type the negative sign (-) and keep the Replace with field empty.

Now, if your worksheet contains a column of negative values and you want to convert all these to positive values, click the Replace All button on Find and Replace.

Find All
Find All

If you’d like to selectively change negative values to positive ones, click the Find All button to expand the list of cells found.

Replace
Replace

Click on an item to highlight its cell on the worksheet. If this is the intended cell, click the Replace button.

Make Negative Numbers Positive Using Format Cells

You can type a custom number formatting code in the Format Cells dialog to display numerical and text strings in various ways.

Format Cells dialog
Format Cells dialog

Go to your worksheet and select the dataset that you want to convert to positive values from negative ones. Press Ctrl + 1 to bring up the Format Cells dialog.

Made negative numbers positive using Format cells
Made negative numbers positive using Format cells

Go to the Custom category in the left-side navigation. On the right side, you shall see the Type field.

In the Type field, enter 0;0;0;@ and press the OK button on the dialog box.

You shall see that the selected dataset turned positive from negative instantly.

Make Negative Numbers Positive Using ABS Function

In Excel, the ABS function is a mathematical function that returns the absolute value of a number. The absolute value of a number is its distance from zero on the number line, disregarding its sign. Since the function excludes the sign of a number, you can use it to convert negative numbers to positive values.

Using ABS formula
Using ABS formula

Go to your dataset in the Excel worksheet. Find the first cell of the dataset that needs conversion and click on an empty cell to the right side of this cell.

There, insert the following formula and hit Enter:

=ABS(C2)

Excel shall calculate the cell and return the positive value of the negative number.

Using the Fill handle
Using the Fill handle

Now, select this cell again and use the fill handle to apply the formula to the rest of the cells in the column until the cell where reference data exists in the left side column.

Make Negative Numbers Positive Using IF Function

Using an IF function
Using an IF function

Select a blank cell to the right side of the source column containing negative numbers. There, enter the following formula and hit Enter:

=IF(C2<0,-C2,C2)

You must change the cell references according to your own dataset. Excel will calculate the cell and return the absolute value of the reference cell.

Using fill handle to replicate formula
Using fill handle to replicate formula

Use the fill handle to apply the formula to the rest of the cells in the column.

Make Negative Numbers Positive Using Power Query

Power Query of Microsoft Excel enables you to clean and transform your data and enrich your datasets with new features (derivative columns).

If you’re importing a large dataset to Excel where you need to change negatives to positives, it’s better you do it in Power Query. Once the transformation task is done, you can import a clean and organized dataset to an Excel worksheet.

Find below two different ways to accomplish this in Power Query:

Using the Transformation Tool

From SQL Server Database
From SQL Server Database

Are you importing your database to Power Query from an external source? Then, go to the Data tab and click the Get Data command.

On the context menu that appears, hover the cursor over From Database and click on the choice of data sources like From SQL Server Database, From Oracle Database, etc.

From Table Range command
From Table Range command

Alternatively, you’ve got the option to export your Excel worksheet dataset to Power Query by selecting the source data and clicking the From Table/Range command inside the Data tab. You also need to click OK on the Create Table dialog.

Dataset in Power Query
Dataset in Power Query

You’ll now see your dataset in Power Query as shown above.

Multiply in Power Query
Multiply in Power Query

Select the column containing the negative numbers and go to the Transform tab. There, click on the Standard drop-down and choose Multiply from the context menu.

Multiply input dialog
Multiply input dialog

In the Multiply form, enter -1 and click OK.

Made negative numbers positive using Power Query Transform
Made negative numbers positive using Power Query Transform

You shall see that Power Query has transformed your input data column according to your requirements.

Close & Load To
Close & Load To

To send the dataset to Excel from Power Query, click the File tab and select the Close & Load To option.

Import Data
Import Data

You shall now see an Excel worksheet along with the Import Data dialog. Select the Existing worksheet option in the dialog box and select a cell range where you’d like to import the transformed dataset.

Exported dataset to worksheet from Power Query
Exported dataset to worksheet from Power Query

You shall have positive numbers as you wanted.

Using Power Query M Formula

Custom Column
Custom Column

Click the Add Column tab and select the Custom Column command within the General section. The Custom Column wizard shall open.

On Custom Column, type a name inside the New column name field. Inside the Custom column formula field, enter the following M formula:

=Number.ABS([Losses])

Click OK to perform the calculation for the new column. Replace Losses with the source data column name.

Positive values in Power Query
Positive values in Power Query

On the Power Query dialog, you shall see the new column along with converted values.

Follow the Close & Load To method mentioned earlier to export Power Query data to an Excel worksheet.

Make Negative Numbers Positive Using Excel VBA

If you’re automating your Excel worksheet with Excel VBA scripts and need a script to change negative numbers to positive in the same automation workflow, you can use the following script:

VBA script
VBA script
Sub ConvertNegativeToPositive()
    Dim rng As Range
    Dim cell As Range

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

    ' If no range is selected, exit the subroutine
    If rng Is Nothing Then Exit Sub

    ' Loop through each cell in the range
    For Each cell In rng
        ' If the cell contains a numeric value and it's negative, convert it to positive
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Value = Abs(cell.Value)
        End If
    Next cell
End Sub

This script shall let you create a VBA macro that transforms negatives into positive values in a single click. To learn how to use this script, check out this quick tutorial on Excel VBA:

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

Run macro
Run macro

After creating the macro, press Alt + F8 to launch the Macro dialog. There, click on the ConvertNegativeToPositive macro and hit the Run button.

Select range
Select the range

Excel shall prompt you to enter the dataset that needs conversion.

Made negative numbers positive using VBA

The VBA macro shall transform negatives into positives and replace the source dataset with new values.

⚠️ Warning: Before running the macro, create a backup of your Excel workbook. Because you won’t be able to use the Excel Undo feature after running a macro.

Conclusions

So far, you’ve practiced 10 different methods to make a negative number positive in Excel. The methods involve Excel user interface buttons, functions, Power Query, and VBA scripting.

Try out the techniques mentioned in this Excel tutorial and comment below if you liked or disliked any of these. Don’t forget to mention any tips and tricks in your comment if you know a better way to convert negative values to positive values.

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 😃