While Google Sheets provides native sparklines and conditional formatting data bars, standard formatting options can occasionally feel rigid or cumbersome when building compact executive dashboards. Using the REPT function alongside Unicode block characters offers a remarkably flexible, lightweight method to render crisp in-cell data bars and progress meters without adding script overhead or sluggish visual rules.
Understanding the Mechanics of the REPT Function
The REPT function takes a given character string and repeats it a specified number of times based on an integer count. Its core syntax requires two straightforward arguments:
=REPT(text, number_of_times)When combined with solid or segmented Unicode block elements—such as the full block character █ (Unicode U+2588) or the vertical bar |—the function converts numerical metrics or completion percentages into clear horizontal bars directly inside regular cells. Because these bars are rendered as standard textual strings, their colour, font size, alignment, and display scale can be styled instantly using basic cell formatting tools.
Constructing a Basic In-Cell Progress Bar
To demonstrate the calculation, suppose cell A2 contains a task completion percentage (for example, 75% or 0.75), and you want to display an in-cell progress indicator in cell B2 based on a 10-point visual scale.
- Select cell B2 next to your percentage value.
- Enter the following formula to multiply the decimal percentage by ten and round to the nearest whole integer:
=REPT("█", ROUND(A2 * 10))When cell A2 holds 75%, ROUND(0.75 * 10) resolves to 8, prompting the formula to render eight consecutive solid block glyphs (████████). For greater granularity on wider desktop displays, simply multiply by 20, 25, or 50 to achieve a smoother, higher-resolution scale.
Building Dual-Tone Filled and Empty Progress Trackers
A frequent design limitation with single-character repeat formulas is the absence of an empty background track, making it difficult to judge the remaining shortfall at a glance. You can easily resolve this by combining two separate REPT statements inside a single formula: one for the completed portion and one for the incomplete balance.
=REPT("█", ROUND(A2 * 10)) & REPT("░", 10 - ROUND(A2 * 10))This formulation uses the light shade block ░ (Unicode U+2591) to represent remaining work. If your project is at 60% completion, the formula automatically outputs ██████░░░░, preserving a consistent 10-character width across all rows regardless of the underlying metric.
Normalising Data Against Maximum Values
When tracking varying numeric values rather than fixed 0% to 100% percentages—such as sales volumes, inventory levels, or lead counts—you must normalise each individual row against the maximum figure in your dataset.
| Metric Type | Example Formula | Visual Result Sample |
|---|---|---|
| Solid Bar (Max 20 units) | =REPT("â–ˆ", ROUND((B2 / MAX($B$2:$B$50)) * 20)) |
████████████░░░░░░░░ |
| Segmented Pipe Bar | =REPT("|", ROUND((B2 / MAX($B$2:$B$50)) * 25)) |
||||||||||||||| |
| Square Dot Matrix | =REPT("â– ", ROUND((B2 / MAX($B$2:$B$50)) * 15)) |
â– â– â– â– â– â– â– â– â– â–¡â–¡â–¡â–¡â–¡â–¡ |
By locking the maximum range reference with dollar signs ($B$2:$B$50), each row dynamically recalculates its bar proportion relative to the highest performer across your entire worksheet.
Handling Zero, Blank, and Overflow Values Gracefully
If a source cell is empty, negative, or contains non-numeric text, a raw REPT calculation may either throw a #VALUE! error or break visual symmetry. To ensure clean dashboard presentation, nest your logic inside an IF and MAX guardrail:
=IF(OR(ISBLANK(A2), A2 <= 0), "-", REPT("â–ˆ", MIN(20, MAX(1, ROUND((A2 / MAX($A$2:$A$100)) * 20)))))This protective formula ensures that negative numbers or empty records display a tidy dash, while preventing exceptionally large outlier values from stretching past your allocated column boundary.
Styling and Formatting Your In-Cell Visualisations
Because the generated progress bars are plain Unicode text, standard font and alignment controls dictate their appearance:
- Monospace Typography: Set the column font to a fixed-width typeface such as Consolas, Inconsolata, or Courier New. Monospaced characters ensure that solid blocks, light shades, and white spaces maintain identical pixel widths, keeping rows perfectly aligned.
- Font Sizing: Slightly reducing the font size to 8pt or 9pt allows for longer, higher-fidelity bars without demanding wide columns.
- Dynamic Colouring: Apply standard Google Sheets conditional formatting rules to the output column. For example, configure rules based on the source value cell so that bars automatically shift from subtle grey to vibrant green as milestones approach completion.
Implementing formula-driven Unicode data bars provides an elegant, robust alternative to bulky charts, allowing you to build dense, responsive summary reports that remain responsive across both web browsers and mobile spreadsheets.