Excel REPT Function: Repeat Text a Number of Times
Repeats a piece of text as many times as you ask.
REPT repeats text. =REPT("-", 20) gives twenty hyphens. On its own that sounds like a novelty, and its two real uses are both about turning a number into something you can see at a glance.
The first is the in-cell bar chart. Repeat a block character as many times as the value, and a column of numbers becomes a column of bars that scales as the data changes — no chart object, no conditional formatting, just a formula. Divide the value first to keep the bars a sensible width.
The second is star ratings, which is the same trick with two characters: repeat a filled star for the score and an empty one for the remainder. Both patterns work in any Excel version and survive being copied into an email.
Syntax
=REPT(text, number_times)Arguments
textRequired- The string to repeat. It can be a single character or a whole word.
number_timesRequired- How many repeats. Decimals are truncated, and zero returns an empty string rather than an error.
The example data
Headers in row 1, data in A2:C5. Column B arrived as text, not numbers.
| A | B | C | |
|---|---|---|---|
| 1 | Reference | Amount (text) | Score |
| 2 | INV-1041 | 1240.50 | 4 |
| 3 | INV-1042 | 385.00 | 2 |
| 4 | INV-1043 | 2100.75 | 5 |
| 5 | INV-1044 | 940.20 | 3 |
Worked examples
=REPT("█", C2)Result: ████
The in-cell bar chart. Four blocks for a score of 4, growing and shrinking with the value.
=REPT("★", C4) & REPT("☆", 5 - C4)Result: ★★★★★
A five-star rating: filled stars for the score, empty ones for the rest, joined with &.
=REPT("-", 20)Result: --------------------
A separator line, sized once and reused.
=A2 & REPT(" ", 12 - LEN(A2)) & B2Result: INV-1041 1240.50
Padding to a fixed width, which lines up columns in a monospaced export or a plain-text email.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The result would exceed 32,767 characters, which is the maximum a cell can hold.
How to fix it: Scale the repeat count down. A bar chart divided by a sensible factor never comes close to this.
Negative padding produces an error
Why it happens: If LEN(text) is longer than the target width, the subtraction goes negative and REPT rejects it.
How to fix it: Wrap it in MAX: REPT(" ", MAX(0, 12 - LEN(A2))).
Bars are wildly too long
Why it happens: The value is being used directly as the repeat count.
How to fix it: Divide first: =REPT("█", ROUND(B2/100, 0)) keeps the width readable.
Tips worth knowing
- Set the cell font to a monospaced one so bars and padded columns line up exactly.
- Use ROUND on the repeat count, since REPT truncates decimals and a bar chart looks better rounded.
- REPT(" ", n) is the standard way to pad text to a fixed width for a plain-text export.
- For real charts use a chart. REPT bars are for when a chart is overkill or unavailable.
Frequently asked questions
How do I make an in-cell bar chart?
=REPT("█", ROUND(B2/scale, 0)), where scale keeps the longest bar to a sensible number of characters. Set the font to something monospaced and the bars line up perfectly down the column.
How do I build a star rating?
=REPT("★", B2) & REPT("☆", 5 - B2) gives a five-star display where B2 is the score. The two REPTs handle the filled and empty portions and & joins them.
Why does REPT return #VALUE!?
Either the result would exceed the 32,767-character cell limit, or the repeat count is negative — usually from a subtraction that went below zero when padding text. Wrap the count in MAX(0, …) to guard it.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.