REPT Function in Excel (6 Examples)

If you want to repeat a piece of text a set number of times in Excel, the REPT function does exactly that. You hand it some text and a number, and it gives you back that text repeated as many times as you asked.

What makes REPT genuinely useful is what you can build with it. In-cell bar charts, star ratings, and progress bars, all without inserting a single chart.

In Excel 365, you can also feed REPT a range of numbers and the repeated text spills into the cells below.

In this article, I’ll show you how to use REPT with practical examples, from a simple repeat to bar charts you can color by target, star ratings, and progress bars right inside your cells.

REPT Function Syntax

Here is the syntax of the REPT function:

=REPT(text, number_times)

• text – the text you want to repeat. This can be a single character, a word, or a cell reference.
• number_times – a positive number telling REPT how many times to repeat the text. If it isn’t a whole number, REPT drops the decimal part.

Pro Tip: If number_times is 0, REPT returns an empty string. That’s handy for bars that should disappear when a value hits zero.

When to Use REPT

Use the REPT function when you need to:

• Draw an in-cell bar chart without inserting an actual chart
• Turn a score into a row of filled and empty stars
• Show progress as a bar of completed and remaining segments
• Pad numbers or text to a fixed width so they line up

REPT works in Excel for Microsoft 365, Excel 2024, 2021, 2019, 2016, and older versions, plus Excel on the web. The spilling examples below (one formula across a whole range) need Microsoft 365 or Excel 2021.

Let me show you a few practical examples of how to use this function.

Example 1: Repeat Text a Set Number of Times

Let’s start with the simplest use of REPT.

Below I have a small habit tracker. Column A has the habit, and column B has the number of days I completed it this week.

I want to show each habit’s progress as a row of check marks, one check for every day it was done.

Here is the formula:

=REPT("✔",B2:B6)

REPT takes the check mark in the first argument and repeats it as many times as the number in column B.

Because I fed it the whole range B2:B6 at once, the results spill down the column on their own. So Read, with 6 days, gets six check marks, and Meditate, with 3, gets three.

Pro Tip: The text you repeat doesn’t have to be a single character. =REPT("Go! ",3) returns “Go! Go! Go! “, repeating the whole word and the space each time.

Example 2: Create an In-Cell Bar Chart With REPT

Now for the use REPT is famous for. A bar chart that lives right inside a cell.

Below I have monthly website visitors. Column A has the month and column B has the number of visitors.

I want a horizontal bar next to each month, scaled so the busiest month fills about 20 characters and the rest are proportional to it.

Here is the formula:

=REPT("█",B2:B6/MAX(B2:B6)*20)

How this formula works:

• B2:B6/MAX(B2:B6) turns each month into a fraction of the busiest month. The biggest value becomes 1, and everything else is somewhere below it.
• Multiplying by 20 scales that fraction into a number of blocks, so the busiest month works out to a full 20 blocks.
• REPT then draws that many block characters. March, the busiest month here, gets the full bar, and the quieter months get shorter ones.

REPT drops any decimal on the block count, so every bar is a whole number of blocks.

Pro Tip: Set the result cells to a monospace font like Consolas or Courier New. In those fonts every character is the same width, so the bars line up cleanly. In a normal font they come out ragged.

Example 3: Color the In-Cell Bar Chart With Conditional Formatting

The bar chart above is fine, but every bar comes out the same color. A quick way to make it more useful is to color each bar by whether it hit a target.

Below I have units sold by each sales rep for the month. Column A has the rep and column B has the units they sold.

I want a bar next to each rep, green when they hit the target of 50 units and red when they fall short.

Here is the formula for the bars:

=REPT("█",B2:B7/5)

Dividing by 5 keeps the bars a sensible length, so the top rep gets a bar of about 16 blocks instead of over 80. Right now every bar is the default font color.

To color them, I’ll use two conditional formatting rules, one for the reps at or above the target and one for those below it.

Here are the steps to color the bars with conditional formatting:

1. Select the cells that hold the bars.

2. On the Home tab, click Conditional Formatting, then click New Rule.

3. Choose “Use a formula to determine which cells to format”, then enter =B2>=50 in the formula box.

4. Click Format, set the font color to green, then click OK on both dialog boxes.

