Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Text

REPT

HERHALING in Dutch Excel

Repeats a text a given number of times and returns the result as one text.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Audit area	Risk level	Risk
2	Revenue	55	●●●●●=REPT("●", B2)=REPT("●"; B2)
3	Payroll costs	33
4	Housing costs	11
5	Trade receivables	44
6	Inventory	22

Result: ●●●●●

=REPT("●", B2)

=REPT("●"; B2)

What REPT does and when to use it

What it does: it repeats a text a set number of times, for example a block ten times to draw a small bar next to a figure.

When to use it: when the number of repeats comes from your data: a score as stars, an amount as a bar, an ID padded with zeros to the length an import file demands. For a fixed number of leading zeros on a number, TEXT with the code 000000 is shorter.

How Excel evaluates it

Excel reads text and converts it to text if it is a number. It reads number_times and cuts off any decimals. Then:

a negative count stops with #VALUE!
a count of 0 returns an empty text
any other count writes the text that many times, with nothing in between, and checks that the result fits in 32,767 characters

The result is always text, even when it consists of digits.

Watch out: REPT cuts off the decimals of the count instead of rounding: 8.7 draws 8 blocks. Wrap the count in ROUND(x, 0) if 8.7 should draw 9.

Mental model

A rubber stamp pressed a counted number of times along a line, each print touching the last one.

Syntax

Official REPT documentation

REPT(text, number_times)

REPT(text; number_times)

Arguments
Argument	Type	Meaning
`text`	Required	The character or text to repeat, in quotes or from a cell.
`number_times`	Required	How many times; decimals are cut off, 0 gives empty text, below 0 is an error.

Return value:Text: text repeated number_times times

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Risk levels per audit area as dots

In the audit plan for Veldhuis Installatie BV you score the risk of material misstatement per audit area from 1 (low) to 5 (high). In the overview for the team, the scores should be visible at a glance as a row of dots next to each area.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Audit area	Risk level	Risk
2	Revenue	55	●●●●●=REPT("●", B2)=REPT("●"; B2)
3	Payroll costs	33
4	Housing costs	11
5	Trade receivables	44
6	Inventory	22
7

Formula cell (C2)Input

Formula in C2

=REPT("●", B2)

=REPT("●"; B2)

Result: ●●●●●

Step by step

Excel reads the first argument, the dot character in quotes. That is text.
It reads B2 for number_times and finds 5, a whole number, so nothing is cut off.
It writes the dot 5 times in a row, with nothing in between: ●●●●●. C2 shows five dots for revenue.
Copy C2 down: C3 shows 3 dots, C4 shows 1, C5 shows 4 and C6 shows 2. The length of each row follows the risk level.
Change B4 to 0 and C4 becomes empty: REPT with 0 returns an empty text, not an error.

Watch out: If the scores are averages over several assessors, such as 3.8, REPT draws 3 dots, not 4, because it truncates. Round the score first if 3.8 should look like a 4.

More on this example: why it works, 1 variation

Why it works

number_times comes from the cell, so each row draws its own number of dots and the column works as a small chart. The dots are real text characters, which means they copy into the audit plan or an e-mail as they are.

Variations

=REPT("●", B2) & REPT("○", 5 - B2)

=REPT("●"; B2) & REPT("○"; 5 - B2)

Fills the rest of the five places with open dots, so every row is 5 characters wide: ●●●○○.

Intermediate

In-cell bars for the receivables aging

You have the aging list of trade receivables at 31 December 2025, with one total per band. Next to each amount you want a quick visual of where the balance sits, without building a chart. One block stands for 10,00010.000 euro, and that scale sits in a cell so you can change it.

Sheet excerpt: C2 holds the formula
Row	A	B	C	E
1	Aging band	Balance	Bar	Amount per block
2	Not yet due	€118,300.00€ 118.300,00	▇▇▇▇▇▇▇▇▇▇▇▇=REPT("▇", ROUND(B2 / $E$2, 0))=REPT("▇"; ROUND(B2 / $E$2; 0))	1000010000
3	1 to 30 days overdue	€87,400.00€ 87.400,00
4	31 to 60 days overdue	€42,500.00€ 42.500,00
5	61 to 90 days overdue	€15,000.00€ 15.000,00
6	Over 90 days overdue	€3,200.00€ 3.200,00
7

Formula cell (C2)Input

Formula in C2

=REPT("▇", ROUND(B2 / $E$2, 0))

=REPT("▇"; ROUND(B2 / $E$2; 0))

Result: ▇▇▇▇▇▇▇▇▇▇▇▇

Step by step

Excel works from the inside out. B2 / $E$2 is 118,300118.300 / 10,00010.000 = 11.83.
ROUND(11.83, 0) rounds to the nearest whole number: 12.
REPT writes the block 12 times: ▇▇▇▇▇▇▇▇▇▇▇▇. C2 shows a bar of 12 blocks.
Without ROUND, REPT would receive 11.83 and cut it to 11, so the bar for 118,300118.300 would be as long as the bar for 110,000110.000. ROUND is what makes it look closer to 120,000120.000.
Copy C2 down:
- C3: 87,40087.400 / 10,00010.000 = 8.74, rounded 9 blocks
- C4: 42,50042.500 / 10,00010.000 = 4.25, rounded 4
- C5: 15,00015.000 / 10,00010.000 = 1.5, rounded half away from zero to 2
- C6: 3,2003.200 / 10,00010.000 = 0.32, rounded 0, so C6 is empty rather than an error
Set E2 to 20,00020.000 and every bar roughly halves: C2 becomes ROUND(5.915, 0) = 6 blocks. The $ signs keep all five rows pointing at E2.

