How to Change a Negative Number to Positive in Excel
Negative numbers in Excel can cause problems — wrong totals, broken formulas, charts that look off. Whether you're cleaning up imported data, correcting a ledger, or preparing a report, knowing how to flip negatives to positives is a practical skill that saves real time. Excel gives you several ways to do it, and which one fits best depends on what you're working with.
Why Negative Numbers Need to Be Converted
Sometimes a negative sign is meaningful — a loss, a debit, a temperature below zero. Other times it's a data entry error, a formatting quirk from an export, or a sign that needs to flip for a formula to work correctly. Before converting anything, it's worth knowing why the number is negative, because that affects which method is appropriate and whether you should change the raw value or just how it displays.
Method 1: Multiply by -1 Using a Formula
The most straightforward approach is a formula that multiplies the value by -1, which reverses the sign.
If A2 contains -45, this returns 45. You can drag the formula down to apply it to an entire column. This method works well when you want to keep the original data intact and use the converted values elsewhere in your workbook.
A variation using the ABS function (absolute value) achieves the same result and is arguably cleaner:
ABS always returns the non-negative version of a number, regardless of whether it started positive or negative. This makes it useful when your data is inconsistent — some values negative, some positive — and you want all of them positive without checking each one manually.
Method 2: Paste Special — Multiply
If you want to convert the original cells directly (not create a separate column of results), Paste Special is the tool for the job.
Here's how it works:
- Type -1 in an empty cell and copy it.
- Select the range of negative numbers you want to convert.
- Right-click → Paste Special → choose Multiply under the Operation section → click OK.
Excel multiplies each selected cell by -1, flipping the sign in place. The formula values are gone — you're left with plain numbers. This is a destructive edit, so it's worth keeping a backup if the original data matters.
This method works across large ranges quickly and doesn't require helper columns.
Method 3: Using Flash Fill (Excel 365 / Excel 2019+)
Flash Fill can sometimes handle sign changes automatically if Excel detects the pattern. It's not always reliable for this specific task, but in simpler datasets it can work:
- In a column next to your data, manually type the positive version of the first negative number.
- Start typing the second — Excel may suggest the rest of the column.
- Press Enter to accept.
Flash Fill is better suited for text transformations, so for pure number sign changes, the formula or Paste Special methods are more dependable.
Method 4: Conditional Formatting vs. Actual Value Change 🔢
A common point of confusion: displaying a number as positive is not the same as changing it to positive.
You can use a custom number format to make -45 appear as 45 on screen without changing the underlying cell value:
- Select the cells.
- Right-click → Format Cells → Number tab → Custom.
- Enter the format code: 0;0;0
This tells Excel to display all numbers — positive, negative, and zero — using the positive format. But the value in the cell is still -45. Any formula referencing that cell will still use the negative value. This approach is useful for display and printing but will not fix formula errors caused by negative inputs.
Knowing whether you need to change the value or just the appearance is one of the key variables that shapes which method is appropriate.
Method 5: IF Formula for Selective Conversion
Sometimes you only want to convert numbers that meet a condition — for example, only flip negatives and leave positives alone. An IF formula handles this precisely:
This checks whether A2 is negative. If it is, it returns the positive version. If not, it returns the original value unchanged. Combined with ABS, this becomes even simpler:
Both approaches produce the same result when the goal is "make everything non-negative."
Comparing the Methods 📊
| Method | Changes Original Cell | Works on Large Ranges | Keeps Formulas | Best For |
|---|---|---|---|---|
| Formula (×-1 or ABS) | No | Yes | Yes | Separate output column |
| Paste Special Multiply | Yes | Yes | No | In-place conversion |
| Custom Number Format | No | Yes | N/A | Display only |
| IF Formula | No | Yes | Yes | Selective conversion |
| Flash Fill | Sometimes | Limited | No | Simple, small datasets |
Variables That Affect Which Method Fits
Several factors shape the right choice:
- Whether the original data needs to stay intact — if it does, formulas and helper columns are safer than Paste Special.
- Data volume — for hundreds or thousands of rows, Paste Special or a dragged formula scales better than manual edits.
- Excel version — Flash Fill requires Excel 2013 or later; some features behave slightly differently in Excel for Mac vs. Windows.
- Downstream formulas — if other cells reference the negatives, changing values in place with Paste Special will immediately affect those dependent formulas, which may or may not be what you want.
- Mixed data — if your range has both positive and negative numbers, ABS handles both without requiring a conditional check, while Paste Special multiply would flip positives to negatives as a side effect.
The right method isn't always the fastest one — it depends on what the data is doing in your workbook and what happens to it next. ✅