Infographics are powerful communication tools. This is evidenced by how popular they are in magazines and newspapers. Graphic designers usually create infographics through a manual process. What if you could create an infographic that updates with the change of a slicer? That would be powerful!
Live Example: See this example in Excel Online.
The basic construction is to put a cut out image in front of a bar chart. Here are the steps we will cover:
- Create a wide bar chart with a background color different than the image and the bar chart.
- Insert the image or images over the bar chart.
- Connect a slicer to the bar chart.
Let’s get to it!
1. Create a wide bar chart. To create a pivot chart, select the Pivot Chart option in the PowerPivot window.
Next change the chart type to a bar chart. Select the “Change Chart Type” on the “Design” tab.
Now we can resize the bar chart to the size we want. To make the bar wide, right click on the bar and select “Format Data Series…”. In this window, slide the “Gap Width” property to “No Gap” so the bar fills the whole chart.
Next we will change the scale so it is fixed between 0% and 100%. To do this, right click on the X axis and select “Format Axis”. Enter 1 for the max and 0 for the min axis range.
Now our bar should be all set. Next we can remove the chart labels/lines/buttons, because these will not be relevant in our infographic. To remove these, right click on each label/line/button and delete or hide them.
Here is what we have so far. Really exciting chart! 😉
The next thing we want to do is change the chart background gray (or the color of your choosing). This will display as the unfilled portion of the infographic. To do this, click on the chart background and go to the “Format” tab. Click on “Fill Color,” and choose the color you want.
2. Overlay the image. This is a simple copy paste of the image from your file system into Excel.
Next you can move, re-size, and align the images until they cover the bar the way you want. If you are using multiple images, I recommend using the align tools to get the images just right.
3. Insert Slicers. The final step is to insert a slicer so you can filter the chart. I also added a label below with the value. This is just the pivot table that the chart is connected to with the measure label hidden.
Here is the finished product. Don’t forget to turn off the gridlines to make it look like a real report.
This is a pretty simple technique, but can be very valuable. An infographic is great lead in to an analysis. They communicate a key measure clearly and elicit the next follow up question: why?
If an infographic is on a dashboard it is usually the first thing people look at. Make it the lead in to your next dashboard.
Please comment below with your experience using this technique. How did people like it?
Update: here are the links to the posts in this series:
Part 1 – Basic Construction (Excel Online example)
Part 2 – Easy ways to find or customize an infographic shape (Excel Online example)
Part 3 – Advanced Construction (Excel Online example)