Home > Formulas > Text > REPT Excel REPT Function Examples These Excel REPT function examples show in-cell charts, how to lookup the last text entry, and more, with videos, written steps, and sample files for free download.

Videos: REPT Function

The Excel REPT function repeats a text string, a specified number of times. Watch this short video to see how to use REPT to create in-cell charts, keep a quick tally, or find the last text entry in a column.

This short video show another example of in-cell charts with the REPT function. The chart shows student grades, out of 100, in either a bar chart or dot plot.

Uses for REPT

The REPT function can be used to fill a cell with a character, or in innovative ways, such as:

• Creating an in-cell bar or dot chart
• Keeping a quick tally
• Finding the last text entry in a column

REPT Syntax

The REPT function has the following syntax:

• REPT(text,number_times)
  • text is the item that you want to repeat
  • number_times is a positive number

REPT Traps

The REPT function has a few traps:

• The limit to the text string is 32,767 characters -- anything higher, and the formula will result in an error.
• If number_times is a decimal, it will be truncated to an integer.
• If number_times is zero, the result is an empty string.

REPT Alternative

If you simply want to fill a cell with a character, you can use cell formatting instead of the REPT function:

1. In a cell, type the character(s) that you want as the fill, such as a hyphen or asterisk
2. With the cell selected, press Ctrl + 1, to open the Format Cells window
3. Click the Alignment tab, and from the Horizontal setting, select Fill
4. Click OK to close the dialog box.

The character will fill the cell, and automatically expands or shrinks as the column width is adjusted.

Example 1: In-Cell Bar Chart

Instead of using Excel's conditional formatting data bars, you can create a simple in-cell bar chart with the REPT function.

Here are 2 examples:

1. Simple In-cell Bar Chart
2. In-Cell Bar Chart with Decimals

Simple In-cell Bar Chart

In this example, the target number is 100:

1. In cells B3:B5, type 100, 55 and 80
2. In cell C3, enter the following formula: =REPT("n",B3/5)
3. Format cell C3 with Wingdings font (I used font size 9)
4. Copy the formula down to row 5
5. Adjust column C's width to fit the widest bar chart, in cell C3.
6. Then, if you change any of the numbers in column B, its bar chart in column C will change

NOTE: I added conditional formatting in cells C3:C5, to use red font for quantities less than 60.

In-Cell Bar Chart with Decimals

In this example, the target number is 20, and some of the quantities include decimal amounts.The bar is solid colour for whole numbers, and shaded for the decimal portion.

First, create a lookup table for the decimal numbers, and their shaded shapes.

1. In cells B3:B5, type 100, 55 and 80
2. In cell C3, enter the following formula: =REPT("n",B3/5)
3. Format cell C3 with Wingdings font (I used font size 9)
4. Copy the formula down to row 5
5. Adjust column C's width to fit the widest bar chart, in cell C3.
6. Then, if you change any of the numbers in column B, its bar chart in column C will change

NOTE: I added conditional formatting in cells C3:C5, to use red font for quantities less than 60.

Example 2: In-Cell Dot Chart

Instead of bar charts, you can use the REPT function to create a simple in-cell dot chart. A dot chart has a cleaner look, with just one circle in each cell, marking the end point.

Here are 2 examples:

1. Simple In-cell Dot Chart
2. In-Cell Dot Plot Stock Chart

Simple In-cell Dot Chart

This example uses the same data as the previous example

1. In cells B3:B5, type 100, 55 and 80
2. In cell C3, enter the following formula: =REPT(" ",B3/5-1) & "o"
3. Copy the formula down to row 5
4. Adjust column C's width to fit the widest bar chart, in cell C3.
5. Then, if you change any of the numbers in column B, its dot chart in column C will change

In-Cell Dot Plot Stock Chart

Here's another example of using REPT to create an in-cell dot plot chart. This example is more complex, and the video below shows how to set up this chart. For the written steps, go to my Contextures blog.

Watch this short video to see how to set up the in-cell dot plot stock chart with the REPT function.

Example 3: Keep a Simple Tally

If you're counting the days until your next vacation, you don't need to make marks on your office wall! Instead, follow these steps to create a tally with the REPT function.

1. In cell B3:B4, type the numbers 18 and 21, as your target days
2. In cell C3, enter the following formula:
   =REPT("tttt ",INT(B3/5)) & REPT("l",MOD(B3,5))
   
3. Format cell C3 with Comic Sans font, or another font with a straight "t".
4. Copy the formula down to row 4
5. Adjust column C's width to fit the widest bar chart, in cell C4. NOTE: If the target number is high, increase the row height, and format for Wrap Text
6. Then, if you change a number in column B, its tally in column C will change

How It Works

There are two REPT functions in this formula, and each one has another function in the number_times argument.

• The first REPT shows one group of t's, for every 5 items in the count -- INT(B3/5)
• If there is a remainder, after dividing the count by 5, the second REPT shows that remainder at the end, as lower case L's -- MOD(B3,5)

Example 4: Find Last Text Item

To find the last text item in a column, you can combine REPT with VLOOKUP. For example, with text items and blank cells in column D, use this formula to find the last text item:

=VLOOKUP(REPT("z",255),D:D,1)

How It Works

The REPT function in the formula creates a text string of 255 "z" characters.

That text would be at the end of the alphabet, and VLOOKUP won't be able to find that string. So, with approximate match (1 or TRUE), it returns the last text item in the list.

Example 5: Set Row Height in Table

Another use for the REPT function is setting a minimum row height in named Excel Tables. Thanks to AlexJ for this technique -- he uses it to add a bit of spacing in his Excel tables, so each row is easier to read, and not crowded together.

An extra column, Spacing, is added at the right side of the table. That column has a REPT formula, which uses the CHAR function, with code 10, to insert 2 line breaks:

=REPT(CHAR(10),2)

Watch this video to see the steps for setting minimum row height with the REPT function, and see the full written steps on my Contextures blog.

Get the Sample Files

1. To see the charts, tally and lookup formulas used in these examples, download the REPT function sample workbook. The file is zipped, and is in Excel xlsx format, with no macros.
2. To see the row height example, download the Set Minimum Row Height workbook. The file is zipped, and is in Excel xlsx format, with no macros.
3. To see the Dot Plot stock chart example, download the Dot Plot Stock Price sample workbook. The file is zipped, and is in Excel xlsx format, with no macros.

More Functions Tutorials

VLOOKUP Function

Conditional Formatting Data Bars

Conditional Formatting

CHAR Function

Named Excel Tables