Excel REPT Function ExamplesThese 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. |
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.
The REPT function can be used to fill a cell with a character, or in innovative ways, such as:
The REPT function has the following syntax:
The REPT function has a few traps:
If you simply want to fill a cell with a character, you can use cell formatting instead of the REPT function:
The character will fill the cell, and automatically expands or shrinks as the column width is adjusted.
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:
In this example, the target number is 100:
NOTE: I added conditional formatting in cells C3:C5, to use red font for quantities less than 60.
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.
NOTE: I added conditional formatting in cells C3:C5, to use red font for quantities less than 60.
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:
This example uses the same data as the previous example
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.
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.
There are two REPT functions in this formula, and each one has another function in the number_times argument.
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)
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.
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.
Conditional Formatting Data Bars
Last updated: January 30, 2023 2:29 PM