In Excel there are several ways to dynamically color a full cell based off of its value, but is there any way to dynamically color only part of the cell based on its value?
For example, say I am building a report that looks something like the following:
_________________________
| | Dec | Nov |
|_______|___________|_____|
|Gross R| $75 (-25%)| $100|
|_______|___________|_____|
|Net Inc| $55 (+10%)| $50 |
|_______|___________|_____|
In this scenario I only wish to color the percentage values (-25%) and (+10%), not the dollar values $75 and $55 which are also in the cell. Adding to the problem is the coloring should be dynamic (green for positive, red for negative values), and these cells are references (so manual coloring is off the table).
I have tried using the built in TEXT()
function, but that did not work either. Specifically I tried =TEXT(A1,"$##")&" "&TEXT(A2,"[Green]0%;[Red](-0%)")
where A1
is the cell reference to the dollar amount and A2
is the cell reference to the percentage delta.
The frustrating thing is that the custom formatting [Green]0%;[Red](-0%)
works just fine when applied to the entire cell (via the custom number formatting section), but when applied via the TEXT()
function it stops working. So, how can I custom color a partial value within a cell?