A common chart mistake is to use a line chart for the wrong type of data. An example of that is the chart below that has categories* along the horizontal axis:
*This chart displays the degree of difficulty each sport requires for the categories above, as judged by a panel of 8 experts made up of sports scientists, Olympic Committee members and academics.
The categories are distinct from one another, whereas the line connecting them implies they are related. A typical use for line charts is to show trends in continuous data collected at equal intervals. For example, data over time, with time/dates on the horizontal axis.
A better chart for nominal data, like categories, is a bar or column chart:
But once you get to more than 3 or 4 series in one chart, they become too cluttered to make sense of:
That said, if I remove the axis labels, I could probably sell it as abstract art. Sorry, I digress 😊
Download the Workbook
Enter your email address below to download the sample workbook.
Watch the Video
When to Dot your Lines
When you have a lot of series to display you need to decide whether to separate your data into panel charts or Sparklines so you can clearly view each series. Or focus on one or two series and let the other series provide context.
In the Dot Plot chart below, we’ve called out one sport, Speed Skating and the other sports show the range and distribution in each skill:
I can also add a Slicer to allow the user to select a different sport to focus on:
Or multiple sports:
Building Excel Dot Plot Charts
A vertical Excel dot plot chart can easily be built in Excel using a Line Chart with Markers.
Then format each line to display ‘No Line’:
This leaves the markers behind and what you end up with is a vertical Excel dot plot:
Next you need to set each series’ marker size slightly larger and set the colour to something subtle, like grey.
Warning, it’s a labour of love to set the marker for each series one by one. This post provides some VBA to quickly format the markers.
Note: I also fixed the vertical axis height to 10, which is the top score for each skill.
How to Highlight One Dot
Here’s the secret, there are actually two charts, one containing all of the sports displaying the grey dots and a separate one on top highlighting the selected sport in a different colour. You can see both charts in the image below:
I used a PivotTable as my chart source data, so I can use a Slicer to select which sport to highlight. This means I need two charts; one for the grey dots displaying all sports, and a separate chart that displays the sport/s selected in the Slicer.
Tip: If you’re not using a PivotTable as your source data for the chart then you don’t need two charts. You can simply add another series to the chart and format that series in a different colour to the rest, as described here. Just be sure to put this series on top of all the other series in the chart so that it doesn’t get covered by the grey dots.
Top Chart Format
Make sure you set the vertical axis height the same in each chart:
Then hide/remove the axis labels on the top chart and set the ‘Shape Fill’ to ‘none’ so the bottom chart shows through:
Then align them perfectly:
Data Source: http://www.espn.com/espn/page2/sportSkills
Thanks
Thanks to Sunny Kow, our Excel forum superstar, for suggesting layering two charts. If you're going to share the file with others just check that they don't go all wacky on different PCs.
Related Tutorials
 |
Ghost series – if you create panel charts then you’ll want to fix your vertical axis height using a ghost series. |
 |
More Dot Plot Chart Examples – including dumbbells and lollipop charts |
Please Share
If you liked this please click the buttons below to share.
Thanks! I really appreciated the tip for the line chart with no lines.
Glad you liked it 🙂
Hi,Treacy!
Thank you for your wonderful blog posts,give me a lot of inspiration
I used this data to make a simple dashboard, I want to send it to you, you can download it from the following address,A secure web page(like Google Docs、Quip):
https://shimo.im/docs/qrC3cKtWWwhpGj6Y/
Looking forward to your comments and suggestions
Thanks again
Please excuse my poor English
Hi Tarzan,
Thanks for sharing your file. You have some interesting charts in there. Some better than others at displaying and allowing comparisons in the data.
Mynda
This Excel Line Dot Plot Chart is really helpful, Thanks.
I wonder if it is possible to build in excel a chart where the radius would become the dependent variable (commonly the y axis) and the circle border would be the independent variable (commonly the x axis).
This type of chart could be very good for describing seasonality or presenting periodic series.
I tried to use the radar chart, but I couldn’t discover a good solution!!!
Glad you liked it, Jorge.
Not sure I can picture your example, so don’t have any ideas at this stage, sorry. Maybe you could post your question on our Excel forum with some example data.
Mynda
Like the chart type!! Neat way of displaying data. I made a simple range to experiment with and created a ‘manual’ graph: I found the highlighted Sport needs to be at the bottom of the Series list (I assume done last). I copied the range which I formatted as a Table. Spot of bother creating the pivot table for the pivot chart tho: I can get Sport on the X axis, 0-10 on the Y axis and Categories as the lines. Try as I might, I can’t seem to swap Categories and Sport. What fills the pivot table Report filter, Column labels, Row labels and Values boxes? Mine are: blank; Sport; Agility etc.; blank. Thanks, rgds
Hi Steve,
Glad you liked the chart. It’s tricky to know what the problem is with your Pivot Chart without seeing it. It might be as simple as switching the Row/Column labels. If that’s not it, please post your Excel file and question on our forum where we can help you further.
Mynda
Excelent post Mynda, I confess that VBA is still my Achilles tendon… :(…. would it be possible to know the macro/VBA to cycle through setting the marker for each series and automatically apply the formatting? I see that you mentioned Phil might write a tutorial about it … ? : O
Thanks, Carlos. Hopefully Phil will write about VBA for formatting line charts soon.
Very good post, but unfortunately it is impossible to load the workbook, the link doesn’t work.
Best regards.
Sorry, Jacques. I’ve fixed the link now.
even if the source is a couple of pivot tables, I’d still want to plot them on the same chart instead of lining up different ones
this “just” requires plotting a conventional chart from pivot charts, which is a different type of pain (I think you might have done an article on it)
Hi Jim,
Indeed I did write about creating regular charts from PivotTables. This post just shows an alternative.
Mynda