In-Cell Bar Charts with REPT

All versionsREPT

REPT repeats a character N times — which means you can draw a bar chart right inside a cell. Repeat a block character proportional to a value and you get an instant, lightweight data visualization with no chart object.

Quick formula: to draw a bar proportional to the value in B2:

=REPT("|", B2)

REPT stamps out B2 bars; scale the count (e.g. B2/10) so the longest bar fits the column.

Functions used (tap for the full reference guide):

REPT ROUND

The example

Sales as in-cell bars (1 block per 20 units).

	A	B	C
1	Rep	Sales	Bar
2	Ana	120	██████
3	Ben	60	███
4	Cy	200	██████████

The formula

The in-cell bar (one block per 20 units):

=REPT("█", ROUND(B2/20, 0)) // 120 → 6 blocks

How it works

Repeat a block character proportional to the value:

B2/20 scales the value down so the biggest bar fits — here, one block per 20 units.
ROUND(…, 0) turns it into a whole number of blocks.
REPT("█", …) stamps that many block characters, drawing the bar.
Use a solid block █ (or |, ■) and a narrow font column for a clean look.

REPT vs Data Bars. Conditional-formatting Data Bars are usually prettier and auto-scale. REPT bars shine when you want the bar inside a text string, in a tooltip-like cell, or somewhere conditional formatting can’t reach — and they copy as plain text.

Try it: interactive demo

Live demo

Drag the value; watch the in-cell bar grow.

Value: 120

Bar:

Variations

Star rating

Filled and empty stars:

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

Progress bar to 100%

Scale a percentage to 10 blocks:

=REPT("█", ROUND(B2*10, 0)) & REPT("░", 10 - ROUND(B2*10,0))

Win/loss sparkline

Map outcomes to symbols with a helper or MAP.

Pitfalls & errors

REPT caps at 32,767 characters. An unscaled large value (REPT("|", 50000)) errors. Always scale the count down.

Font matters. Use a monospaced or block-friendly font so bars line up; proportional fonts make ragged bars.

Negative or fractional counts. REPT needs a non-negative whole number — ROUND and MAX(…,0) keep it valid.

Practice workbook

📊

Download the free In-Cell Bar Charts with REPT practice workbook

Sales with live REPT in-cell bars, the star-rating and progress-bar variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make an in-cell bar chart in Excel with a formula?

Use REPT to repeat a block character proportional to the value: =REPT("█", ROUND(B2/20, 0)). Scale the count so the longest bar fits the column.

How do I make a star rating with REPT?

Combine filled and empty stars: =REPT("★", B2) & REPT("☆", 5-B2) shows B2 filled stars out of 5.

When should I use REPT instead of Data Bars?

Data Bars (conditional formatting) are prettier and auto-scale. Use REPT when you need the bar inside a text string or somewhere conditional formatting can't go, and you want it to copy as plain text.

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

Function references: REPT · ROUND