Negative Numbers in Parentheses in Excel (and Fixing Text)

Quick Answer: To show negative numbers in parentheses in Excel, select the cells, press Ctrl+1, choose Custom and enter #,##0.00_);(#,##0.00). The cell keeps its negative value; only the display changes. If your amounts arrived as text such as (1,234.56) or 1,234.56 CR, formatting will not help: convert them first with a formula that strips the brackets or the CR and multiplies by -1.

Two different problems hide behind this search. One is cosmetic: you have real negative numbers and want accountants' brackets around them. The other is a data problem: you pasted or imported a bank statement and the negatives came in as text with brackets, a trailing CR or a trailing minus, so Excel cannot add them up. This guide fixes both, in that order.

How do you display negative numbers in parentheses in Excel?

Excel decides how a number looks through its number format, and a custom format can have up to four parts separated by semicolons. Microsoft's Number format codes page describes them as positive;negative;zero;text, in that order. So to bracket negatives you write one pattern for positives, then the same pattern wrapped in parentheses for negatives.

1. Select the cells or the whole column.
2. Open Format Cells. Microsoft lists three ways: press Ctrl+1, right-click and choose Format Cells, or use the dialog launcher in the Number group on the Home tab.
3. In the Category list choose Custom.
4. In the Type box, replace whatever is there with one of the codes below and click OK.

The _) in the second row is worth understanding. An underscore followed by a character tells Excel to leave a blank space as wide as that character. Microsoft's own example is exactly this case: put _) at the end of the positive section so positive numbers line up with negatives that are enclosed in parentheses. Without it, the last digit of a positive amount sits one character further right than the last digit of a bracketed one, and a column of figures looks ragged.

You can also skip typing codes. The Number and Currency categories in Format Cells let you pick how negatives are displayed from a short list, and in the US English locale that list includes a bracketed option. Which options appear depends on your Windows or macOS regional settings, so the custom code is the reliable route if you share workbooks across countries.

What the format does not do

A number format never changes the stored value. A cell showing (45.00) still contains -45, so =SUM(), sorting, filters and pivot tables all treat it as negative. That is what you want. The flip side is the trap in the next section: typing the characters (45.00) into a cell that was formatted as Text stores a string, and no format will turn that string back into a number.

Why does Excel treat my bank statement amounts as text?

Copying a table out of a PDF, or importing a CSV some banks produce, often gives you amounts like these:

• (1,234.56) in brackets, the accounting convention for a debit.
• 1,234.56 CR or 1,234.56 DR, common on UK, Indian and Australian statements and on many credit card statements.
• 1,234.56- with a trailing minus, which older core banking systems print.
• $1,234.56 with a currency symbol, or a stray non-breaking space from the PDF.

Excel's signs that something is wrong: the values sit on the left edge of the cell, a small green triangle appears in the corner, and a SUM over the column returns 0 or a figure that is too small. The Microsoft page on numbers stored as text covers the simple cases: select the cells and choose Convert to Number from the error indicator, or use =VALUE() in a helper column and paste the results back with Paste Special, Values. If the triangle never appears, it notes that background error checking can be switched on under File, Options, Formulas.

Those built-in fixes are fine for a plain number that happens to be text. They are unreliable for a trailing CR or a trailing minus, so for statement data it is safer to say exactly what you want.

Formulas that convert statement text into signed numbers

Put the raw amounts in column B starting at B2 and write the formula in C2, then fill down. Each formula trims spaces, removes thousands separators and currency symbols, and applies the sign.

Brackets mean negative:

=IF(LEFT(TRIM(B2),1)="(", -1, 1) * VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"(",""),")",""),",",""),"$",""))

Trailing CR or DR (credit positive, debit negative):

=LET(t, UPPER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))), n, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,"CR",""),"DR",""),",",""),"$","")), IF(RIGHT(t,2)="DR", -n, n))

Trailing minus:

=IF(RIGHT(TRIM(B2),1)="-", -VALUE(SUBSTITUTE(LEFT(TRIM(B2),LEN(TRIM(B2))-1),",","")), VALUE(SUBSTITUTE(TRIM(B2),",","")))

A few notes so these behave on real files:

• LET exists in Excel for Microsoft 365 and Excel 2021 and later. On an older version, repeat the cleaned expression instead of naming it t.
• CHAR(160) is the non-breaking space PDFs like to insert. TRIM alone does not remove it.
• If your workbook uses a comma as the decimal separator, SUBSTITUTE(..., ",", "") will wreck the values. Use NUMBERVALUE(text, ",", ".") instead, which Microsoft documents as converting text to a number in a locale-independent way with the separators you name. Its documentation says nothing about brackets, so strip those yourself first.
• When the column mixes formats, nest the tests: check for a bracket, then for CR/DR, then for a trailing minus.

Which sign should CR get?

It depends on whose point of view the statement takes. On a checking or savings statement, CR is a deposit, so it should be positive and DR negative. On a credit card statement the bank is recording what you owe: purchases increase the balance and a CR line is a payment or refund. If you are tracking spending, you probably want purchases negative and CR lines positive, which is what the formula above gives you when purchases carry DR. If purchases carry no marker at all, flip the logic: treat unmarked amounts as negative and CR amounts as positive. Decide once per account and write it at the top of the sheet.

How do you check the conversion worked?

Do not trust a converted column until it ties back to the statement.

1. Sum the converted column with =SUM(C2:C200).
2. Take the statement's closing balance minus its opening balance.
3. The two should be equal. If they differ by exactly twice one amount, that row got the wrong sign.
4. Filter column C for errors. #VALUE! usually means a stray character such as a footnote marker or a currency code like USD.

Once the numbers are real, apply the parentheses format from the first section to column C, and the sheet reads the way an accountant expects while still calculating correctly.

Avoiding the problem at the source

The cleanest fix is to never get text amounts at all. Many banks let you download transactions as CSV directly from online banking, and those files usually carry a plain minus sign. When all you have is a PDF, Excel's own Get Data from PDF can work on simple layouts, but it imports whatever characters the PDF prints, brackets included.

A dedicated converter is another route. QuickBankConvert reads statement PDFs and writes one row per transaction with a signed amount, money out negative, so the column arrives as numbers you can format with brackets straight away. You can preview a statement on the convert page without an account, and a free account downloads Excel or CSV. If you also need to fix dates or split columns, our CSV formatting fixes and data cleanup tips pick up where this guide stops.

Related guides

• Convert PDF to Excel without losing formatting
• Fix bank statement CSV formatting issues
• PDF to Excel for bank statements