5. Add a second rule the same way, this time with the formula =B2<50 and a red font, to color the reps who missed the target.

Each rule checks the units in column B and colors the bar cell next to it. Reps at 50 or more get a green bar, and anyone below 50 turns red.

Pro Tip: For an even more solid-looking bar, type the pipe character instead of the block, like =REPT("|",B2/5), and set those cells to the Stencil font. In Stencil the pipes render as fat, connected slabs that read as one continuous bar.

Example 4: Build a Star Rating With REPT

REPT is also the easiest way to turn a score into a row of stars.

Below I have feedback scores for a few training courses. Column A has the course and column B has its rating out of 5.

I want to show each rating as five stars, filled in up to the score and empty after it.

Here is the formula:

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

How this formula works:

• REPT(“★”,B2:B6) draws one filled star for each point in the score.
• REPT(“☆”,5-B2:B6) fills the rest of the way up to 5 with empty stars.
• The & joins the two pieces, so a score of 4 shows four filled stars and one empty one.

Pro Tip: This works cleanly with whole-number ratings. For a decimal score like 3.5, wrap it in ROUND first, like ROUND(B2,0), since REPT drops the decimal on its own and can throw the empty-star count off.

Example 5: Make a Progress Bar With REPT

A progress bar is the same idea with two characters. One for the part that’s done and one for the part that’s left.

Below I have a list of projects with how far along each one is. Column A has the project and column B has the percent complete.

I want a 10-segment bar for each project, filled for the finished portion and hollow for the rest.

Here is the formula:

=REPT("■",ROUND(B2:B6*10,0))&REPT("□",10-ROUND(B2:B6*10,0))

How this formula works:

• Multiplying B2:B6 by 10 turns a percentage like 75% into a number of segments, in this case 7.5 out of 10.
• ROUND(…,0) rounds that to a whole segment so the filled and empty halves always add up to 10.
• The first REPT fills the finished segments, the second fills the rest with hollow squares, and & joins them into one bar.

The ROUND is doing real work here. Without it, REPT would chop 7.5 down to 7 and 2.5 down to 2, and your bar would come up a segment short.

Example 6: Pad Numbers to a Fixed Width

Finally, REPT is handy for padding numbers to a fixed width so a column of them lines up.

Below I have raw invoice numbers in column A. They’re different lengths, and I want every one to show as a six-digit ID.

I want each number padded with leading zeros, so a short number like 7 becomes 000007.

Here is the formula:

=REPT("0",6-LEN(A2:A6))&A2:A6

How this formula works:

• LEN(A2:A6) counts the digits in each number.
• 6-LEN(…) works out how many zeros are missing to reach six digits.
• REPT adds that many zeros in front, and & sticks the original number on the end.

Keep in mind the result is text, not a number. Use it for display and keep your real values in the original column.

The TEXT function can pad numbers the same way, but REPT paired with LEN also works on alphanumeric IDs.

If a number is already six digits or longer, 6-LEN goes negative and REPT returns a #VALUE! error. Set the width to at least your longest number.

Pro Tip: To right-align numbers in a report instead of adding zeros, repeat spaces instead, like =REPT(" ",8-LEN(A2))&A2, and set the column to a monospace font so they line up.

Tips & Common Mistakes

• The result can’t be longer than 32,767 characters. Ask REPT to repeat text past that limit and it returns a #VALUE! error instead of the string.
• number_times can’t be negative. A negative repeat count gives a #VALUE! error, which is why the padding formula above breaks if the width is smaller than the value.
• REPT drops decimals. A number_times of 4.9 repeats the text 4 times, not 5. When you need the nearest whole number, wrap the count in ROUND first.
• Use a monospace font for bars and progress meters. Block and bar characters only line up when every character is the same width, so pick a font like Consolas or Courier New for those cells.
• The result is always text. A REPT output can’t be summed or used in math. It’s built for display, so keep your real numbers in a separate column.

Wrapping Up

REPT looks simple, but it’s one of the more fun functions to have in your kit. Once you see it as a way to draw with characters, in-cell bar charts, star ratings, and progress bars all become quick one-formula jobs.

I hope you found this tutorial helpful.

List of All Excel Functions

Other Excel Articles You May Also Like:

• Excel Custom Number Formatting Tricks
• CONCATENATE Function in Excel (5 Examples)