Excel REPT Function: Gotta Love Repeats!

The Excel REPT Function is another Text manipulation function that will repeat all characters in the given string of text the given number of times. It seems like this might be useless function, but it is actually quite useful, as you will see in the examples below.


Description of the Excel REPT Function

The Excel REPT Function repeats text a given number of times. You can use the function to fill a cell with a number of instances of a text string or padding cells. You can even use it to create a poor-mans bar chart!


Excel REPT Function:

Syntax of the Excel REPT Function:

  • REPT(text, number_times)
  • text (Required) - The text you want to repeat.
  • number_times (Required) - The number of times to repeat the given text.
  • number_times must be a positive number or it will generate the #VALUE! error.

Examples:

  • =REPT("*", 5) = *****
  • =REPT("*", 3) & " HELLO " & REPT("*", 3) = *** HELLO ***
  • =REPT("|", 10) = ||||||||||
  • ​=REPT("|", 15) = |||||||||||||||
  • =REPT("|", 13) = |||||||||||||
  • =REPT(A1, 4) = Repeat the contents of cell A1 a total of 4 times.
Excel REPT Function 1
Excel REPT Function 2

Bar Charts?!? What Does That Have To Do With The Excel REPT Function?

This is where the mundane repeating function gets a little cool. What appears to be a bar chart, is actually just the "OR" symbol repeated a number of times. The "OR" symbol is displayed as the vertical bar that is just above the Enter Key on your keyboard. Using the Shift Key, press the "OR" symbol and you will see the following character:    |

​Okay, So How Does That Make A Chart?

In the first image above, the Excel REPT Function is used to calculate how many | characters to print in the cell. It takes the value in Column D and divides it by 100. We divide by 100 to ensure that the "Bar Chart" doesn't go past the right edge of the Excel Window. 12,000 characters is a lot, so we cut it down to 120, which is far more manageable. The formula to create the "Bar Chart" is =REPT("|", D2/100). Using your default font probably won't look good, so you may want to change it for the cells that the Excel REPT Function is in. I suggest using either the Stencil or Haettenschweiler fonts. The Stencil font is used in the first image above and the Haettenschweiler font is used in the second image.

How Do You Get The Vertical Chart?​

The formula is the same for the vertical chart. The only difference is that the orientation of the text is rotated by 90 degrees. You can do this by right-clicking on the cell and choosing Format Cells. Then choose the Alignment Tab and look for the Orientation section on the right. You can either change the Degrees by typing 90 or -90 into the Degrees box, or you can drag the little red dot up or down until the Degrees reads either 90 or -90.

Excel REPT Function Format Cells

Download The Example Spreadsheet Here:

The Excel REPT Function


Why use the Excel REPT Function?

This function is great for padding cells with text, symbols, numbers or even extra spaces. It really shines when you need to do a quick, yet dirty, Bar Chart. Formatting cells helps make things look better too, and we all could use a little professionalism.

Conclusion

For all of the above reasons, this function turns out to be pretty valuable to know! It is simple to use and like most functions, it can be nested inside of other functions to create some truly astounding spreadsheets. This one can quickly get the eye of your Superiors and have them asking "How Did You Do That?". When they are asking that question, you can color them impressed!

Share, Comment, Ask a Question or Rip Me a New One!

If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!

Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services​.
Joe Austin
 

Joe Austin is the founder of Potentials Unleashed. He is currently an IT Professional Manager in the Financial Industry and has been using computers since 1978. While mostly self-taught, he is proficient in several computing areas, such as Programming , Web Development, most Microsoft Office programs, Internet Marketing, Spyware / Malware Removal and even 3D Animation & Modeling.

Click Here to Leave a Comment Below 0 comments
%d bloggers like this: