The Excel REPT function does exactly one thing — repeats a piece of text a given number of times — and that one trick is surprisingly mighty. Tie the repeat count to a number and every cell becomes a tiny bar chart, a star rating, or a progress meter that updates with the data. It also pads IDs with leading zeros and builds dot leaders, all in plain text that works in every version of Excel.
| per thousand. Format the cell in a monospace font (Consolas, Courier New) and the bars line up like a chart.
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text to repeat — a single character or a whole string. |
number_times | Required | How many times to repeat it. 0 returns empty text; decimals are truncated (2.9 → 2); negative numbers return #VALUE!. |
Available in: every version of Excel. The result is always text, capped at 32,767 characters — ask for more and you get #VALUE!.
In-cell bar charts
The classic REPT trick: repeat a character once per unit of value and you get a bar chart with no chart object, no conditional formatting, nothing to maintain. Here each | stands for 1,000 in sales:
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | =REPT("|",B2/1000) |
| 2 | North | 12,000 | |||||||||||| |
| 3 | South | 7,000 | ||||||| |
| 4 | East | 15,000 | ||||||||||||||| |
| 5 | West | 4,000 | |||| |
Swap the character for different looks — a solid block reads like a real chart:
Or pair the bar with its number in one cell:
Try it: interactive REPT demo
Pick a character and drag the count — watch the bar grow exactly as it would in a cell.
Leading zeros, star ratings, and dot leaders
Pad IDs to a fixed width. Need every product code to be 6 digits, zeros in front? Repeat exactly as many zeros as are missing:
(=TEXT(A2,"000000") does the same for pure numbers — the REPT version also works when codes mix letters and digits.)
Star ratings. Filled stars for the score, hollow stars for the rest — a 5-star widget in one formula:
Dot leaders, like a table of contents. Repeat dots to fill the gap between a label and its page number:
Two-sided charts. Right-align a REPT bar in one column and left-align another beside it for a tornado / win-loss view:
Errors & common pitfalls
#VALUE! — negative count or result too long. number_times can’t be negative, and the repeated string can’t exceed 32,767 characters. Guard noisy data with MAX: =REPT("|", MAX(0, B2/1000)).
Pitfall: decimals are truncated, not rounded. =REPT("|", 2.9) draws 2 bars, not 3. If you want normal rounding, wrap the count: =REPT("|", ROUND(B2/1000, 0)).
Pitfall: bars don’t line up. In proportional fonts like Calibri, ten pipes in one cell aren’t the same width as ten in another context. Set the bar column to a monospace font — Consolas or Courier New — and the chart snaps into alignment.
Pitfall: the output is text, not numbers. =REPT("0",3) returns the string “000”. Zero-padded IDs built with REPT won’t match true numbers in lookups — pad both sides, or convert with VALUE first.
Pitfall: number_times = 0 returns “”, not an error. Empty text looks like a blank cell but isn’t one — ISBLANK says FALSE and COUNTA still counts it. Handy for hiding zero bars; surprising if you test for blanks downstream.
Practice workbook
Frequently asked questions
How do I make an in-cell bar chart with REPT?
=REPT("|", B2/1000) draws one bar per 1,000. Put the formula beside the numbers, set that column to a monospace font like Consolas, and the bars align into a chart that updates with the data.How do I add leading zeros with REPT?
=REPT("0", 6-LEN(A2)) & A2 pads any value to 6 characters. For pure numbers, =TEXT(A2,"000000") is simpler — the REPT version wins when codes mix letters and digits.How do I make a star rating in Excel?
=REPT("★", B2) & REPT("☆", 5-B2) shows B2 filled stars out of five. Insert the star characters via Insert › Symbol or copy them from anywhere.Why does REPT return #VALUE!?
number_times is negative, or the result would exceed 32,767 characters. Clamp the count to safe territory: =REPT("|", MIN(200, MAX(0, B2))).Does REPT round the number_times argument?
=REPT("|", 2.9) gives 2 bars. Wrap the count in ROUND if you want 2.9 to become 3: =REPT("|", ROUND(B2,0)).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class