Colored Shapes with Conditional Formatting

User Rating:  / 1

You can use Excel conditional formatting and the Wingding font to create colored shapes in a cell. In the example at right, colored shapes will appear in cells C2:C7, depending on the value in the adjacent cell in column B. If the value is less than 10, a red circle will appear, if the value is greater than 30, a green square will appear. Otherwise, a yellow diamond will appear.

• In cell C2, enter the formula:

IF(B2="","",IF(B2<10,"I",IF(B2>30,"n","t")))

• Copy the formula down to C7
• Format cells C2:C7 with the Wingding font and set the font color to yellow
• Select cells C2:C7
• Choose "Conditional Formatting", "New Rule", and "Use a formula to determine which cells to format"
• In the input box under "Format values where this formula is true:" enter: =$B2<10
• Click "Format" and select Red as the font color, then click "OK"
• Click the "New Rule" button and select "Use a formula to determine which cells to format"
• In the input box under "Format values where this formula is true:" enter: =$B2>30
• Click "Format" and select Green as the font color, then click "OK"
• Click "OK" in the "Conditional Formatting Rules Manager" window to apply the rules.