Make a Pictogram in Excel
This graph type goes by a lot of names: isotype chart, pictograph, or pictogram. Whichever way, it allows us to use symbols rather than stick with the squares that make up the waffle chart. And it is especially well suited to representing small counts of things that can otherwise be distorted when we convert them to percentages. In this example, I’ll show you how to display a set of simple counts.
In this example, we are plotting the number of servings of fruits and vegetables consumed per day by some families in our simulated program, as well as the national average. Could just be a bar chart, right? Or even a lollipop or dot plot. But maybe we need to be a little more precise, since the counts are so small, so we will replace the bar with an icon of an apple, one icon per serving consumed.
Notice that the servings consumed are in the column labeled Red. The goal is 5 servings per day so the remainder is listed in the next column, called Gray. You’ll also need to procure an apple icon and have a red and gray version saved somewhere on your computer.
Now grab the data in Excel and insert a stacked bar chart.
Right-click on the bars that represent your red data and select Format Data Series. In the Fill section (for me, this is inside the icon that looks like a paint bucket), select the radio button by Picture Fill.
Then select the File button and go locate that red apple icon you saved earlier. Then click the Stack and Scale with button.
I never knew what that button did until one of my readers shared the secret with me. So cool!
We want those gray apple icons to look like the remainder. So follow the same procedure as above to fill the other bars with a picture of the gray apple.
Now, the graph is probably looking a bit distorted with some seriously squished and unappetizing apples. We will adjust a few things here to make this look right.
First of all, Excel likes to give you more than you need (I hear you nodding your head in agreement), so the x axis is currently running to 6 but it needs to stop a 5. To fix this, right click on one of the numbers in your x axis. Choose Format Axis. Change the Maximum bound to 5 instead of 6. Well… this probably added to the distortion. But it’ll be easier to deal with the overall chart area now.
In fact, let’s delete that axis (just click on it and hit the Delete key on your keyboard) and delete any gridlines.
If you click on one of the apples you’ll see that it is sitting within your original stacked bar area and that area is skinny. Thin. Stretching your apples. Let’s make it thicker. Right-click on any bar and select Format Data Series. You’ll see a menu called Gap Width. This is referring to the width of the gap between your stacked bars/apples. Set it to 0 and the apples will become more proportionate.
The apples are still a bit stretched, so resize the overall chart area until the icons look proportionate.
You could be done at this point but you may want to reorder the categories on your axis so that Average is at the top and Family 4 is at the bottom. Right-click on the y axis and select Format Axis. Check the box that says Categories in Reverse Order.
You’ll notice there’s still one line left, even though we deleted the gridlines. That’s actually the y axis line, so while in that same menu messing with the order of the categories, go into the paint bucket icon and, in the Line menu, select No Line.
Now, delete the legend and add a title. Boom!
This process will be fine if you are working in whole numbers. But let’s say Family 3 ate 4.25 servings of fruits and vegetables. This method is not gonna work. Another one of my readers sent me the trick to make partial amounts still work.
In the spreadsheet, fill the Gray column with all 5’s. Then, instead of a stacked bar chart, insert a clustered bar chart. Follow the same steps to pop the icons into the bar. Then right-click on the red apples and select Format Data Series. Then click the button for Secondary Axis. The red apples – even the partial pieces – will show up in front of the gray ones. Cool trick!
Whether you call it a pictograph, pictogram, isotype chart, or icon array it’s an efficient way of communicating small datasets. It might take a few minutes of your time to construct something like this but the time investment is worth it. These graph types are easy to read.
These pictogram instructions are one of the new chart types + step-by-step instructions in the second edition of Effective Data Visualization, where you can find even more options for visualizing quantitative OR qualitative data. This book has been the #1 New Release on Amazon’s Communications, Business Communication, AND Media Studies lists, just in the first couple of weeks since release.