Watch out: Without the $ in $E$2, copying down shifts the scale to E3, E4 and so on, which are empty. Dividing by an empty cell gives #DIV/0!, and every bar below the first turns into an error.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Dividing by a scale turns euros into a block count that fits in a column, and ROUND chooses the nearest count instead of letting REPT always round down. The balance of the oldest band is small, so the bar also shows how little of the receivable is at risk of being uncollectable.

More to watch out for

The band over 90 days, 3,2003.200 euro, rounds to 0 blocks and looks the same as a band with nothing in it. That is the band you look at hardest for credit losses, so show the amount next to the bar.

Variations

=REPT("▇", B2 / $E$2)

=REPT("▇"; B2 / $E$2)

Without ROUND the bars always round down; acceptable when the scale is fine enough that one block does not matter.

Advanced

Fixed-width match keys for an invoice test

Your data analysis tool matches open invoices between the subledger and the general ledger from a fixed-width text file: the invoice number as exactly 6 digits with leading zeros, directly followed by the customer name as exactly 12 characters, padded with spaces or cut off. You build each line in the sheet and save the column as a text file.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Invoice no.	Customer	Match key
2	8282	Brinkman BV	000082Brinkman BV=REPT("0", 6 - LEN(A2)) & A2 & LEFT(B2 & REPT(" ", 12), 12)=REPT("0"; 6 - LEN(A2)) & A2 & LEFT(B2 & REPT(" "; 12); 12)
3	44174417	Bakkerij Smit
4	13051305	Okafor IT
5	2727	Hendriks Groothandel
6	950950	Mol & Zn
7

Formula cell (C2)InputMarks: extra space

Formula in C2

=REPT("0", 6 - LEN(A2)) & A2 & LEFT(B2 & REPT(" ", 12), 12)

=REPT("0"; 6 - LEN(A2)) & A2 & LEFT(B2 & REPT(" "; 12); 12)

Result: 000082Brinkman BV

Step by step

Excel starts with the number part. LEN(A2) converts 82 to the text 82 and counts 2 characters. 6 - 2 = 4.
REPT("0", 4) returns 0000. Joined with A2 that gives 000082, exactly 6 characters.
Then the name part, inside out. REPT(" ", 12) returns 12 spaces. B2 & those spaces is Brinkman BV followed by 12 spaces: 11 + 12 = 23 characters.
LEFT(..., 12) keeps the first 12 of those 23: Brinkman BV plus 1 space. Adding more spaces than needed and then cutting is what makes every name exactly 12 wide.
The two parts are joined: 000082Brinkman BV followed by 1 space, 6 + 12 = 18 characters. =LEN(C2) confirms 18.
Copy down to C6:
- in C3, Bakkerij Smit has 13 characters, LEFT keeps 12, and the line reads 004417Bakkerij Smi
- in C5, LEN(27) is 2, so the number becomes 000027, and Hendriks Groothandel (20 characters) is cut to Hendriks Gro
- in C6, LEN(950) is 3, so the number becomes 000950, and Mol & Zn (8 characters) is padded with 4 spaces to 12: 000950Mol & Zn followed by 4 spaces, 18 characters, nothing cut
The tool accepts all lines, and the long names are cut without a warning.

Watch out: An invoice number with 7 digits, such as 1234567, makes 6 - 7 = -1, and REPT returns #VALUE!. That failure is loud and shows at once; the silent one is the name that gets cut, as in the Hendriks Groothandel row.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

The number is padded on the left by computing how many zeros are missing, and the name is padded on the right by overfilling with spaces and cutting back with LEFT. The second technique never produces a negative count, which is why it is used for the part that can be too long. The first technique relies on the invoice number never exceeding 6 digits.

More to watch out for

Excel shows trailing spaces as nothing, so C2 looks 17 characters long on screen. Check the width with LEN, not by eye.

Variations

=TEXT(A2, "000000") & LEFT(B2 & REPT(" ", 12), 12)

=TEXT(A2; "000000") & LEFT(B2 & REPT(" "; 12); 12)

TEXT pads the number with a format code instead; a 7-digit number then comes out as 7 digits rather than as an error.

=RIGHT(REPT("0", 6) & A2, 6) & LEFT(B2 & REPT(" ", 12), 12)

=RIGHT(REPT("0"; 6) & A2; 6) & LEFT(B2 & REPT(" "; 12); 12)

The overfill-and-cut technique on the left side too: never an error, but a 7-digit number silently loses its first digit.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

A materiality bar that survives overruns

