Spreadsheet Practice

How to repeat a character n times in Excel

Formula (Excel and Google Sheets)

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

Use =REPT(text,number). For a 5-star rating: =REPT("★",B2)&REPT("☆",5-B2).

Result on the example
one result per row in C2, filled down to C6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

How it works

REPT repeats a piece of text a given number of times. The filled stars repeat B2 times and the empty ones 5-B2 times, so every row has exactly five stars and the ratings are easy to scan.

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

Result"★★★★☆"

  1. B24

    The value in B2.

  2. REPT("★",B2)"★★★★"

    REPT: Repeats text.

    • text"★"
    • number_timesB24
  3. 5-B21

    The - operator subtracts.

  4. REPT("☆",5-B2)"☆"

    REPT: Repeats text.

    • text"☆"
    • number_times5-B21
  5. REPT("★",B2)&REPT("☆",5-B2)"★★★★☆"

    The & operator joins as text.

In Google Sheets

The same formula works in Google Sheets: =REPT("★",B2)&REPT("☆",5-B2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=REPT("★",B2)★★★★, ★★★★★, ★★★…Different resultOnly the filled stars. "★★★★" instead of "★★★★☆".
=REPT("|",B2*4)||||||||||||||||, ||||||||||||||||||||, ||||||||||||…Different resultA simple in-cell bar chart: longer bars for higher values. Bars of | characters, 4 per point.

Common mistakes

  • REPT drops decimals: a 4.6 rating shows 4 stars. Round first with ROUND(B2,0).
  • A negative count returns #VALUE!: a rating above 5 makes 5-B2 negative. Cap it with MIN(B2,5).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Repeat a character n times

In C2, show the rating as stars: one filled star per point and empty stars up to 5. Fill down to C6.

Questions

How do I make an in-cell bar chart scaled to the largest value?

Scale to a width of 20: =REPT("█",ROUND(B2/MAX($B$2:$B$6)*20,0)).

How do I repeat a word with a space between?

Include the space and trim: =TRIM(REPT("ha ",3)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.