To split a column of amounts into positive and negative columns, such as receipts and payments in a bank statement, use =MAX(B3,0) and =MIN(B3,0), an IF formula that leaves blanks, or FILTER for two separate lists. Power Query does it for large data.
Table of Contents
1. MAX and MIN
MAX of an amount and 0 keeps positive amounts and turns negatives into 0. MIN does the opposite:
=MAX(B3,0)
This is the shortest method, and the columns total correctly. To hide the zeros, see the tip below.
How the formula works
MAX(B3,0)compares the amount with 0 and keeps the bigger one, so negatives become 0.IF(B3>0,B3,"")tests the sign and returns the amount or nothing.FILTER(B3:B10,B3:B10>0)keeps only the rows where the test is TRUE.
2. IF, leaving blanks
IF returns the amount when it matches, and an empty text "" otherwise:
=IF(B3>0,B3,"")
Zero goes in neither column. Use >=0 if zeros should count as positive. SUM ignores the blank text, so totals still work.
3. FILTER into two lists (Excel 365)
FILTER gives two short lists with no gaps, which update when the amounts change:
=FILTER(B3:B10,B3:B10>0)
4 positive and 3 negative amounts. Wrap them in SORT to order them: =SORT(FILTER(B3:B10,B3:B10>0),,-1).
Totals, signs and display
| To | Formula or setting |
|---|---|
| Total of positives | =SUMIF(B3:B10,">0") |
| Total of negatives | =SUMIF(B3:B10,"<0") |
| Count of negatives | =COUNTIF(B3:B10,"<0") |
| Show negatives as positive | =ABS(B3) |
| Hide zeros in method 1 | Custom format #,##0;-#,##0; (the empty last part hides zeros) |
| Show negatives in red | Custom format #,##0;[Red]-#,##0 |
4. Power Query
| Step | Do this |
|---|---|
| 1 | Select the data and go to Data → From Table/Range. |
| 2 | Add Column → Conditional Column: name it Positive; if Amount is greater than 0, output the Amount column, else leave blank. |
| 3 | Repeat for Negative with is less than 0. |
| 4 | Home → Close & Load. Click Refresh when new data arrives. |
Notes
- Bank statements from PDF often show amounts as text with Dr or Cr. Convert them first; see the PDF to Excel tool.
- To colour positive and negative cells instead of splitting them, see the highlight negative and positive values macro.
- The FILTER method returns
#CALC!if there are no negative numbers. Add a third argument:=FILTER(B3:B10,B3:B10<0,"None").
Common errors
| Problem | Cause | Fix |
|---|---|---|
| All amounts go to Positive | Amounts are text, not numbers | Convert with VALUE or Text to Columns |
#CALC! |
FILTER found nothing | Add "None" as the third argument |
| Totals wrong with IF | "" replaced by 0 later |
Keep ""; SUM ignores it |
Practice file
A bank-style list of amounts with all three formula methods and the totals.