I hope this question isn’t beneath the Teeming Millions…
I have a financial spreadsheet I’ve created in Excel. In one column of calculations, I’d like it to automatically make any positive number green, any negative number red and in parenthesis [ie ($200) but text is red] and any zero as black.
Is this doable in any reasonably simple way?
Easily doable, the term to search for is conditional formatting. Here is a guide from the Microsoft website.
To get the “parenthesis / red” for negative numbers, highlight the cells in Excel, right click and select “Format cells”, select currency and pick the option for how you want to display negative numbers.
To get the green format for positive numbers, highlight all the cells again and select “conditional formatting” and select “highlight cell rules” → “Greater Than…”, then ensure all values greater than zero are displayed with a custom format (green font color).
Thanks both. There was a time, back in the late 90s when I thought I was pretty good with Excel. I don’t use it nearly as much as I used to, and it’s slowly evolved to where I didn’t even see that obvious conditional formatting button.
I used to be with it, then they changed what it is, and now it seem strange and scary to me.
You don’t need conditional formatting for that. Conditional formatting can be made to work for your use case, but is unnecessarily complicated.
The unconditional formatting specifiers give you a way to specify different formatting and coloration for each of positive, negative, zero, and empty/text cells.
See here for the nitty gritty.
Something real close to this should achieve your goal:
[green]_($* #,##0.00_);[red]_($* (#,##0.00);_($* “-”??);(@_)
Note that I had to insert a bunch of backslashes in my text to make discourse not try to reformat this according to its rules for showing fancy math symbols. So you want to copy what you can see here, not what you’d see if you quote my post.
You can also experiment by inputting some example numbers into some junk cells, choosing “format cells” from the menu, then under the number or accounting or currency formatting categories, fiddling with the various checkboxes and settings and see what format string the various choices generate. Once you understand that, you can generalize from there to achieve your specific goals that exceed what their sorta-wizard interface can generate.
And I was playing with this (thanks) and if it’s not obvious: apply the conditional format rule twice on the same cell(s), once for “if less than” and zero (for red), and again for “if greater than” and zero (green). Then the paintbrush “Copy Format” will apply those rules to any other cells you choose.