Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formatting won / loss records

Copper Contributor

Hi there.  I'm looking to format won / loss records in Excel.  For instance, if column D2 is 64-59 (a winning record), I'd like to color it green.  If column D2 = 36-48, I'd like to color it red.  Column D is formatted as text.  If wins are twice as many or more than losses (e.g. D2 = 99-31), I'd like to color it purple.  Thanks in advance for any help you can provide.  

2 Replies

@grassland7964 

Let's say you want to apply this to D2:D100.

Select this range. D2 should be the active cell in the selection.

 

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

 

=--LEFT(D2,FIND("-",D2)-1)<--MID(D2,FIND("-",D2)+1,10)

 

Click Format...
Activate the Fill tab.
Select red as color.
Click OK, then click OK again.

 

Repeat these steps, but with the formula

 

=--LEFT(D2,FIND("-",D2)-1)>--MID(D2,FIND("-",D2)+1,10)

 

and green as color.

 

Finally, repeat them again with the formula

 

=--LEFT(D2,FIND("-",D2)-1)>=2*MID(D2,FIND("-",D2)+1,10)

 

and purple as color.

 

Please note that draws will not be colored.

Thanks so much, I really appreciate it!