A few weeks ago, I published an Excel tutorial on how to create the ill-named “box-and-whisker-and-scatterplot chart.” In response, Len Kiefer pointed me to a post on Maarten Lambrecht’s cool new project Xenographics that was essentially the same chart type, just turned on its side, and named the more clever “Raincloud Chart”. The creator of that example, Micah Allen, posted a step-by-step tutorial on how to create the graph in R.
So, I took my original graph and moved the data around to rotate the whole thing to create a Raincloud Chart in Excel. It’s basically the same approach as before, but just changing the columns to bars and flipping the x and y coordinates.
After reading Allen’s post, which used more data, I wanted to see if I could use my Excel template to make a similar graph. Now, I’m still not ready to do kernel density estimates in Excel, so the task was really to extend what I had built and make one change–notice how in my version above, the dots are plotted as a histogram, but in Allen’s version, all of the data points are plotted and bound in a tight band.
In Excel, I was able to use my existing template and extend the references to account for all of the data. Changing how the points were plotted required doing a little jiggering: For the y-position of each point, I multiplied the “base position” by a random number bounded between 0 and -0.5 [=rand()*-0.5]. The “base position” is the maximum y-value for each band; for the “AngerUH” category, for example, this value is 1.3, and the rest of the points cascade (downward) from there. (The x-position is simply the data value.)
This is certainly easier to do in R or some other coding tool. Aside from the fun intellectual challenge of trying to build this in Excel, however, I think it’s useful to know that graphs like this can also be built in tools like Excel.