Forum Discussion
currency format Excel 360
I highlight a column and click on the format for "currency" and choose options for use 2 decimal places, show dollar sign, then for negative number, no minus sign, no red. Aside from the currency format tab, I choose to format the column in red and bolded using the "home" button.
The problem is this format is inconsistently applied. Sometimes it shows a leading minus sign, sometimes it shows a hidden minus sign, and sometimes there is no minus sign at all. This causes me problems when I try to total a column by highlighting it and looking on the lower right for the value. Why is it doing this and how can i stop it?
1 Reply
The inconsistency usually means the column has mixed formats, or some entries are stored as text. Formatting changes appearance only; it does not change whether a number is negative or how the status-bar Sum is calculated.
Select the data range, choose Home > Clear > Clear Formats, press Ctrl+1, choose Custom, and apply:
$#,##0.00;$#,##0.00;$0.00
The three sections control positive, negative, and zero values. A negative remains negative internally but displays without a minus sign. Reapply red font and bold styling afterward.
Check suspicious cells in the formula bar. An apostrophe, warning indicator, or inconsistent alignment can mean the value is text; convert those cells to numbers.
The status-bar Sum uses the underlying values, so hidden negatives still reduce the total. If you want negatives visually obvious, use parentheses instead: $#,##0.00;($#,##0.00);$0.00. If you need a total of absolute amounts instead of a signed total, use SUMPRODUCT with ABS over the range.
