How to Use Conditional Formatting with Custom Icon Sets in Google Sheets

Standard conditional formatting in Google Sheets allows you to change a cell’s background colour or text colour based on its value. However, when you are building a dashboard or a performance report, colour changes alone can be difficult to interpret at a glance. By combining conditional formatting rules with Unicode symbols and the CHAR function, you can create a visual icon set system that displays arrows, traffic lights, or checkmarks directly inside cells based on numerical performance thresholds. This technique transforms a plain data table into an instantly readable visual report.

How the Icon Set Technique Works

Google Sheets does not offer a built-in “icon set” conditional formatting option like Microsoft Excel does. Instead, you can achieve the same result by using a helper column that outputs a Unicode character based on a formula. You then apply standard conditional formatting to colour that character green, amber, or red depending on the value it represents. The result is a clean, professional icon set that updates dynamically as your underlying data changes.

Preparing Your Spreadsheet

For this example, assume you have a list of sales representatives in column A and their quarterly sales figures in column B. You want column C to display a visual icon: a green upward arrow for figures above the target, an amber diamond for figures near the target, and a red downward arrow for figures below the target.

1. Open your Google Sheets spreadsheet.
2. Click on cell C1 and type the header Status.

Creating the Icon Formula

The CHAR function in Google Sheets outputs a character corresponding to a Unicode code point. We will use it inside an IF statement to output different arrow symbols based on the sales value.

1. Click on cell C2.
2. Enter the following formula: =IF(B2>=10000,CHAR(9650),IF(B2>=5000,CHAR(9670),CHAR(9660)))
3. Press Enter.

This formula checks three conditions: if the sales value in B2 is 10,000 or above, it outputs an upward-pointing triangle (▲). If the value is between 5,000 and 9,999, it outputs a diamond (◆). If the value is below 5,000, it outputs a downward-pointing triangle (▼). Drag the formula down through the rest of column C to apply it to all rows.

Applying Colour-Coded Conditional Formatting

The symbols are now visible, but they are all the same colour. To create a true traffic-light icon set, you need to apply conditional formatting rules.

1. Select the entire range of icon cells in column C (for example, C2:C100).
2. Click Format in the top menu bar, then select Conditional formatting.
3. In the conditional formatting sidebar, set Format cells if to Text contains.
4. In the text field, paste the upward triangle character: ▲ (copy it from any cell displaying it).
5. Under Formatting style, set the text colour to a strong green (such as hex #34A853).
6. Click Done.
7. Click Add another rule. Set it to Text contains and paste the diamond character ◆. Set the text colour to amber or orange (such as hex #FBBC04).
8. Click Done.
9. Click Add another rule. Set it to Text contains and paste the downward triangle ▼. Set the text colour to red (such as hex #EA4335).
10. Click Done.

Refining the Visual Presentation

To achieve a polished look, increase the font size of column C to 14 or 16 points so the icons are clearly visible. You can also centre-align the column and set its width to a narrow value so it acts purely as a status indicator next to the data. If you want to hide the helper column entirely and display the icon inside the data cell itself, you can embed the CHAR formula directly within the original cell’s content using a concatenation formula, but keeping it in a separate column provides cleaner maintenance.

Customising with Alternative Icon Sets

You are not limited to triangles and diamonds. Google Sheets supports a wide range of Unicode characters through the CHAR function. For a checkmark and cross system, use CHAR(10004) for a heavy checkmark (✔) and CHAR(10008) for a heavy ballot cross (✘). For a star rating system, use CHAR(9733) for a filled star (★) and CHAR(9734) for an empty star (☆). Experiment with different symbols to match the visual language of your specific report or dashboard.