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.
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.
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.
Now, go to the Data tab on the Excel ribbon menu and click on the Flash Fill command button inside the Data Tools block.
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.
Type -1 in a blank cell on the worksheet. Select the cell and press Ctrl + C to copy it.
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.
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.
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.
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.
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.
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
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.
Click OK to confirm the cell formatting on the Format Cells dialog. Again, click OK on the New Formatting Rule dialog.
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.
Go to your worksheet that contains the negative values and press Ctrl + H to bring up the Find and Replace tool.
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.
If youโd like to selectively change negative values to positive ones, click the Find All button to expand the list of cells found.
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.
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.
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.
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.
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
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.
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
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.
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.
Youโll now see your dataset in Power Query as shown above.
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.
In the Multiply form, enter -1 and click OK.
You shall see that Power Query has transformed your input data column according to your requirements.
To send the dataset to Excel from Power Query, click the File tab and select the Close & Load To option.
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.
You shall have positive numbers as you wanted.
Using Power Query M Formula
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.
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:
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 cellEnd SubThis 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
After creating the macro, press Alt + F8 to launch the Macro dialog. There, click on the ConvertNegativeToPositive macro and hit the Run button.
Excel shall prompt you to enter the dataset that needs conversion.
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.
