The general public who work with Excel spreadsheets take care of numbers in massive/small datasets.
And while you work with numbers, you should have all kinds of it (optimistic, detrimental, decimals, date/time).
One of many frequent duties a number of us must do usually is to transform these numbers from one format to a different.
And possibly the commonest one is when it’s a must to change detrimental numbers to optimistic numbers (take away the detrimental signal) for some calculations.
And once more, there are a number of methods you are able to do this in Excel.
On this tutorial, I’ll present you some easy methods to vary detrimental numbers to optimistic in Excel (utilizing formulation, a copy-paste approach, and different superior strategies).
So for those whore , hold studying!
Table of Contents
Multiply with minus 1 to Convert Unfavorable Quantity to Constructive
When you’ve got a column filled with numbers and also you wish to shortly get the numbers the place negatives have been transformed into optimistic, you’ll be able to simply try this by multiplying these detrimental values by -1.
However you even have to make sure that youre solely multiplying the detrimental numbers and never the optimistic ones.
Suppose you might have a dataset as proven beneath:
The above method makes use of the IF perform to first test whether or not the quantity is optimistic or not. If its optimistic, the signal just isn’t modified and if its detrimental, a detrimental signal is added to the reference, which finally ends up giving us a optimistic quantity solely.
In case you might have textual content values within the dataset as properly, this perform goes to disregard that (and solely detrimental values will probably be modified)
Now that you’ve got the required outcome, you’ll be able to convert these formulation to values (and replica it over the unique information in case you dont want it)
Use the ABS perform to Change all Unfavorable Numbers to Constructive
Excel has a devoted perform that strips out the detrimental signal and provides you absolutely the worth.
.. the ABS perform
Suppose you might have the dataset as proven beneath and also you wish to change detrimental values to optimistic values.
The above ABS perform doesnt impression the optimistic numbers however converts detrimental numbers into optimistic values.
Now that you’ve got the required outcome, you’ll be able to convert these formulation to values (and replica it over the unique information in case you dont want it)
One minor downside of the ABS perform is that it may solely work with numbers. In case you might have textual content information in among the cells and you employ the ABS perform, it gives you #VALUE! error.
Multiply Utilizing Paste Particular To Reverse the Signal
In case you wish to reverse the signal of the quantity (i.e., change detrimental to optimistic and optimistic to detrimental), you may also use this paste particular multiplication approach.
Suppose you might have the dataset as proven beneath and also you wish to reverse the signal:
Under are the steps to reverse the signal utilizing Paste Particular:
In any empty cell within the worksheet, enter -1https://trumpexcel.com/wp-content/uploads/2019/12/Enter-1-in-any-empty-cell-in-the-worksheet.png” />Copy this cell (which has the worth -1)Choose the vary the place you wish to reverse the signal.Proper-click on any of the chosen cellsClick on Paste Particular. It will open the Paste Particular dialog fieldhttps://trumpexcel.com/wp-content/uploads/2019/12/Click-on-Paste-Special-1.png” />Within the Paste possibility, choose Valueshttps://trumpexcel.com/wp-content/uploads/2019/12/Select-values-in-Paste-Special-dialog-box.png” />Within the Operation choices, choose Multiplyhttps://trumpexcel.com/wp-content/uploads/2019/12/Select-multiply-in-the-Paste-Special-dialog-box.png” />Click on OkDelete -1 from the cell
You’d discover that the above steps immediately change reverses the signal of the quantity (i.e., optimistic numbers grow to be detrimental and detrimental numbers grow to be optimistic).
However what for those who solely wish to convert detrimental numbers to optimistic numbers and never the opposite means spherical?
In that case, you one way or the other first want to pick all of the detrimental numbers after which comply with the above steps.
Right here is find out how to choose solely the detrimental numbers in Excel:
Choose all the datasetHold the Management key after which press the F key. It will open the Discover and Exchange dialog field.Within the Discover what area, enter (the minus signal)Click on on Discover AllHold the Management Key and press the A key.
The above steps would choose solely these cells which have a detrimental signal. Now that you’ve got these cells chosen, you should utilize the Paste Particular approach to vary the signal of solely the detrimental numbers.
This method has two benefits over the method approach (the 2 strategies coated earlier than this):
It doesnt require you so as to add a further column after which use a method to get the lead to that column. You should use this in your current dataset.You dont must convert the formulation to values (because the outcome you get is already worth and never a method)
Flash Fill To Take away the Unfavorable Signal
Flash Fill is a brand new performance that was launched in Excel 2013.
It permits you to shortly establish patterns after which provide the outcome the place the sample has been utilized to all the dataset.
One use of that is when you might have names and also you wish to separate first identify and final identify. As quickly as you kind the primary identify in an adjoining cell a few instances, Flash Fill will establish the sample and offer you all the primary names.
Equally, you should utilize it to shortly take away the detrimental signal from a quantity, whereas the optimistic values stay unchanged.
Under is a dataset the place I’ve the detrimental numbers and I wish to change these to positives values.
Under are the steps to vary detrimental numbers to optimistic numbers utilizing Flash Fill:
Within the adjoining to the one with the info, enter the anticipated outcome manually. On this instance, I’ll manually enter 874Within the cell beneath it, enter the anticipated outcome (162 on this instance)Choose each the cellsPlace the cursor over the underside proper a part of the choice. It’s going to change right into a plus iconClick and drag to fill the column (or double-click)Click on on the AutoFill Choices iconClick on Flash Fill
The above steps would provide the anticipated outcome, the place the detrimental signal has been eliminated.
One factor you could keep in mind when utilizing this methodology is that Excel depends on guessing the sample. So you’ll have to no less than present Excel that you’re changing a detrimental quantity to optimistic.
Because of this you’ll have to manually enter the anticipated outcome till you might have coated no less than one detrimental quantity.
Convert Unfavorable Numbers to Postive with a Single Click on (VBA)
And lastly, you may also use VBA to transform detrimental values to optimistic values.
I’d suggest utilizing this methodology if doing that is one thing it’s a must to do usually. Perhaps you commonly get the dataset from a database or a colleague and it’s a must to do that each time.
In such a case, you’ll be able to create and save the VBA macro code to the Private Macro Workbook and place the VBA within the Fast Entry Toolbar. This manner, the subsequent time you get a dataset the place it’s a must to do that, you merely choose the info and click on on the icon within the QAT
Dont fear, I’ll present you the precise steps to get this up and working.
Under is the VBA code that may convert detrimental values to optimistic values within the chosen vary:
Sub ChangeNegativetoPOsitive()
For Every Cell In Choice
If Cell.Worth < zero Then
Cell.Worth = -Cell.Worth
Finish If
Subsequent Cell
Finish Sub
The above code makes use of the For Subsequent loop to undergo every cell within the choice. It makes use of the IF assertion to test whether or not the cell worth is detrimental or not. If the worth is detrimental, the signal is reversed, and if its not, its ignored.
You may add this code to a daily module in a workbook (for those who solely wish to use this in that workbook). And in case you wish to use this macro code in any workbook in your system, it can save you it in a private macro workbook.
Listed here are the steps to get the Private Macro Workbook (PMW).
Listed here are the steps to avoid wasting this code within the PMW.
Now let me present you find out how to add this code to the Fast Entry Toolbar (steps are the identical whether or not you save this code in a single workbook or within the PMW)
Open the workbook the place you might have the dataAdd the VBA code to the workbook (or to the PMW)Click on on the Customise Fast Entry Toolbar possibility within the QAThttps://trumpexcel.com/wp-content/uploads/2019/12/Click-on-the-Customize-Quick-Access-Toolbar-option.png” />Click on on Extra Instructionshttps://trumpexcel.com/wp-content/uploads/2019/12/Click-on-More-commands.png” />Within the Excel Choices dialog field, click on on the Select Instructions from drop-downClick on Macros. It will present you all of the macros within the workbook (or the Private Macro Workbook)https://trumpexcel.com/wp-content/uploads/2019/12/Click-on-Macros-in-the-Excel-Options-drop-down.png” />Click on on the Add buttonhttps://trumpexcel.com/wp-content/uploads/2019/12/Click-on-the-Add-button.png” />Click on OK
Now you should have the macro icon within the QAT.
Copy this cell (which has the worth -1)Choose the vary the place you wish to reverse the signal.Proper-click on any of the chosen cellsClick on Paste Particular. It will open the Paste Particular dialog field
Within the Paste possibility, choose Values
Within the Operation choices, choose Multiply
Click on OkDelete -1 from the cell
Click on on Extra Instructions
Within the Excel Choices dialog field, click on on the Select Instructions from drop-downClick on Macros. It will present you all of the macros within the workbook (or the Private Macro Workbook)
Click on on the Add button
Click on OK
