REPT function in Google Sheets: repeat text, pad values and draw in-cell bars
REPT repeats a text a given number of times. It is the function behind in-cell bar charts, star ratings and padded codes. This guide covers the syntax, bars and ratings drawn with symbols, padding to a fixed width, repeating a value down a column, and the limits of the function, including the 32,000 character cap and what happens with zero, negative and decimal counts.
REPT repeats a text a given number of times:
=REPT("ab", 3)
returns “ababab”. The text can come from a cell and the count from another, which is what makes the function useful: the result gets longer as a number gets larger.
REPT syntax
=REPT(text_to_repeat, number_of_repetitions)
- text_to_repeat: the text, a number or a cell reference.
- number_of_repetitions: how many times to repeat it.
=REPT(C3, 3)
Nothing is put between the repetitions. For a separator, make it part of the text: =REPT(C3 & ", ", 3).
With both arguments from cells:
=REPT(B3, C3)
How REPT treats the count
From our test sheet:
| Count | Result |
|---|---|
| 0 | An empty text |
| A decimal, 2.9 | Cut to 2: “xx” |
| A negative number | #VALUE! |
| A text | #VALUE! |
| TRUE or FALSE | 1 or 0 repetitions |
The result is always text: =REPT(5, 3) returns “555” as a text, not the number 555.
The length limit
The result cannot be longer than 32,000 characters. In our test, 32,000 repetitions of one character worked, and 32,767 returned #VALUE!. The limit applies to the length of the result, so “ab” repeated 20,000 times fails as well.
In-cell bar charts
Repeating a symbol as many times as a value draws a bar inside the cell:
=REPT(CHAR(10074), D3)
CHAR returns a symbol by its code; a typed symbol such as “|” or “■” works the same way.
Large values need scaling, or the bar runs off the screen. Divide by a step:
=REPT("|", ROUND(D3 / 500))
In our test, an amount of 1,500 gave three bars. To scale a whole column to a width of 20 characters, relate each value to the maximum:
=REPT("|", ROUND(D3 / MAX($D$3:$D$12) * 20))
For one formula over the column: =ARRAYFORMULA(REPT("|", ROUND(D3:D12 / 500))).
A bar made of text can be colored with the font color and with conditional formatting. For a drawn chart in a cell, with real bars, lines and colors, use SPARKLINE.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Ratings and progress
Stars for a quantity
=REPT(CHAR(127775), D16)
A rating out of five
Filled stars for the score and empty ones for the rest:
=REPT("★", C3) & REPT("☆", 5 - C3)
With 3 in C3 the result is ★★★☆☆.
All or nothing
=IF(D16>3000, REPT("★", 5), REPT("☆", 5))
A mark for each completed step
Count the steps that are done and repeat a symbol that many times:
=REPT("*", COUNTIF(D3:H3, "Y"))
See COUNTIF.
Show a text only when a condition is true
A comparison returns TRUE or FALSE, which REPT reads as 1 or 0:
=REPT("Yes", C3 > 200)
It is a compact form of =IF(C3 > 200, "Yes", ""). The IF version is easier for others to read.
Pad a value to a fixed width
Leading zeros
=REPT("0", 6 - LEN(B3)) & B3
With 42 in B3 the result is “000042”: LEN measures the value, and REPT supplies the missing zeros. For numbers, =TEXT(B3, "000000") returns the same and is shorter; the REPT form also works for text codes and for any filling character. If the value is longer than the width, the count becomes negative and the formula returns #VALUE!; guard it with MAX(0, 6 - LEN(B3)).
Trailing characters
=C3 & REPT("_", 24 - LEN(C3))
Fixed-width text of this kind is needed for some file formats and for lining up labels in a monospaced font.
Repeat a value down a column
REPT returns one cell. To get one row per repetition, repeat the value with a separator, split it and turn the result:
=TRANSPOSE(SPLIT(REPT(C3 & "♕", D3), "♕"))
The separator has to be a character that does not occur in the data, which is why the example uses an unusual symbol. See how to split cells.
A divider line
=REPT("-", 40)
draws a line of forty hyphens to separate blocks of a report without borders.
Errors in REPT
⚠️ Error: #VALUE! with a count that looks fine.
✅ Solution: The count is negative, usually from a subtraction such as 6 - LEN(B3) on a long value, or it is a text. Wrap the count in MAX(0, …), and check that the cell holds a number.
⚠️ Error: #VALUE! on a large count.
✅ Solution: The result would be longer than 32,000 characters. Scale the count down.
⚠️ Error: The bar is shorter than expected.
✅ Solution: Decimals are cut, not rounded: 2.9 gives two repetitions. Wrap the count in ROUND.
⚠️ Error: The padded number no longer works in calculations.
✅ Solution: REPT returns text. Keep the number in its own column and use the padded version for display or export.
Related Google Sheets functions
Related Google Sheets functions
- SPARKLINE: Charts inside a cell.
- CHAR: Symbols by their code.
- LEN: The length of a value, for padding.
- TEXT: Leading zeros and number formats.
- CONCATENATE and the ampersand: Join texts.
- JOIN and TEXTJOIN: Join a range with a separator.
An in-cell bar is as current as the number behind it
An in-cell bar is as current as the number behind it
A column of REPT bars makes a table readable at a glance, and it is trusted at a glance too. The bars are drawn from whatever numbers are in the sheet. If those came from an export pasted last week, the picture is last week’s, and nothing in a row of symbols says so.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The bars, stars and padded codes are then redrawn from the refreshed numbers. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Is the REPT function case-sensitive when repeating text?
REPT repeats the text exactly as given and keeps its capitalization: =REPT("AbC", 3) returns AbCAbCAbC. There is no matching involved, so case sensitivity does not apply.
Can the REPT function be used to pad text or numbers?
Yes, the REPT function can pad text or numbers in Google Sheets by repeating a character to fill space. For example, =REPT("0", 5-LEN(A1)) & A1 pads a number in A1 with leading zeros to ensure a length of five, useful for formatting or aligning data.
How do I combine the REPT function with other formulas for dynamic results?
To combine the REPT function with other formulas for dynamic results, nest it within functions like IF or ARRAYFORMULA. For example, =IF(A1>5, REPT("■", A1), "Low") dynamically adjusts the output based on a condition. This approach enables advanced, flexible data visualizations and customizations in your spreadsheets.
What is the maximum number of repetitions allowed by the REPT function?
The result of REPT cannot be longer than 32,000 characters. In our test, 32,000 repetitions of one character worked and 32,767 returned #VALUE!. The limit is on the length of the result, so a two-character text can be repeated at most 16,000 times.
Can I use the REPT function to create progress bars in Google Sheets?
Yes, the REPT function can create progress bars in Google Sheets by repeating a character, such as a "|" or "■," based on a value. For example, =REPT("■", A1) generates a bar proportional to the value in cell A1, making it a simple way to visualize progress.
How can I handle errors when the REPT function fails due to invalid repetitions?
REPT returns #VALUE! when the count is negative or a text, and when the result would exceed 32,000 characters. Wrap the count in MAX(0, ...) so it cannot go below zero, and in MIN to cap it. A decimal count is cut to its whole part, so use ROUND if you need rounding.
How can I use the REPT function for data visualization in Google Sheets?
The REPT function in Google Sheets creates simple visualizations by repeating characters. For example, use it to generate bar-like representations by repeating symbols like | or * based on numerical values in cells, allowing you to display trends or comparisons directly within your spreadsheet.
