Contextures

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

Hlookup00

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.

Hlookup00

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. 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 REPT

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.

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 bar chart with REPT

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.

in-cell dot plot for stocks

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

quick tally with REPT

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)

find last text item with REPT and VLOOKUP

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)

find last text item with REPT and VLOOKUP

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.

Download 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.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

More Functions Tutorials

VLOOKUP Function

Conditional Formatting Data Bars

Conditional Formatting

CHAR Function

Named Excel Tables

 

 

Get weekly Excel tips from Debra

 

 

30 Excel Functions in 30 Days

 

excel tools

 

fast excel

 

 

 

pivot power premium

Last updated: June 22, 2019 3:26 PM