fbpx
Search
Close this search box.

Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]

Share

Facebook
Twitter
LinkedIn

In the recent How to visualize survey results using Incell Panel Charts I have presented an easy way to create panel (or trellis / small multiples ) chart using Excel’s Incell Charting capabilities. That post is inspired from beautiful work done by Jon on Stacked Bar Chart Alternatives. Naomi B Robbins, who is an author and writer (visit her site: NBR – Graphics) has written a follow up article on this in which she suggested a dot-plot like this:

Dot plot panel chart for survey data

Jeff, who is a sweet fella from down under pointed that Naomi’s dot plots too can be incellified easily with excel. He suggested that I do a follow up post and show how an incell dot plot can be created for same data. I always look for opportunities to make you awesome in excel and Jeff’s request sounded just the right thing to do. So here is the incell dot plot for the same survey data – without further ado…

Incell Dot Plots in a Panel

Incell Dot Plots - Panel Chart in Excel to show Survey Data

The chart works exactly same as earlier one. Instead of REPTing | symbols, we used – (hyphens) and then a big black dot in the end (char code x25CF). Since today is a Friday, I am refraining from a lengthy post explaining how this is done. Instead, download the source file and learn yourself. Here is a clue…

Incell Dot Plots - Panel Chart in Excel - How it works?

Further Reading:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

19 Responses to “Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]”

  1. david says:

    thx, chandoo.

    question: how do u get the common normalized value (2.5)? any specific formula for it?

    reason i'm asking cuz i have similar data: some have extremely high values compared to others

    e.g. PRoduct A = values ranges in 1-5k. Product B/C/D = values ranges in 200-500k.

  2. That Chandoo is a magnificient piece of work : simplicity, elegance & a touch of flair.

  3. Jeff Weir says:

    Bernard...without a comma between the words "That" and "Chandoo" in your post, you're saying that Chandoo himself is a magnificent piece of work et cetera. Probably the nicest typo that Chandoo's ever got 🙂

  4. Nice work, Chandoo.

    I want to point out a small but important difference between my dot plot and Chandoo's. In mine, the line connecting the dot to the label goes completely across the panel. Therefore, we are judging the value of the dot by position alone. In Chandoo's, the line stops at the dot (these are often called lollipop plots.) Therefore, length enters into our judgment. As a result, it is important to always include zero with lollipop charts while a zero baseline is not necessary when the line goes all the way across.

  5. ikkeman says:

    @ naomi
    easily remedied by formatting the cell: custum, @*~
    You can replace the tilde with whichever character you prever. using different characters for different rows makes it easyer to identify which marker belongs to which label (instead of counting down from the top to see which label corrosponds to that particular data point)

  6. Chandoo says:

    @David: It is mostly trial and error. I used 2 and there were few dots outside the cell width. I tired 3 and there was way too much white space. So 2.5 it is.

    @Bernand... thank you 🙂

    ----

    That Jeff is the most alert and lovely, comment. Thank you 🙂

    @Naomi... Thanks for telling us about the lollipop plots. I didnt know there was such a term. Also thanks for the pointer about length and visual judgement.

    I have enhanced my chart to show full lines...

    Enhanced Dot Plot Panel

  7. Jeff Weir says:

    I'm not a fan of the lines...kind of defeats the purpose of a dot plot I think.

  8. Chandoo says:

    @ikkeman... very good tip.. I didnt know that.. 🙂

  9. Matt Muir says:

    Hi Chandoo.

    On my PC, if you format the cell width at 96 pixels, then 100% fills the cell (the dot is "half vanished", 99% not quite, and every other value is in between. A good alternative to showing the complete line....

  10. Chandoo says:

    @Matt: good idea. However if for some reason the data changes, you need to again adjust the cell width. A better approach could be to use auto-scaling approach suggested by David here: http://chandoo.org/wp/2010/04/01/incell-panel-chart/#comment-99903

  11. [...] I just saw that they did a followup article on Dot Plot Panel Charts. [...]

  12. Trellis plots are a great visualization method, far underutilized in Excel. My submission for your sales visualization challenge in November was a trellis plot examining salesperson performance across multiple variables:
    http://cid-4e546fec4f1c6d0d.skydrive.live.com/self.aspx/.Public/dashboard%20submission.xlsx

    I am happy to see the credit given to dot plots, as they are a valuable tool for increasing the data density of reports or tools, e.g. http://pushindatalikeweight.com/2009/11/23/integrating-report-elements-in-excel/

    However, while use of character functions to create lightweight in-cell graphics can be handy, it is worth noting that dot plots (or any type of in-cell plot) can be created simply by shrinking down a normal excel graph and making a few formating changes. This approach is allows one to leverage all the different charting capabilities within Excel and relatively easily create detailed and nuanced graphs that would likely be impossible to create using character functions. I provide a tutorial to creating these in-cell charts, including dot plots here:
    http://pushindatalikeweight.com/2009/12/05/excel-2010-sparklines-v-small-multiples/

    Regards,
    PDV

  13. suroso says:

    Dear Mr. Chandoo,

    Is it possible to operate the dynamic excel dashboard in PowerPoint "slide show? (without open the Excel Program)
    Ps. recently i should use other dasboard program to perform the dashboard in PowerPoint

  14. [...] have written about panel charts before. Learn more from incell panel chart, Incell dot plot panel chart, and see some example panel charts in visualizing market share [...]

  15. remcos says:

    I'm viewing this no an older version of Excel (2003) and the data of the company selected in the dropdown menu seems to be the same for any company, ie. it doesn't highlight the "true" data before it was highlighted.
    Why would this be?

  16. Chandoo says:

    @Remcos: The file should work in Excel 2003 as well. May be you have disabled to automatic formula calculation mode?

  17. Pete says:

    I like it! But, how did you make the drop-down list? I've created drop down lists before, but I can't seem to figure out the one in your excel file.

  18. Ganesh says:

    Thanks for sharing this.

  19. Tzica says:

    Ehhmmm ... i can't download ( first link / adress don't do nothing, and second , one drive ? diplay an error ). There are some requirements for download ?

Leave a Reply