Create a Dot Chart in Excel

The other day while helping someone out to create a visualization from a performance rating data, I thought of creating a Dot Chart, where dots get filled depending on the score. I really liked what finally came out and thought it would be of much use to you as well!

So, here we go !

 

Things needed to create this Chart

1. Performance Rating Data – But obvious!
2. Empty Dots
3. Semi Filled Dots
4. Filled Dots

To hunt for Empty, Semi Filled and Filled Dots I looked into the Symbols option in Excel

1. In the Insert Tab
2. Click on Symbols
3. Now Change the Font to Wingdings 2 (most likely you would have that pre-installed)
4. Scroll down to find a Filled Dot and an Empty Dot
5. Semi Filled Dot will also be around
6. Notice the Character Code for each of these Dots

Since Excel doesn’t understand text so we will be using these (ASCII) codes to create the dots. Let’s keep moving

 

Meaning of Filled Dots, Semi Filled, Empty Dots

Filled Dot

Means that we have an integer value. We are also assuming that numbers with decimal places greater than .5 will be rounded off to the next integer. So 3.6 will be rounded off to 4 but 3.5 will be considered as 3. For example

• 3 Filled Dots for 3.2 or
• 5 Filled Dots for 4.7

 

Semi Filled Dot

Means that we have a fractional value which is less than or equal to .5 For Example

• 3 Filled Dots and 1 Semi Filled Dot for 3.2
• 4 filled dots and NO Semi Filled Dot for 3.7

 

Empty Dots

Are the balance from a maximum value of 5. For Example

• 3 Filled, 1 Semi Filled and 1 Empty Dot for a value of 3.2
• 2 Filled, 0 Semi Filled and 3 Empty Dots for a value of 1.7

 

Crunching the Numbers

We start this data where we have Names and some random ratings, (too bad I got a 2.1 🙁 )

 

Next we find how many Filled Dots do we need

• If you don’t know, check out The CEILING Function (how it works and examples)
• CEILING(Rating, 0.5) Applying CEILING Function the rating will push the rating to the next multiple of 5

 

Now finding out how many Semi Filled Dots do we need

• Learn to use the INT and CEILING Function with an example
• Rating – INT (Rating) We are first finding the fraction from the filled dots
• CEILING(Rating – INT (Rating),1) The CEILING Function will convert the fractions to 1

Note a few things

1. The values of Filled Dots are in Decimals (which are less than or equal to 0.5)
2. We want to apply a Semi Filled Dot for all the values containing decimals
3. We are converting the Semi Filled Dot values to whole numbers (1) because we would need 1 Semi Filled Dot where there are decimal places

 

Calculating the Unfilled Dots

• INT(5 – Rating) We are subtracting the Filled Dots from 5 because that’s our maximum rating
• Also then converting the decimals into whole numbers

 

Making the Chart

Now that we have the back up ready lets make the chart. But even before I start making the chart, I want you to recall the Codes for the Dots

1. Code for a Filled Dot – 152
2. Code for a Semi Filled Dot – 155
3. Code for an Empty Dot – 153

 

PART 1 – CREATING FILLED DOTS

• Don’t worry about the symbols that show up. Our final move is yet to come 😎
• Check out the REPT Function if you don’t know how to use it
• All we are doing is repeating the Dot, filled number of times and the CHAR formula is helping us convert the code (152) into a Dot

 

PART 2 – ADDING SEMI FILLED DOTS 

• Don’t worry about the symbols that show up. Our final move is yet to come 😎
• After the Filled Dot we need to have the Semi Filled Dot so we use the & operator and combine the formula

 

PART 3 – ADDING THE EMPTY DOTS

• Don’t worry about the symbols that show up. Our final move is yet to come 😎
• In the end we need Empty Dots so we again combine the formula with the & operator

 

NOW OUR FINAL MOVE !

This is like the icing on the cake, an Ace to finish it all

1. Select the data
2. Change the font to Wingdings 2
3. And the color the font in a lighter shade of grey
4. Boom! There you have it!

 

A Benchmark – to give it more meaning!

Wouldn’t it be nice to color all the dots as red if the score falls below a benchmark?

And let’s say our Benchmark is 3

 

Apply Conditional Formatting Rule

1. Select your Data
2. Open a New Conditional Formatting Rule – (Use the Shortcut ALT H L N)
3. Select the Option – ‘Use a formula to determine..’
4. Paste the following formula =C7<=Benchmark. C7 contains our first rating
5. Set the Format as you like (The format will be applied if the Rating is below or = 3
6. See a preview of the Formatting in the Preview Box
7. Click Ok

And we are done, baby!

 

Where can you use this Chart – Pros and Cons

I cannot end this piece without mentioning its utility and may be some pros and cons

• Utility : Use this chart for showing ratings, describing complexity, high or low signals etc..
• A Pro : The filled and unfilled dots act as an excellent visualization for getting an approximation of the rating
• A Con : Not a good chart type when the rating is done on a larger scale. May be a scale point of 10. Too many dots might tend to distract rather than making it easier to interpret the chart

 

 

Learn to make some other interesting charts

1. Line Chart with Part Selection
2. Line Chart with Phases
3. InfoGraphic Charts PART 1, PART 2
4. Target Charts