Excel has some powerful features for creating graphs and charts, but sometimes they are overkill. Also, most charts give a view of your information that is completely separate from the original data. But if you have a list with numbers in Excel you can use the REPT() function to easily create a bar chart style column of information right in your existing list.
First let’s take a quick look at REPT(). It stands for Repeat and all it does is repeat whatever letters, numbers, or other characters you request, a specified number of times. It’s format is equally simple:
=REPT(“text to repeat”,”number of time to repeat”)
The text you want to repeat needs to be in quotation marks. For the number of times to repeat, instead of entering a number, we will enter cell reference.
For example, here is a list showing quantity sold for each sales rep. In column C, I’ve entered a formula that looks to cell B2 to determine how many times to repeat a capital i. When you copy that formula down, Excel will automatically adjust the cell reference so that it points to the quantity in that row (B3, B4, etc).
A | B | C | |
1 | Sales Rep | Qty Sold | |
2 | Joe | 10 | =REPT(“I”,B2) |
3 | John | 15 | |
4 | Fred | 8 | |
5 | Ralph | 17 |
Once you have copied the formula down the rest of the column, your spreadsheet will look like this.
A | B | C | |
1 | Sales Rep | Qty Sold | |
2 | Joe | 10 | IIIIIIIIII |
3 | John | 15 | IIIIIIIIIIIIIII |
4 | Fred | 8 | IIIIIIII |
5 | Ralph | 17 | IIIIIIIIIIIIIIIII |
In this example, it works well to take the number of times to repeat directly from the list. However, if the quantities sold were larger, the bars would be too long to be useful. The simple solution is to divide the number in the list by 100, 1000, or whatever works for you. For example we could change the formula to read =REPT(“I”,B2/1000).