How to make negative numbers positive in Excel

Method 1: ABS in a helper column

The sheet is a month of business bank transactions. The bank shows money out as negative numbers in column D. Your expense report wants spending as positive amounts, so put =ABS(D2) in a new column and fill it down.

ABS (absolute value) strips the minus sign and leaves positives alone, so you never turn a refund into a negative by mistake. It is the safest choice when the direction does not matter. In Excel 365, =ABS(D2:D7) spills the whole column at once.

Once the helper column is right, you can copy it and use Paste Special > Values over the original if you want to drop the formulas.

Method 2: multiply by -1 (formula or Paste Special)

=-D2 or =D2*-1 flips the sign: negatives become positive and positives become negative. Use it when every value in the range is the wrong way round, for example an export where all costs arrived as negatives and there are no credits mixed in. On a mixed column it turns the 1,250 client payment into -1,250, which is why ABS is usually the better pick.

To flip numbers in place without a helper column, use Paste Special. It works on typed values; for cells that already hold formulas, edit the formula instead (put a minus in front or wrap it in ABS).

1. Type -1 in any empty cell and press Ctrl+C to copy it.
2. Select the cells you want to flip, for example D2:D7.
3. Go to Home > Paste > Paste Special (or press Ctrl+Alt+V).
4. Under Paste choose Values, so the -1 cell's formatting is not copied too. Under Operation choose Multiply. Click OK.
5. Delete the -1 cell.

Flip only some rows: use the Type column

Column F is a hand-typed log of the same transactions. Most amounts were typed as positives, but someone entered the AWS payment as -312.40. A plain SUM of F gives 1,096.29, while the real net movement is 871.31.

When a Debit/Credit column tells you the direction, let it set the sign and ignore whatever sign was typed. Wrapping the amount in ABS first makes the formula give the same answer whether the cell holds 312.40 or -312.40.

To make only negatives positive and leave positives untouched, that is just ABS. To keep money out and money in apart, use two columns: =MAX(-D2,0) gives the debit as a positive (0 for credits) and =MAX(D2,0) gives the credit.

Total only the negative (or positive) numbers

To total spending without a helper column, sum only the values below zero with SUMIF and a "<0" criterion. The result is negative, because you added negatives; put a minus in front, or wrap it in ABS, to report it as a positive total.

Use ">0" for the money in: =SUMIF(D2:D7,">0") gives 1,296.20. To add a second condition, switch to SUMIFS: =-SUMIFS(D2:D7,D2:D7,"<0",B2:B7,"Card payment*") gives 412.89 of card spending.

=SUM(ABS(D2:D7)) adds the sizes of all the numbers (1,721.09 here). That is rarely what you want for money, but it is useful for things like total absolute error. In Excel 2019 and older it needs Ctrl+Shift+Enter; =SUMPRODUCT(ABS(D2:D7)) works in every version.

Show negatives in brackets (the value stays negative)

Accountants often want -84.50 to appear as (84.50). That is a number format, not a change to the value. Select the cells, press Ctrl+1, choose Custom and type #,##0.00;(#,##0.00). The part before the semicolon formats positives and the part after formats negatives. The built-in Number and Currency formats also offer bracket and red options for negatives.

A format such as #,##0.00;#,##0.00 hides the minus sign completely. Be careful: the cell still holds -84.50, so SUM, IF and charts treat it as negative. If a sheet you were sent looks positive but adds up wrongly, click a cell and check the formula bar.

To build the bracketed version as text for a label or an email, use TEXT with the same format. The result is text, so keep the real numbers elsewhere for maths.

Bank exports with a trailing minus ("84.50-")

Some banks and older accounting systems put the minus sign after the number, and the file often arrives as text. Column E shows that export. Excel sees "84.50-" as text, so SUM skips it and ABS cannot help.

Test for the minus sign yourself instead of relying on how Excel happens to read the text: if the last character is "-", take everything before it, turn it into a number and make it negative. Otherwise convert the text as it is. In Excel 365, point the formula at E2:E7 and it spills the whole column, which sums to 871.31.

Importing a fresh file? Data > Text to Columns can do it without a formula: select the column, choose Delimited, click Next twice, click Advanced and make sure "Trailing minus for negative numbers" is ticked, then Finish.