Show progress toward a goal as a bar that fills up — no chart object needed. REPT draws a block bar sized to the percent complete, capped at 100%, right inside a cell.
The example
$8,000 of a $10,000 goal — 80%.
| A | B | |
|---|---|---|
| 1 | Metric | Value |
| 2 | Actual | $8,000 |
| 3 | Goal | $10,000 |
| 4 | Progress | ████████░░ 80% |
The formula
A text bar plus the percent:
How it works
REPT repeats a block character to length:
- Compute progress:
actual / goal. REPT("█", fraction × 20)draws that fraction of a 20-block bar.MIN(fraction, 1)caps the bar at full so going over goal doesn’t overflow.- Append
TEXT(fraction, "0%")for the exact percent. Use a monospace font so the blocks align.
Two-tone bar: draw the filled part with █ and the remainder with a light block: =REPT("█", pct*20) & REPT("░", (1-pct)*20). Or skip REPT entirely and use a conditional-formatting data bar for a smooth fill.
Try it: interactive demo
Set actual and goal.
Variations
Two-tone bar
Filled + remainder:
CF data bar instead
Smooth fill:
Over-goal flag
Celebrate exceeding:
Pitfalls & errors
Cap with MIN. Without MIN(…,1), exceeding the goal draws a bar longer than the cell.
Use a monospace font. Proportional fonts make the blocks uneven; Consolas/Courier keeps the bar tidy.
REPT max is 32,767 chars. Keep the multiplier small (a 20-block bar is plenty); huge counts error.
Practice workbook
Frequently asked questions
How do I show percent of goal as a bar in Excel?
How do I make a two-tone progress bar?
Should I use REPT or a conditional-formatting data bar?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class