How to create a dot table using the REPT function

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!

Alesandra Blakeston

Advertisements