REPT in Excel: Simple In-Cell Bar Charts

The Excel REPT function repeats a piece of text a set number of times, and that simple job makes it a quick way to draw bar charts inside cells. =REPT("|",B2/1000) turns a sales figure into a row of bars you can read at a glance. This guide is for anyone who wants a visual summary in a table, a progress tracker or a report without building a chart.

What does the Excel REPT function do?#

REPT returns text repeated as many times as you say.

=REPT(text, number_times)

Two rules to remember:

• Decimals are cut off. REPT keeps only the whole number part of number_times, so 3.9 gives 3 repeats. Use ROUND when you want 3.9 to count as 4.
• The result is text. It lines up with the rest of the row, but you can't add it up.

REPT works in every version of Excel, including Excel for Mac and Excel for the web.

How to make an in-cell bar chart with REPT#

Repeat a narrow character once for every step of the value. Here are five reps' sales:

In C2, type:

=REPT("|",B2/1000)

Each 1,000 in sales adds one bar. 12,000 gives 12 bars, and 18,500 gives 18 (the half is dropped). Fill down with Ctrl+D on Windows or ⌘D on a Mac, and the column becomes a simple horizontal bar chart.

Make the bars look better#

A few formatting tweaks make a big difference:

• Set the font colour of column C to a strong colour, such as blue or green.
• Left-align the column so all bars start at the same edge.
• Try other characters. A full block (█) gives solid bars; in Excel 2013 and later you can produce it with =REPT(UNICHAR(9608),B2/1000).
• Shrink the font size of the bar column if the bars get too long.

How to scale bars to a fixed width#

Divide each value by the largest one to keep the longest bar at a set length, however big the numbers are:

=REPT("|",ROUND(B2/MAX($B$2:$B$6)*20,0))

How it works:

1. B2/MAX($B$2:$B$6) gives each value as a share of the largest, from 0 to 1.
2. *20 makes the largest value 20 characters long.
3. ROUND turns the share into a whole number of bars.

For Ben, 18,500 ÷ 21,000 × 20 is 17.6, which rounds to 18 bars, while Dev, the top seller, gets the full 20. Lock the range with dollar signs before filling down, so every row compares against the same maximum. This version keeps working when sales grow from thousands to millions, while the /1000 version would start drawing hundreds of bars.

How to show the number next to the bar#

Join the bar and the value with &. The joining text guide covers & in detail.

=REPT("|",B2/1000)&" "&TEXT(B2,"#,##0")

For Ava, this shows 12 bars followed by 12,000. TEXT formats the number with a thousands separator, because joining a raw number would show 12000.

How to build a progress bar#

Join two REPTs: one for the completed part and one for the rest. With a percentage complete in B2 (for example 0.3 for 30%):

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

At 30%, this returns ■■■□□□□□□□: three filled squares and seven empty ones, always 10 in total. It works well in project trackers and task lists. Add &" "&TEXT(B2,"0%") to show the percentage after the bar.

Other uses: star ratings and padding#

REPT is useful wherever you need repeated characters:

For leading zeros, =TEXT(B2,"000000") gives the same result and is easier to read; REPT is handy when the value is already text. REPT also pairs well with REPLACE to hide part of an ID, as shown in our guide to SUBSTITUTE and REPLACE.

REPT vs Conditional Formatting Data Bars#

Excel has a built-in way to draw bars inside cells. Select the numbers, then choose Home > Conditional Formatting > Data Bars and pick a style. The bar appears behind the number in the same cell.

Use Data Bars for a polished report. Use REPT when you want the bar in its own column, need to join it with text, or want a trick that also works in a plain-text export.

Common REPT mistakes#

These are the problems people run into:

• Bars that run off the screen. The values are too big. Divide by a larger step or scale with MAX.
• #VALUE! In Excel, a negative number_times or a result over 32,767 characters returns #VALUE!. Check for negative values or wrap them in MAX(0, …).
• Uneven bars. Proportional fonts make some characters wider. Use the same character in every row.
• Forgetting the quotes. The text to repeat must be in double quotes: "|".

If you want to split text apart rather than build it up, TEXTSPLIT splits a cell into columns, and LEFT, RIGHT and MID take out part of a cell.

Practise it#

Try the bar chart exercise on this page. Then open the joining text lesson to practise &, CONCAT and TEXTJOIN in a live spreadsheet; the TEXTJOIN function guide explains its delimiter and ignore_empty options, and CONCATENATE vs CONCAT vs TEXTJOIN helps you pick the right one.