Incell Dot Plots in Microsoft Excel

Dot plots are one of the simplest plots available, and are suitable for small to moderate sized data sets. They are useful for highlighting clusters and gaps, as well as outliers. Their other advantage is the conservation of numerical information.

Today we will learn about creating in-cell dot plots using excel. We will see how we can create a dot plot using 3 data series of some fictitious data. We will create something like this:

Note: If you are new to in-cell charting, I suggest you read the incell bar charts article to understand the concept.

1. Take your data and massage it a bit

Since we are doing an incell variation of dot plot, we need to pre-process the data a little bit. Assuming we have data on revenues of 3 imaginary companies – MegaHard, Grape and Twogle like this:
We need to normalize the data to some meaningful number like 100 (remember, incell graphs print some character for each unit in the data.) so that the in-cell dot plot looks meaningful.

After normalizing the data we will also need to calculate some helper columns so that we can develop the incell dot plot easily. The helper columns (3 of them) will show,

Smallest value in each row – 1

Next smallest value in each row – previous helper column – 2

The largest value in each row – previous two helper columns – 3

Helper columns ?!? why are we doing this?

The helper columns (or intermediate values) are usual practice when we need to pre-process data for dashboards or charts. Once the chart is ready, I usually hide the helper columns as they do not really say anything.

In our case, we are using helper columns since the formulas for plotting the incell dot plot are rather long and we would make then even longer if we don’t use these.

2. Identify Symbols for Each Data Series

This is the simple job. In our case I have shown the symbols we are going to use in the above image. You can find some interesting symbols like triangles, rectangles, circles etc. in a regular font like Arial. Just go to Menu > Insert > Symbol (or Insert > Symbol in Ribbon) to find the symbols you like.

Let us assume the symbols are in the range C5:E5

3. Finally Write the Formulas That Generate the In-cell Dot Plot

Now comes the fun part. We have the normalized data in the range C16:E16, and the helper values in F16, G16, H16.

For the first row of the dot plot, the formula looks like:=REPT("-",F16)&INDEX($C$5:$E$5,MATCH(SMALL(C16:E16,1),C16:E16,0))&REPT("-",G16)&INDEX($C$5:$E$5,MATCH(SMALL(C16:E16,2),C16:E16,0))&REPT("-",H16)&INDEX($C$5:$E$5,MATCH(SMALL(C16:E16,3),C16:E16,0))&REPT("-",100-MAX(C16:E16))

huh! it has to be one of the longest formulas I have written in a while.

I thought long and hard about how this formula can be explained and came up with the below illustration.
Once you have the formula for one row, we just need to copy paste it over the entire range to show dot plot for each year of the data. That simple!

How to Generate 2 Series Dot Plots?

The 2 series dot plots have even simpler formulas. So I am leaving it to your imagination. But when you finish it, the dot plot looks something like this:

Download the In-cell Dot Plot Template and Make your own Dot plots

The downloadable workbook has examples for 2 series and 3 series in-cell dot plots. Go ahead and play with it.

Further Resources on Dot Plots

Dot plots are not new, there is quite a bit of material and tools available for you to understand and make dot plots. They are proven to be very effective tools for communicating small to medium series of data. I suggest you to read few of these articles to learn more about dot plots.

More on In-cell Charts

Sign-up for our FREE Excel tips newsletter:

Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

21 Responses to “Incell Dot Plots in Microsoft Excel”

ive tried following this but couldnt get the standardization to work so used the formula =B3/MAX($B$3:$D$9) * 100

some of the results come out as 11.5 which translates to 12 when removing the decimal places and i noticed that these are highlighted green on the example.
Would it be worth using =INT(B3/MAX($B$3:$D$9) * 100) ?

@Jon.. I have used int just to ensure that we are sending integers to REPT(). But it works with decimal values as well. Excel highlights a cell with green color because it has inconsistent formula wrt. other cells in that region. When you paste the same formula (=B3/MAX($B$3:$D$9) * 100) over the entire region, you wont see the green highlights.

Also, if you know formulas well, you can even turn off this notification to save sometime. Just go to excel options > formula and turn it off.

question: I wanna take the incell a step further, and create a UDF called (surprise!!) incell, to which I’ll pass the range of numbers I want to represent.

a first approach is related to the length of the bars, as I’m not sure how to normalize any number as a representation in 10 bars (silly me…).
the second issue might be related to sign. I’d thought on adding spaces prior to positive bars, so when represented they should be “upper”, and the opposite to negatives, so they appear “lower”.

@Martin: Welcome to PHD and thanks for asking a question. Excel UDFs have a limitation when it comes to cell formatting. You cannot use cell formatting related functionalities from UDFs. However there is a small loophole. You can create shapes using UDFs.

Fabrice, who is a regular at PHD and a wonderful person, has used this little idea to create a really sexy piece of UDF library here: http://sparklines-excel.blogspot.com/ using which you can generate richer and better incell graphs.

If you are planning to write UDFs that can generate incell graphs, you can take a look at his library and get some ideas.

Also, I noted that if the variations between the data series is too small, cell G16 returns a -1 ,and the plot returns a #VALUE!. Is there any way we can overcome this. I am trying to graph prices of 5 products and the variatons are not huge.

Hi Hui,
Still having trouble, much appreciated if you can help. I am using the downloaded file attached, and having the following issue:
– not sure how to apply the 3 series model into a 4 or 5 series model
– the data file which I have sent previously, when I paste them into the file in cell C7:E13, still creates a negative helper columns (creating a plot that returns a #VALUE!). I have tried to change the formula in cell C16 to =INT(C7/MAX($C$7:$E$13)*150.2320164) and dragged this to E22

Is there any chance you can send me the amended excel spreadsheet? Thanks, been stuck on this problem the last 2 days. Thanks again.

@Quen
Not sure there is an easy answer here
Problem is that to do this style of Chart you need to normalise your data so that there is at least a whole unit between the minimum separation
Your data
2000 0.499635011 0.506168977 0.499656989 0.506168977
will have to be multiplied by at least 100,000 to separate the 1st and 3rd numbers
But this will mean that there is over 100,000 between the 1st and 2nd numbers
This is no good for the Rept Function.
.
I have some ideas for a work around
I will make a mock up in a few days and post it.