Remove leading minus sign from numbers in Excel -3 simple methods
Dealing with negative numbers in Excel can sometimes be tricky, especially when you need to convert them to positive numbers by removing the leading minus sign. This article provides a comprehensive guide on various methods to remove the leading minus sign from numbers in Excel. Whether you’re managing financial data, preparing reports, or simply cleaning up imported datasets, knowing how to quickly convert negative numbers to positive ones can save significant time and prevent errors in calculations.
Remove leading minus sign from cell with formula
There is a straightforward Excel formula that can help you instantly obtain the absolute value of numbers with a leading minus sign, converting them all to positive values. This approach is practical when you need a dynamic, formula-based solution that updates automatically if your source data changes in the future. It's particularly useful for small to medium datasets.
Select a blank cell next to the numbers (for example, cell B1), and enter the following formula:
=ABS(A1)Here, A1 represents the cell from which you want to remove the minus sign. After entering the formula, press Enter to confirm. To apply the formula to multiple cells, drag the autofill handle down to fill the formula in additional rows as needed. This method will not only remove the negative sign, but also automatically display the absolute value if the original number changes.
Tips:
- To convert the formula results to actual values (not formulas), copy the results and use Paste Special > Values in a new column or over the existing one. This step is important prior to deleting the original negative numbers.
- If you need to process a large dataset, formulas can slow down Excel; consider using VBA or Paste Special for efficiency.
Remove leading minus sign from cell with Kutools for Excel
Transform your data seamlessly with Kutools for Excel's Change Sign of Values feature! Whether you need to convert negative numbers to positive or vice versa, Kutools for Excel simplifies the process with just a few clicks. Kutools is especially effective for bulk operations, non-formula-based conversions, or when working with a mix of data types.
After free installing Kutools for Excel, please do as below:
1. Select the cells that you want to remove leading minus signs, and then click Kutools > Contents > Change Sign of Values. See screenshot:
2. In the Change Sign of Values dialog, check Change all negative values to positive option. This specific setting ensures only the negative values are converted, leaving other numbers intact. See screenshot:
3. Click Ok. Now you can see all the negative numbers have been converted to positive values in the selected range, with the adjustment applied instantly.
With Change Sign of Values, you can easily convert all positive values to negatives, fix the trailing negative signs (which sometimes appear due to imported data), and change all negatives to zero or reverse the sign of values entirely. This feature is particularly efficient for large data ranges and for users who want to avoid formulas or manual copying.
Note: While Kutools is very convenient, always double-check that you have selected the correct range before applying bulk changes, especially on critical or unsaved data.
Remove leading minus sign from cell with Format Cells
If you want to remove the leading minus sign from numbers and also visually distinguish negative numbers from positive ones, you can apply the Format Cells function. This method allows you to change how the values look without actually changing the underlying data, which is useful when visual representation is more important than altering the values themselves.
1. Select the numbers you want to modify, then right-click to open the context menu and select Format Cells. See screenshot:
2. In the Format Cells dialog, under the Number tab, choose Number from the Category list. Then, set the number of decimal places as needed (for example, type 0 in the Decimal places box), and select the display style for negative numbers (for example, use parentheses or make negatives red to distinguish them).
3. Click OK. After applying this format, the leading minus sign may be hidden depending on your formatting choice, but negative and positive numbers can still be differentiated as per your selected format.
Note: This method changes only the appearance, not the actual cell values. If you copy or use these numbers in calculations, their original sign remains unless you use one of the conversion methods above.
Convert All Negative Numbers to Positive with VBA
For users working with large datasets or those who prefer automation, using a simple VBA macro provides a highly efficient way to convert all negative values to positive across a chosen range. This method is ideal when you need to process a large number of cells in one step, without relying on formulas or extra columns.
Advantages: Handles huge ranges at once, automates repetitive tasks, and doesn't require formulas or add-ins.
Limitations: Requires enabling macros and some basic familiarity with the VBA editor. Always make a backup before running any VBA code.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window.
If you don't see the Developer tab, enable it from Excel Options File > Options > Customize Ribbon and check Developer.
2. In the VBA window, click Insert > Module, and paste the following code:
Sub BatchConvertNegativesToPositives()
Dim rng As Range
Dim cell As Range
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to convert negatives to positives", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Value = Abs(cell.Value)
End If
Next cell
End Sub3. Click the 
Run button to execute. When prompted, select the range containing the numbers you want to process and click OK. All negative values in the selected range will be automatically converted to positive ones.
Troubleshooting: If nothing happens, ensure macros are enabled in your Excel settings. If you click Cancel in the range selection dialog, the macro will exit without changes. Non-numeric cells are skipped automatically.
Tip: Always save your workbook before running macros, as this change cannot be automatically undone with a single click of Undo if mixed with other actions.
Use Paste Special to Convert Negatives to Positives
Excel’s Paste Special operation provides a quick, formula-free way to convert negative values to positives. By multiplying a range by -1 using the Paste Special > Multiply feature, you flip the sign of all numbers in the selection. This method is especially useful for users who prefer not to deal with formulas, add-ins, or VBA.
Advantages: No formulas or coding required, works instantly, efficient for small to medium ranges.
Limitations: Overwrites the original data in place, so consider making a backup if needed. Make sure your selected range contains only negative numbers to avoid unintentionally changing positive values.
1. In a blank cell anywhere on your worksheet, type -1 and copy this cell (Ctrl+C).
2. Select the range of numbers that contain negative values you wish to convert to positive. If necessary, use filters to isolate only negative numbers.
3. Right-click the selected range, choose Paste Special, and in the Paste Special dialog, select Values under Paste and Multiply under Operation.
4. Click OK. All numbers in your selected range will have their sign reversed—negative numbers become positive and positive numbers become negative. Zero values will remain unchanged.
Tip: After the conversion, delete the -1 helper cell. If you inadvertently affected positive values or zeroes, you may need to undo (Ctrl+Z) and try again using a filtered or more precisely selected range.
Note: Paste Special > Multiply modifies the original values directly, so double-check your selection before proceeding for important datasets.
Removing the leading minus sign from numbers in Excel can be accomplished using formulas, the Format Cells feature, Kutools for Excel, or alternative approaches such as VBA or Paste Special. Each technique has its own specific advantages and possible limitations, depending on your particular requirements and dataset size. Choose the method that best fits your situation for maximum productivity and accuracy. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in