Excel REPT Function

REPT repeats a piece of text a specific number of times and joins the copies into one string. Type =REPT("x", 5) and Excel returns xxxxx. It sounds almost too simple to matter, but REPT quietly powers zero-padded IDs, in-cell bar charts, and star ratings, all without a single chart object or custom format code.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and all earlier versions with a Ribbon interface. REPT has been part of Excel since it was still competing with Lotus 1-2-3, so compatibility is never a concern.

Syntax

=REPT(text, number_times)

Parameter	Required	Description
`text`	Yes	The character or string to repeat. Can be a literal in quotes, a cell reference, or the result of another formula.
`number_times`	Yes	How many copies of `text` to string together. Must be zero or a positive number. Decimals get truncated to a whole number before repeating.

⚠

REPT throws #VALUE! if number_times is negative. It doesn't round negatives up to zero or ignore them, it just fails. Wrap the argument in MAX(0, number_times) if there's any chance the value could go negative.

Basic Example

You're building a product review sheet and want a visual star rating instead of a plain number in column C.

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

// C2       = the numeric rating, from 0 to 5
// "★"      = filled star, repeated once per rating point
// "☆"      = empty star, filling out the remainder to 5

If C2 holds 4, the formula returns ★★★★☆. REPT builds each half of the string separately, the filled stars and the empty ones, and the & operator glues them together into a single readable rating. No conditional formatting, no icon sets, just text.

How REPT Works

It repeats the text argument exactly, character for character

Whatever you put in text, REPT copies it as a unit. =REPT("ab", 3) returns ababab, not aaabbb. If you pass a multi-character string, the whole string repeats as a block.

number_times must be zero or positive

A value of 0 returns an empty string "", not an error. Negative values are where REPT breaks. This is different from a lot of Excel math functions that quietly treat negatives as zero.

Decimals get truncated, not rounded

=REPT("*", 4.9) returns four asterisks, not five. Excel drops everything after the decimal point rather than rounding to the nearest whole number. If your number_times comes from a calculated ratio, expect this truncation to shave off the fractional remainder.

There's a 32,767 character ceiling

REPT can't produce a result longer than 32,767 characters. Push past that (say, repeating a 10-character string 4,000 times) and the formula returns #VALUE! instead of a truncated string.

The result is text, not a number

Even if you repeat a digit, like =REPT("7", 3), the output "777" is a text string. You can't add it, average it, or feed it into SUM without converting it first with something like VALUE().

Common Use Cases

Zero-pad invoice or account numbers

You're formatting account numbers so they all display with seven digits, with leading zeros filling the gap.

=REPT("0", 7 - LEN(B2)) & B2

// B2   = the raw account number, e.g. 358
// LEN  = counts the existing digits so REPT only adds what's missing

If B2 is 358, this returns 0000358. LEN does the heavy lifting here: it tells REPT exactly how many zeros are still needed so the total length always comes out to seven.

Build an in-cell bar chart

You want a quick visual comparison of monthly sales totals without inserting an actual chart.

=REPT("█", ROUND(D2/750, 0))   // one block per $750 in sales

A $12,400 sales figure produces a bar of roughly 17 blocks. Drag this down a column of monthly totals and you get an instant, self-scaling bar chart made entirely of text. It updates the moment the underlying number changes, no chart refresh required.

Create visual separators in reports

You're formatting a printed summary and want a clean divider line between sections.

=REPT("-", 62)   // a 62-character dashed line matching the report's print width

This beats manually typing dashes, and if you ever need a wider or narrower line, changing one number does it.

Flag values that exceed a threshold

You want a quick visual warning next to any region that missed its quarterly target more than once.

=REPT("!", F2)   // F2 = number of missed targets, e.g. 3 returns "!!!"

Combine this with IF to only show the exclamation marks when a threshold is crossed, keeping clean rows uncluttered.

Handling Errors

REPT throws #VALUE! in two situations: when number_times is negative, or when the resulting string would exceed 32,767 characters. Both are catchable.

Common causes of #VALUE!:

number_times calculated from a formula that can go negative (a subtraction like 5 - C2 where C2 exceeds 5)
A repeat count large enough to blow past the character limit
number_times pointing to a cell containing text instead of a number

=IFERROR(REPT("★", 5 - C2), "Invalid rating")

✓

If your number_times comes from a subtraction that could go negative, like the star rating example above, wrap it in MAX(0, ...) instead of relying on IFERROR. =REPT("☆", MAX(0, 5 - C2)) prevents the error from happening at all rather than catching it after the fact.

Notes & Gotchas

Why does REPT return #VALUE!?

The most common cause is a negative number_times, often from a subtraction formula that produces a negative result when the input exceeds expectations. The second cause is exceeding the 32,767 character limit on the output string. Check both before assuming the formula itself is broken.

Does REPT round number_times to the nearest whole number?

No. REPT truncates, it doesn't round. =REPT("x", 3.9) returns three x's, not four, because Excel drops the decimal rather than rounding up. If you need rounding behavior, wrap the argument in ROUND() first.

Does REPT work in older versions of Excel?

Yes. Unlike TEXTJOIN or FILTER, REPT isn't a newer addition. It works identically in Excel 365, 2021, 2019, 2016, and every version going back decades, so a workbook built with REPT will open and calculate correctly no matter which Excel version opens it.

Can REPT replace an actual chart?

For quick, single-column visual comparisons, yes. It won't replace a real chart for anything with axes, legends, or multiple series, but for a fast in-cell bar built from a block character, REPT is faster to set up than inserting and formatting a chart object.

Why does my repeated text look inconsistent across columns?

REPT results are left-aligned by default because they're text, not numbers. If bars or padded values look uneven, check your column's alignment setting rather than the formula itself.

What happens if text is an empty string?

=REPT("", 10) returns an empty string regardless of number_times. There's nothing to repeat, so the result is always "". This is a quiet way REPT can return blank when you expected output, worth checking if a formula depending on REPT's result seems to disappear.

Related Functions

Function	Use this when...
`TEXT`	You want to format a number with a fixed pattern instead of manually padding it with REPT.
`LEN`	You need to measure existing text length before calculating how much REPT should add.
`IF`	You want REPT to only display output when a condition is met, like flagging values above a threshold.