# How to repeat a character n times in Excel

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

**Excel:** `=REPT("★",B2)&REPT("☆",5-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Product | Rating | Stars |
| Desk lamp | 4 |  |
| Office chair | 5 |  |
| Monitor arm | 3 |  |
| USB-C dock | 2 |  |
| Standing desk | 4 |  |

Formula in C2, filled to C6: `=REPT("★",B2)&REPT("☆",5-B2)`

Result: ★★★★☆; ★★★★★; ★★★☆☆; ★★☆☆☆; ★★★★☆

## 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.

## Other ways

- `=REPT("★",B2)`: Only the filled stars. ("★★★★" instead of "★★★★☆".)
- `=REPT("|",B2*4)`: A 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)`.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/repeat-a-character-n-times