September 9

Info Graphics with Excel

I’m not always the biggest fan of info graphics. Many of the posters-sized info graphics released these days have issues. But lately I’ve also received several requests on how to do info graphics with Excel. Many people don’t know where to start.

How Info Graphics are Different

Info graphics differ somewhat from your usual dashboard-style reporting. When we report with business tools, we use the data points–charts, tables, etc–to investigate a problem or monitor a system. That is, we use data to find results. Info graphics are used when we already know the results and we want to present it in an interesting, sometimes even artistic, way. Info graphics, then, are more about style and appearance–they wouldn’t necessarily find a good home on a dashboard. But they do work well in magazines, newspapers, and some student projects.

To help you follow along, go ahead and download the file that accompanies this post:

Info Graphics and Excel

Many info graphics are made with graphic editing programs like Adobe Illustrator. As far as I know, these illustrations are static. So each change in the underlying data won’t be automatically updated in the graphic. You would just  have to redraw the graphic. Excel provides a benefit here: if we use Excel’s charts to make our info graphics, we can update the underlying data and the result appears automatically.

Below, I’ve listed three different types of infographics: (1) a waffle chart; (2) a progress meter; and (3) a donut chart.

The Waffle Chart

The waffle chart is simply a variation of the one created by Michael Alexander. You can go to his website to find out how they’re made.

waffle chart

The Progress Meter

Progress is a good thing, right? Below is a progress meter info graphic created with Excel. The chart is automatically linked to the cell F2 as seen in the picture, so changing that value will change the chart’s presentation automatically.

image

To create your own, you’ll start with a loveable bar chart. Below, I’ve linked the chart to our data cell, F2.

image

Now, you’ll have to make a few changes to get the chart ready.

  1. Set the Horizontal Value Axis properties: set the minimum bounds to 0 and the maximum bounds to 1.
  2. Set the Gap Width of the chart to be 0%, so it takes up the entire plot area.
  3. Delete the chart title.
  4. Delete the Horizontal (Value) axis from the chart.
  5. Delete the Vertical (Category) axis from on the chart.
  6. Delete the Major Gridlines from the chart.

If all went according to plan, you should a minimalist chart like the one shown below.

image

Now this next part is tricky. In the above picture, you can see plot area of the chart has been selected. That leaves a very large margin between the plot area and the chart area. For this next step, we want to make the plot area to be exactly the same size as the larger chart area. You can do this by dragging the anchors of the plot area beyond the contours of the chart area. You can see I’m doing this for the top anchor below. You’ll need to it for all four sides.

image

When complete, your chart should look something like this when selected.

image

In this next step, we’re going to anchor the entire chart to a cell. Specifically, in the image below, I am going to anchor the chart to cell B7.

image

To get the chart to fit to the cell exactly, we’re going to use the Snap-To-Grid feature. Once you’ve selected the chart, you can enable Snap-To-Grid from in Align dropdown from the on the Format context tab. Simply drag the anchors to fit in the cell as I do below.

image

Once snapped, notice you resize Row 7 and the chart height will size accordingly. Snapping the chart to a cell allows us to make quick size changes immediately without having to select the chart. It’s a huge timesaver, in my opinion.

As you can see from the chart below, I’ve decided to give the bar an electric green color. I’ve also right-clicked the bar chart and added data labels from on the popup menu.

image

You can select the data label to change it’s options and make it prettier. From the Format tab, you can change how the data label is presented. For starters, I’ve changed it’s shape to a rounded rectangle. The Change Shape dropdown is in the Insert Shapes group on the Format tab. To make things easy, I’ve simply selected one of the preloaded defaults (it’s rare I ever do that, but the colors fit so well) for its background and font color.

image

Now, with the data label selected, turn off Snap-To-Grid and then resize the label for effect. In addition, you can also select the plot area and give it a more stylistic background color as I’ve down below. Finally, you can add some color formatting in the cells that surround the chart.

