Thursday, January 26, 2012

How To Create a Dot Plot in Excel

A Dot Plot or Dot Chart is one of the most simple types of plots and they are very easy to create in Excel without having to use a Chart object. The trick is to use the REPT() function to display the dot plot either horizontally or vertically. I will explain how this is done and you can download the Dot Plot Example file to see how I created the dot plots on this page.

 Horizontal Dot Plot

I like board games. The example dot plot below shows the number of times out of 50 that I rolled a 1-6 with a single die. Actually I cheated because these values were randomly generated in Excel (see the example file). But it demonstrates the point.

A horizontal dot plot is probably the easiest type to create. Just list the category labels in column A. Then in column B enter the corresponding numbers. To create the dots for the dot chart in column C enter the formula =REPT("•",B1) or =REPT(CHAR(149),B1) and then copy the formula down. Then, hide column B. Pretty simple, eh?

Vertical Dot Plot

One of the games I like to play is Settlers of Catan. If you're familiar with this game, you may recognize why I used the following example vertical dot plot.

The trick to creating a vertical dot plot is to simply change the orientation of the text within the cells to vertical. Otherwise, the procedure is pretty much the same as the horizontal dot plot.

Spice up the Dot Plot

When you use this approach to create dot plots, you can change the color of the dot chart by just changing the font color. In the example below, I've used conditional formatting in Excel to automatically make the maximum value red.

 Tip: If you want a larger dot without increasing the font size, you can use the WingDing font and the letter l (el) for the repeated character.

Related Post :-
How to Export Microsoft Communicator Contacts

How to Install Microsoft Office Visio From Zip File
How To Transform Dull Data With Microsoft Visio 2010
How to Add a Traditional Header and Footer in Microsoft Visio 2010


Post a Comment