The simplest way to show many types of data is through a column or bar chart, ordered greatest to least. These will work just fine, most of the time. When do they fall short? Well, when the values are all high, such as in the 80-90% range (out of 100%). Then the set of tall columns can be visually aggressive. In that case, try the lollipop graph, which looks like its name – a dot where the value that sits on top of a stick, connected to an axis. Here’s how to hack a lollipop out of Excel.
If you don’t end up loving this method, alter my steps for creating a horizontal dumbbell dot.
The backbone of the lollipop graph is a scatterplot, which means we will need x values and y values. The x values are easy – those are my actual data points, my survey responses in this case. The y values are going to be faked, just inserted to make each lollipop equidistant from each other. So here is my data table, with my actual survey responses and a new column next door where I typed in placeholder data, from 1 to 21. (Response options pulled from Multicultural Alumni survey for SUNY Cortland. Response percentages are dummy.)
Now highlight both columns of numbers and insert a simple scatterplot. Don’t highlight the data labels or the header row, or else the graph won’t work out properly.
The scatterplot process created dots – these are the lollipops! It is basically visualizing just the end of the bars in the bar graph. Right now, I suggest you click on the horizontal gridlines and delete them. They can confuse things if we keep them in place any longer.
It’s time to make the stick of the lollipop. We are going to create these using error bars. In Excel 2013, navigate up to the Chart Tools>Design tab and look for the button that says Add Chart Element. Click the dropdown arrow there, hover down to Error Bars, hover on it’s arrow to open another menu, and finally click on More Error Bars Options. In Excel 2010, you should just see a button in the Chart Tools ribbon for Error Bars.
As soon as I clicked on More Error Bars Options, my graph went wacky.
Excel added error bars in all four directions around each dot. We do not need the vertical error bars at all. So in the dialogue box that opened, change the number in the Fixed value box so that it is zero.
This will eliminate one set of error bars. To get to the other set, click on the little arrow next to Error Bar Options and then switch from Series 1 Y Error Bars to Series 1 X Error Bars.
You’ll see the same basic options, now just for the horizontal set of error bars. Here is where the magic happens. You’ll want to adjust every bit of this dialogue box. We really only want the error bars that shoot out to the left, so in the Direction area, switch from Both to Minus.
In End Style, get rid of the cap. In Error Amount, click the radio button by Percentage and type in 100%. This will fix your x axis so it starts at 0%, like it should, and will extend the lollipop stick from the lollipop head to the y axis.
The essential pieces of the lollipop graph are in place.
Give yourself a fist pump because that was awesome.
Next up, let’s fix that y axis, shall we? Right-click on it and adjust the maximum to 22 – that’s one more than the highest number we have in our fake data. If, in doing so, Excel adjusts your minimum, just change it back to zero. Now you can delete the y axis labels altogether.
Let’s get the data labels in place now, so we know which lollipop represents what. You could just do this by inserting textboxes. Or you can do it like ninjas do. Ninjas only, read on.
We are going to insert another series of fake data so that we can use it’s labels. Since we are still working with a scatterplot, the y values for the data are already here – we will use the same y values in the Lollipop spacing column. But the x values will be new. So add another column of data and this time, just fill it up with zeroes. This way, the new series of data will be a set of dots all the way over on the left side of the graph and the labels we eventually put there will look like regular, standard, proper labels for the lollipops. The table will now look like this:
Right-click on the graph and click Select Data. In the dialogue box that opens, click on Add in the Legend Entries section. Use the cell picker icons in the new box to select Label holders as your series name, all those zeroes at the Series X values, and all the numbers underneath Lollipop spacing as your Series Y values.
Click OK and your graph will have a second set of dots going up the left side of the graph. Sweet! We are going to add data labels to the dots! But there’s no room for the data labels yet. So click inside the graph, on the white background (Excel calls this the plot area). It’s border will become active and you should see a little square white box on the left, amidst your new series of dots. Click on that baby and drag it over to the right so that you are shrinking the plot area and making room for your labels.
Now that there’s room to breathe, right-click on the new set of dots and select Add data labels. Excel is going to give you the y axis values, which are a secret. But click on each label and link it to the spreadsheet cell with the label you actually want. Here’s how you create that link. Click on a single label and type an equals sign in the formula bar. Then go click in the spreadsheet, in the cell with your true label. Hit Enter and your actual label will populate in the graph, Ninja.
You may need to readjust your plot area here or enlarge your entire graph to make room for all those labels (I did both).
Labels are in place, lollipop and stick are ready to go. It’s time to get rid of the second set of dots that our labels are attached to. Right-click on those dots and select Format Data Series. In the Marker Options window, select None to eliminate the marker dots altogether.
The last step here is to add an awesome title and make any color adjustments.
Go ahead and compare this lollipop graph to a bar graph of the same data. Less visually demanding, isn’t it? Even though it took us several more steps to make the lollipop, we put in that elbow grease in order to make life easier for our readers. Plus you learned about the secret power of error bars in this process!
Yummy enough to eat, I know! Lollipops are decent alternative to standard bar chart when the visualization is overpowering due to its massive ink because the lollipop focuses attention at the value.
This tutorial and others like it are featured in my book, Effective Data Visualization. Order now.
