This is a great technique on how to create a histogram in Excel using the Excel REPT function. If ever you have some data and want to avoid using Excel’s charts or if you have limited space on your spreadsheet then this technique would be great for you.
What is the REPT Function?
But first what is the REPT function? Well the Excel REPT function simply repeats characters a specified number of times.
text – the text to repeat
number_times – the number of times you want to repeat the text
For example =REPT(“a”,6) will return “aaaaaa”
How to Create a Histogram using the REPT Function
In this example I have some sales data for a clothes shop in the cell range B4:C15 and have created a simple histogram from this data using the REPT function in column D. The formula in cell D5 is =REPT("n",C5/15)
I have used the “n” character as the text argument as this is the character I want to repeat. For the number of times argument I have used the actual sales value in cell C5. I have divided this by 15 otherwise “n” would be repeated 350 times which is too much. By dividing by 15 it now only repeats 23 times in cell D5. I then just copied the formula down using the fill handle.
To make the square blocks I changed the format of cells D5 to D15 to a Wingdings font. If I did not change the font then I would get:
Using the REPT function is a great way to create a histogram in Excel but be aware that it is best to use this method if your data is simple and you want to avoid spending time creating a complex chart. If your data is more complex and not as straightforward then I would recommend you use one of Excel’s built in charts.
I hope you enjoyed this tutorial on how to create a histogram in Excel. If you have any questions or want to give me your feedback then please leave me a message below and I will get back to you as soon as possible.