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 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:

dot table 2

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.


Alesandra Blakeston

One thought on “How to create a dot table using the REPT function

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s