The REPT function is one that I feel is remarkably under used. In fact it simply replicates a parameter / value of your choice based on the contents of another cell.
What most people don’t realise is that you can use this function to create a dot table.
Imagine you have two columns of data:
| Data | |
| Data 1 | 2 |
| Data 2 | 4 |
| Data 3 | 5 |
| Data 4 | 2 |
| Data 5 | 6 |
| Data 6 | 12 |
| Data 7 | 11 |
| Data 8 | 4 |
| Data 9 | 7 |
| Data 10 | 10 |
| Data 11 | 9 |
| Data 12 | 7 |
You could easily plot this as a bar or column or even a line chart. However, you can also plot this directly within a cell using the function below:
=REPT(” “,B2-1)&”l”
Column B of course would be the second column of figures in the example above. Paste this formula into column C of your data and expand the column size. Then change the font of that column to wingdings, give it a font colour of for example blue, and voila:
What the function is doing is putting spaces into the cell, the number being determined by column B, and then adding the “1” character at the end, creating the dot. As you can see, it’s not very difficult to reproduce and much less time consuming than creating a dot chart using the standard built in line charts. It also means your final excel file is very light in size in comparison to one with a chart.
Enjoy!
This is one of my favorite excel tricks. What’s funny is that I’ve made simple bar charts but it never dawned on me to make dot charts – so thanks!
One suggestion for mac folks: instead of changing the font I use the dot character (••••), which is Option-8 on a mac keyboard. In fact there might be a key combo for it on PC but I’m not as familiar w/ windows.