image

Our final task will be to give the chart above that appearance being rounded. Select the chart above, and from on the Format context tab, ensure the shape outline has been set to none. Next, from on the Inset tab, insert a new rounded rectangle shape. The shape will require some formatting, so do this:

  1. Set the fill color to none.
  2. Set the shape border to be the same color as the cells surrounding the bar chart.

Once you’ve done these steps, flip Snap-To-Grid back on and size the shape to fill up the same cell area currently holding your bar chart. Your screen should look something like this:

image

Now, with the shape still selected, change its border width from on the Format tab. There’s no specific width you should set it to, but just remember you want it to be thick. A size too small will show the corners of the bar chart. Below, the width has been set to 4 1/2.

image

And that’s all it takes to make the progress meter! Remember, the bar chart is linked to the cell, so if you want to reuse the progress meter, simply change the value in the linked cell. Also, remember the chart and rounded rectangle have been anchored to a cell. So to easily make the chart larger or small, simply change the row and column size.

The Progress Meter

Don’t worry, the donut chart is easier to make than the progress chart. The basics of a donut chart are the current value (as a percentage) and its complement. The complement is simply the leftover when the value is taken away from 100%. The image below shows the value and complement. Cell D4 shows the formula used in cell D3.

image

To make your donut chart, select your value and complement and then insert a new donut chart from the Insert menu. See below.

image

As we’ve done previously, we’ll need to clean up a few things. Go ahead and delete the legend. You can also change the colors as I’ve done below. Personally, I like making the value a stronger color than the complement. This allows us to clearly see the signal of the chart. I’ve also moved the chart title to the center. More on that in a second.

image

Personally, I don’t really like how value (that green portion) starts at the top. So under the series options, I’ve set the Angle of the first slice to 90 degrees.

image

Now, we’re going to use the Chart Title to put something interesting inside the donut chart. Select that chart title, then up in the formula bar, link it to whatever cell holds what you want to appear in the center. You could go for the value as is popular. I’ve done this below.

image

But I’m partial to have an icon appear instead—another popular choice. I’ve used an icon from in the Webdings font. When you do that, you need to ensure you chart title is also set to use the Webdings font. Below, I’ve also created a new textbox and linked it to a cell holding the 35%. I also set the outlines of all the charts and textboxes to none. Finally, I set all items to have no fill and set the background color of the cells to be white. How does it look?

image

Final Thoughts

You maybe wondering why you’d even want to do an info graphic an Excel given all the other available illustration programs. Well, as we’ve covered, it’s easily reusable by changing a number in a cell. You don’t have to redraw it as you might in another program.

The second advantage comes in laying out the whole info graphic in Excel. If you have Excel 2013, you can select the cells bounding the charts and copy it it to the clipboard as you would anything else.image

Then, on another sheet, you can paste the chart as a linked picture.

image

Now, you can treat a new worksheet tab as your canvas. The linked pictures move around the spreadsheet easily as any shape. This dynamic allows you to easily create your info graphic components on another tab. Then, on a final tab, you can lay everything out as desired by treating the charts as pictures.

image

If you don’t have Excel 2013, you can always use the Camera Tool instead. Unfortunately, the pictures are of lesser quality with the camera tool, but still look good.

Download File:

If you missed downloading the file at the start of the this blog post, make sure to get it now.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags


You may also like

September 9, 2014

Info Graphics with Excel

June 19, 2012

Rollovers for Gantt Charts
  • I’m a huge believer in the use of Info-graphs while presenting data (or dashboard) to an audience. It allows you as a presenter to more easily tell a data-driven story as peoples’ eyes (ie attention) are natural drawn to orderly graphics.

    I had never thought to make a progress bar out of an Excel graph before! I’m going to have to think of some way to incorporate that into one of my future projects. Great stuff!

  • I really liked the Progress Bar and when implemented in my macro, it was really amazing.
    Thanks for sharing. Appreciate it..!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >