How to Convert Negative Value to Positive in Excel Using Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show how to convert negative value to positive in Excel using formula. We will create Excel formulas with common functions like IF, MAX, and ABS. Using these formulas, you can easily convert the format. So, without any delay, let’s start the discussion.

how to convert negative value to positive in excel using formula


How to Convert Negative Value to Positive Using Formula in Excel: 3 Easy Ways

To demonstrate the methods, we will use a dataset of the company’s income for the previous month and the present month in million dollars. By subtraction process, we can find the change in income. Sometimes, we need to know only the value of the difference, not the sign. Let’s see the 3 methods to convert the negative value to the positive using formula in Excel.

how to convert negative value to positive in excel using formula


1. Use ABS Function to Convert Negative Value to Positive in Excel

Utilizing the ABS function, we can formulate a formula to convert the negative value to positive. Let’s follow the steps below to learn the method.

STEPS:

  • In the Change column, you can observe both positive and negative numbers.
  • In this step, we will convert the negative values of the E column into positive ones.
  • To do so, write down the following formula in the F5 cell:
=ABS(E5)
  • Now, press Enter to see the result.

Use ABS Function to Convert Negative Value to Positive in Excel

  • After that, drag down the formula in the F6:F9 range with the Fill Handle option.

  • As a result, in the Positive Value column, all values are positive.
  • So, we have successfully converted the negative value to positive.


2. Utilize MAX Function to Convert Negative Value to Positive in Excel

Utilizing the MAX function, we can build up a formula to convert the negative value to a positive. Let’s follow the steps below to learn the method.

STEPS:

  • Firstly, we need to calculate the change in the income of the company.
  • To do so, you have the subtraction from the present value to the previous value.
  • So, write down the following formula in the E5 cell to calculate the change:
=D5-C5
  • Then, press OK to observe the result.

Utilize MAX Function to Convert Negative Value to Positive in Excel

  • After that, drag down the subtraction formula in the E6:E9 range with the Fill Handle option.

  • As a result, we can observe the change in income in the column.

Utilize MAX Function to Convert Negative Value to Positive in Excel

  • In the Change column, you can observe both positive and negative numbers.
  • In this step, we will convert the negative value into positive.
  • To do so, write down the following formula in the F5 cell:
=MAX(E5,-E5)
  • Then, press Enter to watch the result.

  • After that, drag down the formula in the F6:F9 range with the Fill Handle option.

Utilize MAX Function to Convert Negative Value to Positive in Excel

  • As a result, in the Positive Value column, all values are positive.
  • So, we have successfully converted the negative value to positive one.


3. Create a Formula Using IF Function to Change Negative Value to Positive

Utilizing the IF function, we can form a formula to convert the negative value to positive. Let’s follow the steps below to learn the method.

STEPS:

  • In the Change column, you can observe both positive and negative numbers.
  • In this step, we will convert the negative values of the E column into positive.
  • To do so, write down the following formula in the F5 cell:
=IF(E5>0,E5,-E5)
  • Now, press Enter to see the result.

Create Formula Using IF Function to Change Negative Value to Positive

  • After that, drag down the formula in the F6:F9 range with the Fill Handle option.

  • As a result, in the Positive Value column, all values are positive.
  • So, we have successfully converted negative value to positive.

Read More: How to Change Positive Numbers to Negative in Excel


How to Change Negative Value to Positive and Vice Versa in Excel Applying VBA Code

Without building the formula, we can also change the negative value to positive applying VBA Code. Here, you can also change the positive number to negative. To demonstrate the method we have created a dataset of 5 random numbers. Here, both positive and negative numbers are present.

how to convert negative value to positive in excel using formula

STEPS:

  • Firstly, select the numbers in the B4:B9 range.
  • Then, Ctrl + C to copy the numbers.

Apply VBA Code to Change Negative Value to Positive and Vice Versa

  • After that, Ctrl + V to paste the numbers in the C and columns.
  • So, the numbers of the B columns are copied to the C and columns.

  • Now, select the C5: C9 range.

Apply VBA Code to Change Negative Value to Positive and Vice Versa

  • After selection, click on the Developer tab and select Visual Basic.

  • Instantly, the Microsoft Visual Basic Application window will open.
  • In this window, select Insert >> Module to open the module.
  • In the module, you will insert your code.

  • As a result, the module will appear.
  • Now, write down the following code in the module:
Sub Pos_To_Neg()
Dim x As Range
For Each x In Selection
If IsNumeric(x.Value) Then
x.Value = Abs(x.Value)
End If
Next x
End Sub

Apply VBA Code to Change Negative Value to Positive and Vice Versa

Here, the code is doing the work of the ABS function. In the code, we are applying the ABS function and the code is returning the absolute value of the selected cells.
  • After writing the code, you need to run the code.
  • To do so, press on the following icon.
  • So, your code has successfully run.
  • Now, return the Excel Worksheet to observe the outcome.

  • As a result, in the All Positive column, all values are positive.
  • So, we have successfully converted the negative value to positive.

  • In the next step, we will convert the positive value to negative.
  • To do so, select the D5:D9 range and return to the module.

Apply VBA Code to Change Negative Value to Positive and Vice Versa

  • In the module, replace the following code with the previous code:
Sub Neg_To_Pos()
Dim x As Range
For Each x In Selection
If IsNumeric(x.Value) Then
x.Value = -Abs(x.Value)
End If
Next x
End Sub
Here, the code is doing the work of the ABS function. In the code, we are applying the ABS function and the code is returning the absolute value of the selected cells. After that, we are adding minus sign with each result. For this reason, all the elements are in the negative form.
  • After writing the code, press the following icon to run the code.
  • Now, return the Excel Worksheet to observe the outcome.

  • As a result, in the All Negative column, all values are negative.
  • So, we have successfully converted the positive value to negative.

Apply VBA Code to Change Negative Value to Positive and Vice Versa

Read More: How to Move Negative Sign at End to Left of a Number in Excel


Utilize Custom Formatting to Show Negative Value to Positive and Vice Versa

Utilizing Custom Formatting, we can easily change the negative value to positive and vice versa. Let’s follow the steps below to learn the method.

STEPS:

  • Firstly, select the numbers in the B4:B9 range.
  • Then, Ctrl + C to copy the numbers.

Utilize Custom Formatting to Transform Negative Value to Positive and Vice Versa

  • After that, Ctrl + V to paste the numbers in the C and columns.
  • So, the numbers of the B columns are copied to the C and columns.

  • Now, select the C5: C9 range.

  • After selection, press the shortcut key Ctrl + 1.
  • Here, the shortcut key will open the custom formatting options.
  • Now, go to the Custom panel and in the Type input bar, enter “#,###;#,###”.
  • In the end, click OK to proceed.

Utilize Custom Formatting to Transform Negative Value to Positive and Vice Versa

  • As a result, in the C column, all the numbers are in the positive form.
  • As the C9 cell contains 0, it is showing nothing in this cell.
  • After that, you can also convert the positive value to negative.
  • So, select the D5: D9 range.

  • After selection, press the shortcut key Ctrl + 1.
  • Here, the shortcut key will open the custom formatting options.
  • Now, go to the Custom panel and in the Type input bar, enter “-#,###;-#,###”.
  • In the end, click OK to proceed.

Utilize Custom Formatting to Transform Negative Value to Positive and Vice Versa

  • As a result, in the D column, all the numbers are in the negative form.
  • As the D9 cell contains 0, it is showing just () in this cell.

Read More: How to Sum Negative and Positive Numbers in Excel


Download Practice Workbook

To practice by yourself, download the following workbook.


Conclusion

In this article, we have shown how to convert negative value to positive in Excel using formula. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles


<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo