Excel's conditional formatting data bars look professional but carry a hidden cost. They slow down large workbooks, create "rule rot" when copied across sheets, and offer no way to change colours based on custom conditions. The REPT function solves all three problems at once. It repeats any character a specified number of times, producing a lightweight text string that behaves exactly like a visual bar. Consequently, it is faster, simpler to maintain, and fully compatible with conditional formatting rules that change colour based on your own logic.
Beyond progress bars, REPT also creates star ratings, dot plots, text padding, tally marks, and self-updating dashboard labels — all from a single two-argument function.
What Is the REPT Syntax?
REPT takes just two arguments — what to repeat and how many times.
| Argument | Required? | What it does |
|---|---|---|
| text | Required | The character or string to repeat. Can be any text — a single character like "|", a Unicode symbol inserted with UNICHAR(), or a text string like "AB". |
| number_times | Required | How many times to repeat the text. If this is a decimal, it is truncated to an integer (not rounded). If it is 0, REPT returns an empty string. The total result cannot exceed 32,767 characters. |
=REPT("█", ROUND(B2*20, 0)).
Which Characters Work Best?
The visual quality of an in-cell chart depends entirely on which character you choose. Some characters produce solid-looking bars. Others create rating symbols or dot plots. The table below lists the most useful options along with the UNICHAR code to insert each one.
| Character | Code | Best for |
|---|---|---|
| | | CHAR(124) | Thin bars — classic style, available on every keyboard. Use Playbill or Britannic Bold font to merge them into a solid bar. |
| █ | UNICHAR(9608) | Solid block bars — the most visually convincing bar character. No special font required. |
| ░ | UNICHAR(9617) | Empty/remaining segment — pair with █ to show filled vs unfilled portions of a progress bar. |
| ● | UNICHAR(9679) | Filled dot — modern dot plot style. Clean and readable at small sizes. |
| ○ | UNICHAR(9675) | Empty dot — pair with ● for a dot-based progress indicator. |
| ★ | UNICHAR(9733) | Filled star — ideal for 1–5 or 1–10 star ratings. |
| ☆ | UNICHAR(9734) | Empty star — pair with ★ to show unfilled rating positions. |
| ♦ | UNICHAR(9670) | Diamond — compact alternative to stars for rating scales. |
Example 1: Simple In-Cell Bar Chart
The most fundamental use is mapping a numeric value directly to a bar length. REPT repeats the bar character as many times as the value. The result is a proportional visual bar that updates automatically when the source data changes.
Example 2: Scaled Bar — Normalise Any Range to a Fixed Width
Raw values are rarely between 1 and 50 — the natural bar width. Scaling normalises any value range into a fixed bar length. Dividing each value by the maximum and multiplying by the target width (typically 20 or 30 characters) produces proportional bars regardless of the data range.
Example 3: Filled and Empty Progress Bar for Percentages
Combining two REPT calls — one for the filled portion and one for the remaining portion — creates a progress bar with a clear visual boundary. The filled segment grows as the percentage increases. The empty segment shrinks proportionally. Adding a percentage label at the end makes the bar fully self-documenting.
Example 4: Star Rating Display
Star ratings are one of the most visually recognisable data formats. REPT reproduces them natively — filled stars for the achieved rating and empty stars for the remaining positions. The pattern works for any scale: 1–5, 1–10, or any other range.
Example 5: Conditional Bar with Colour — REPT + Conditional Formatting
REPT produces text, and conditional formatting applies colour to text. Together they produce traffic-light bars — green for on-target values, amber for warning, red for critical. This combination is specifically what REPT achieves that standard data bars cannot: rule-based colour logic tied to actual thresholds rather than gradient shading.
Example 6: Text Padding for Aligned Output
REPT with spaces is useful for right-aligning labels in monospaced-font columns or for adding visual spacing in dashboard rows. Padding each label to a fixed total width ensures columns stay aligned even when the source strings have different lengths.
Example 7: Tally Chart and Repeat Counter
REPT makes excellent tally marks for counts and frequency displays. Repeating a mark character by a count value produces a visual tally that is instantly readable. This is particularly useful for count data in dashboards where space is limited and a full chart is unnecessary.
Example 8: REPT Without MATCH — Find the Last Text Entry
REPT has a useful edge-case trick that goes beyond visual effects. By repeating "z" 255 times, you create a text string that sorts to the absolute end of any alphabetical list. VLOOKUP with approximate match then finds the last text entry in a column. This pattern is a classic workaround for locating the final non-blank text value.
Common REPT Issues and How to Fix Them
Bar shows fewer segments than expected
REPT truncates decimal values without rounding. If number_times is 7.9, the bar shows 7 segments, not 8. Wrap the calculation in ROUND(): =REPT("█", ROUND(B2*20, 0)). This ensures you get the nearest whole number of segments rather than the truncated value.
Bar looks gapped or uneven in pipe-character style
The "|" character has natural whitespace around it in most standard fonts. Consequently, repeated pipes do not form a solid bar — they appear as separate strokes with gaps. Fix this by changing the cell font to Playbill, Britannic Bold, or Stencil. Alternatively, switch to the solid block character (█) which needs no special font.
#VALUE! error
REPT returns #VALUE! when number_times is negative or when the total result would exceed 32,767 characters. Check that the value driving the repeat count cannot go negative. Additionally, for large datasets, confirm that the scale multiplier does not produce a character count above 32,767 for any single row.
Frequently Asked Questions
-
What does the REPT function do in Excel?REPT repeats a text string a specified number of times. The syntax is =REPT(text, number_times). For example, =REPT("█", 10) returns "██████████" — ten solid block characters. This makes it ideal for creating in-cell bar charts, progress bars, star ratings, tally marks, and text padding — all without any chart objects or complex conditional formatting.
-
Why does my REPT bar show fewer segments than I expect?REPT truncates decimal values instead of rounding them. If number_times evaluates to 7.9, REPT produces 7 segments — not 8. The fix is to wrap the calculation in ROUND(): =REPT("█", ROUND(B2*20, 0)). This converts the calculated length to the nearest whole number before REPT uses it, giving you the expected segment count.
-
How do I make REPT bars change colour based on the value?Apply conditional formatting to the REPT bar column using the "Format cells based on a formula" rule type. Reference the source value column (not the bar column) in the formula. For example, to make bars green when the source is above 75%, set the rule formula to =B2>=0.75 and apply a green font colour. Add additional rules for amber and red thresholds. The bar character stays the same — only the font colour changes. This is precisely the behaviour that standard data bars cannot replicate.
More Questions About REPT
-
What is the best character to use for REPT progress bars?The solid block character █ (UNICHAR(9608)) is the most visually solid choice — it produces a seamless filled bar without requiring a special font. For a two-part bar that shows filled and remaining segments, pair it with the light shade character ░ (UNICHAR(9617)). The pipe character | is a classic alternative, but it needs a font like Playbill or Britannic Bold to eliminate the gaps between repetitions.
-
Is REPT faster than conditional formatting data bars?Yes. Excel treats REPT output as plain text, which is significantly lighter than graphical conditional formatting rules. Data bars require Excel to render a gradient graphic in each cell and recalculate whenever source data changes. REPT bars are just text strings — they recalculate with the normal formula engine and produce no rendering overhead. In workbooks with thousands of rows, switching from data bars to REPT can noticeably reduce file size and recalculation time.
-
Which Excel versions support REPT?REPT is available in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, and 2016. It also works identically in Google Sheets. UNICHAR(), used for inserting Unicode characters like █ and ★ into REPT formulas, is available in Excel 2013 and later. For Excel 2010 and earlier, copy-paste the Unicode character directly into the formula string instead of using UNICHAR().