4. Create a Dynamic Star Rating Display Using Conditional Formatting

Why a Formula Alone Is Not Enough

In the previous step the scorecard already contains a numeric rating column (1–5) produced by the threshold lookup. That number is correct but hard to scan: a reviewer comparing twenty vehicles has to read digits and mentally rank them. Conditional formatting solves this by turning the rating value into a visual glyph — filled stars, colored icons, or a data bar — while leaving the underlying number intact for sorting, pivoting, and aggregation. The key constraint is that the display must be dynamic: when an injury score changes, the stars must change with it, with no manual repainting.

Two mechanisms are available in Excel, and they behave differently enough that the choice matters:

• Icon sets — a built-in rule type that maps numeric ranges to a small icon. Fast to apply, but limited to the icon families Excel ships with (3, 4, or 5 symbols), and the icons are fixed-size glyphs, not true stars.
• Custom rules with a star character — a formula-based rule that writes a repeated character (★) into a helper column, or a font-color rule that hides/shows star glyphs. Fully controllable, works with any rating scale, and prints reliably.

For a 5-star scorecard the custom-rule approach is usually the better fit, because the rating scale is exactly five and reviewers expect literal stars. The icon-set approach is worth knowing as a fallback when the workbook must avoid special characters (for example, when it is exported to a system that mangles Unicode).

Building the Star String with REPT

The cleanest dynamic display uses a helper column that concatenates filled and empty stars from the rating value. Assume the rating sits in F2 and the scorecard table runs from row 2 downward. In G2:

=REPT("★",F2)&REPT("☆",5-F2)

REPT repeats a text string a given number of times. With F2 = 4 the result is ★★★★☆; with F2 = 1 it is ★☆☆☆☆. Because the formula references F2, any change to the injury score that flows through the rating formula immediately repaints the stars. Fill G2 down the table and the whole column stays live.

Two practical details matter here. First, the star characters must be entered as literal Unicode glyphs (U+2605 and U+2606); typing them from the keyboard or inserting via Insert > Symbol both work, but copying from a web page can introduce a different code point that renders inconsistently. Second, set the helper column font to a typeface that contains both glyphs — Segoe UI Symbol, Arial Unicode MS, or Calibri all cover them; a font that lacks ☆ will show a box.

Applying Conditional Formatting to the Rating Column

The star string is a display convenience, but the rating column itself should also carry a visual cue so that the number and the stars agree at a glance. Select F2:F100 (or the full data range), then Home > Conditional Formatting > New Rule > Format only cells that contain. Create five rules, one per rating value, each setting a fill color:

Order the rules so that the most severe (1) is evaluated first if you later switch to "Stop If True"; with plain "equal to" rules the order does not affect the result because the values are mutually exclusive. Use Manage Rules to confirm the range reference is absolute and covers the whole table, not just the active cell.

Alternative: Icon Sets on the Rating Column

If the workbook must stay free of special characters, apply an icon set instead. Select the rating range, choose Conditional Formatting > Icon Sets > 5 Symbols, then Manage Rules > Edit Rule and set the thresholds to >= 5, >= 4, >= 3, >= 2, with the last band covering 1. Tick Show Icon Only if the numeric value should be hidden — but keep in mind that hiding the number removes the sortable value from view, which is usually undesirable in a scorecard that gets sorted by rating.

How the Pieces Fit Together

The diagram below shows the data path from the raw injury score through the rating formula to the two display layers, and the point at which each conditional-formatting mechanism attaches.

Verification and Common Failures

To confirm the display is genuinely dynamic, change one injury score in the input column and watch three things: the rating value in F, the fill color on that cell, and the star string in G. All three must update in the same recalculation. If the stars change but the fill does not, the conditional-formatting range is wrong — check that it covers the row you edited. If the fill changes but the stars do not, the helper formula is probably referencing a hard-coded cell rather than the row's own rating.

Typical symptoms and their causes:

• Stars show as boxes or question marks — the cell font lacks U+2605/U+2606. Switch to Segoe UI Symbol or Arial Unicode MS.
• Star count is wrong by one — the rating formula returns a value outside 1–5, or REPT is receiving a text value. Coerce with VALUE(F2) if the rating column is stored as text.
• Icon set shows no icons — the rule was applied to the text star column instead of the numeric rating column.
• Colors disappear after sorting — the rule range was relative and shifted with the sort. Re-anchor the range with absolute references.

With the display layer in place, the scorecard now reads correctly at a glance and remains fully computational underneath — the foundation for aggregating ratings by vehicle type in the next step.