In the audit file you show, per audit area, how much of performance materiality is used up by uncorrected misstatements: a bar of fixed width with filled and empty segments and the percentage behind it. Some areas are over performance materiality, and a net correcting entry once pushed one area below zero. The bar must not break on either, and the width sits in a cell.

Sheet excerpt: C2 holds the formula
Row	A	B	C	E
1	Audit area	Share of performance materiality	Progress	Bar width
2	Revenue	62%62%	▇▇▇▇▇▇░░░░ 62%=LET( used, B2, width, $E$2, filled, ROUND(MIN(1, MAX(0, used)) * width, 0), REPT("▇", filled) & REPT("░", width - filled) & " " & TEXT(used, "0%") )=LET( used; B2; width; $E$2; filled; ROUND(MIN(1; MAX(0; used)) * width; 0); REPT("▇"; filled) & REPT("░"; width - filled) & " " & TEXT(used; "0%") )	1010
3	Payroll costs	115%115%
4	Inventory	5%5%
5	Trade receivables	-2%-2%
6	Housing costs	97%97%
7

Formula cell (C2)Input

Formula in C2

=LET(
  used, B2,
  width, $E$2,
  filled, ROUND(MIN(1, MAX(0, used)) * width, 0),
  REPT("▇", filled) & REPT("░", width - filled) & " " & TEXT(used, "0%")
)

=LET(
  used; B2;
  width; $E$2;
  filled; ROUND(MIN(1; MAX(0; used)) * width; 0);
  REPT("▇"; filled) & REPT("░"; width - filled) & " " & TEXT(used; "0%")
)

Result: ▇▇▇▇▇▇░░░░ 62%

Step by step

LET names the inputs: used is B2, 0.62, and width is E2, 10.
filled is computed inside out. MAX(0, 0.62) is 0.62, so a negative value would be lifted to 0. MIN(1, 0.62) is 0.62, so a value above 100% would be capped at 1. Then 0.62 × 10 = 6.2, and ROUND gives 6.
REPT("▇", 6) draws 6 filled blocks. REPT("░", 10 - 6) draws 4 empty ones. Together they are always exactly width characters.
TEXT(0.62, "0%") gives 62%. With the space in between, C2 shows ▇▇▇▇▇▇░░░░ 62%.
C3 (payroll costs) shows why the clamp is there. 1.15 is capped to 1, filled is 10, and the empty part is REPT("░", 0), an empty text: ▇▇▇▇▇▇▇▇▇▇ 115%. Without MIN, filled would be ROUND(11.5, 0) = 12, 10 - 12 = -2, and the second REPT would return #VALUE!.
C5 (trade receivables): -0.02 is lifted to 0, so filled is 0 and the bar is 10 empty segments, followed by -2%. The label still tells the truth while the bar stays drawable. C4 (inventory): 0.05 × 10 = 0.5 rounds to 1 block.

Watch out: The mirror-image mistake is swapping MIN and MAX: ROUND(MAX(1, MIN(0, used)) * width, 0). MIN(0, 0.62) is 0, MAX(1, 0) is 1, so every area shows a full bar. When one area really is over performance materiality, the full bars look plausible and the one that matters does not stand out.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Two REPT calls that share one count always add up to the full width, so every bar lines up in a fixed-width font. MIN and MAX clamp the count into the only range REPT can draw, 0 to width, while the percentage label keeps the unclamped value. A negative count is an error, and an overrun in the filled part turns into a negative count in the empty part; that is where these bars usually break.

Do not use this construction for dashboards that are viewed on screen only and hold many rows: conditional formatting data bars scale smoothly, need no special font and recalculate faster. The trade-off is that data bars cannot be copied as text into a memo or combined with a label in the same cell, which is exactly what REPT bars can do.

More to watch out for

The block characters come from Unicode. In some fonts ░ and ▇ have different widths, and the bars stop lining up. Set the column to Consolas or another monospaced font.
A width of 0 or a blank E2 gives bars of nothing followed by the percentage. That is not an error, so check the width cell if every bar vanishes at once.

Variations

=REPT("▇", ROUND(MIN(1, MAX(0, B2)) * $E$2, 0)) & REPT("░", $E$2 - ROUND(MIN(1, MAX(0, B2)) * $E$2, 0))

=REPT("▇"; ROUND(MIN(1; MAX(0; B2)) * $E$2; 0)) & REPT("░"; $E$2 - ROUND(MIN(1; MAX(0; B2)) * $E$2; 0))

Without LET and without the label: works in every version, but the clamped count is written and calculated twice.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Bars slightly shorter than the numbers	A value of 8.7 draws 8 blocks, and the bars look slightly short compared with the numbers.	REPT truncates, so round the count before REPT sees it.
#VALUE! on one padded row	A padding formula such as REPT("0", 6 - LEN(A2)) & A2 returns #VALUE! on one row.	That value is longer than 6 characters, so the count went negative. Decide what should happen (cut, flag or widen) and guard the count with MAX(0, ...).
Bars in a column do not line up	The bars in one column do not line up, and some look wider than others with the same count.	Character widths depend on the font. Use a font where the block characters have a fixed width, such as Consolas, for columns of bars or padded text.

Used in combinations

Split a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later (This combination needs Excel 2024 or later)