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?
3 Replies
Hello KimC1,
If the same column is showing some negative values with a minus sign and others without, I'd first check whether all the cells actually have the same number format.Select a cell that displays correctly and one that doesn't, press Ctrl+1 on each, and compare the Number format. If they differ, select the entire column, go to Home > Clear > Clear Formats, then reapply a single format to the whole column.
If you want negative values to display without a minus sign, use this custom format:
$#,##0.00;$#,##0.00;$0.00
Keep in mind that this only changes how the values are displayed. The underlying values are still negative, so the status bar Sum and formulas like SUM() will still subtract them.
If some cells continue to behave differently after reapplying the format, check whether those cells are stored as text or whether conditional formatting is overriding the number format or font.
If you're still getting the error after pasting from Notepad, the issue is likely with the forum editor itself rather than the text.
Try this,
Select the column
Press Ctrl + 1
Choose Custom
paste the code
$#,##0.00;[Red]$#,##0.00
Click OK
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.
