How-to Make a USA Today Pie Chart Graph – Replicating All Charts in USA Today Part 1

I have been traveling very frequently for business as a Salesforce.com Software Consultant, so I spend a lot of time in hotel’s and airports.  So I get a lot of free newspapers like the USA Today at the hotel.  In this case, I am travelling back and forth to a client site in Los Angeles and last Friday, I decided to see if I could replicate every chart/graph/infographic that I found in the Friday, April 5th 2013 edition of the USAToday using only Microsoft Excel 2007 (although, there is no reason that Excel 2003 won’t work for this as well but I can’t show you these steps in this tutorial).  Also, I as my “Welcome / About Us” page states, I will not use any Excel macros or Excel VBA so that these charts and graphs can be used by any company dashboard or presentation and in corporation security model.

This is the first part of a multi-part posting so come back often to see how you too can make any of the charts and info graphics that you find in your daily newspaper like the USA Today using Excel.

Here are the first two charts of this series:

20130408_22023720130408_220137

The infographic on the left depicts a baseball image with a pie chart super imposed on the top of the baseball image.  The USA Today info graphic on the right is a similar type of graphic with a sweeping computerized radar graphic in the pie chart back ground.

Now these charts look simple, but it may not be as simple as you might think.  Most people would create a pie chart…image  and then find a baseball image to insert into the spreadsheet…baseball pie chart

then they would copy the baseball image and then select the pie chart and then paste the image, however, this is what your chart will look like:image

Notice that the image is now pasted into each of the pie chart data points and the baseball is squished into the graph.  Not what we wanted above.

Alternately, you paste the image directly into the pie chart, but even if you can match up the baseball graphic with the pie chart, but I can’t find a way to get the image to move behind the chart, so this doesn’t seem to be a solution.image

So how can we create the charts in Excel for the graphs you saw at the top of this posting?  Check out below and let me know in the comments what you think.

 

The Breakdown

1) Find and Insert a Graphic into Your Spreadsheet

2) Setup Graph Data

3) Insert Chart / Create Chart

4) Copy and Paste Picture into Pie Chart Image Series

5) Move Pie Chart Image Series Down

6) Change Pie Chart Series Fill to Solid and Change Pie Chart Series Fill to 100% Transparent

7) Change Pie Chart Series Border Color to Solid

8) Change Pie Chart Series Border Style to 3Pt

9) Move Pie Chart Series to 2nd Axis

10) Delete Legend and Add Chart Title

 

Step-by-Step Tutorial

1) Find and Insert a Graphic into Your Spreadsheet

First things first, you need to find a graphic like a baseball or radar image.  Download the image to your computer and/or insert/copy the image into your spreadsheet.  Here are the 2 images I found:

baseball pie chartRadar Pie Chart

 

2) Setup Graph Data

Now we need to set up our data.  We need to create the data that will represent the pie chart and we also need to add one more series that will hold the baseball or radar image.  Here is what the pie chart will look like for either chart:image

3) Insert Chart / Create Chart

Okay, now that we have set up our data, you will need create your pie chart in Excel.  For the baseball chart, highlight the range from A2:C5 and for the radar graph, select the range of A8:C12.  Then insert a pie chart from the Insert Ribbon:image

The process is the same for the radar chart of the baseball chart, but for the rest of this tutorial, I will focus on just the baseball pie chart.  Your pie chart will now look like this:image

 

4) Copy and Paste Picture into Pie Chart Image Series

Now find the image that you inserted into your spreadsheet back on step 1 and press CTRL+C to copy it.  Then select your chart and then select the blue pie chart you see above and press CTRL+V to paste the image into the chart.  Your chart should now look like this:image

So we now have the image inserted into the data series that you saw in the Pie Chart Image series.  Since that series only has one value, the baseball image will show up as one complete image.

 

5) Move Pie Chart Image Series Down

Now we need to move the baseball down in the chart Legend Entries (Series).  You can do this by selecting the chart and right clicking on it.  Then from the pop-up menu, you can choose the “Select Data…” option as you see here:image

Then from the “Select Data Source” dialog box, you should select the Pie Chart Image and then press the down arrow button from the Legend Entries (Series) section:SNAGHTML648ef3e

Your dialog box should now look like this:SNAGHTML64cf9aa

Your chart should now look like you expect to see for the Pie Chart data series:image

 

6) Change Pie Chart Series Fill to Solid and Change Pie Chart Series Fill to 100% Transparent

For this step, you don’t need to select any individual pie chart data point, just select the entire chart.  Notice how there are selector points around all data points, not just one:image

Then press CTRL+1 and you will see the “Format Data Series” dialog box.  From there, select the Fill options on the left and then change your options to “Solid Fill” and also change the Transparency to 100% as you see below:SNAGHTML651ef7d

Your Excel pie graph will now look like this:image

 

7) Change Pie Chart Series Border Color to Solid

Now if you have left the “Format Data Series” dialog box, select the chart and press CTRL+1 to bring it up again.  You don’t need to leave the dialog box to complete this step, but it was okay if you did.  Now from the “Format Data Series” dialog box, select the Border Color options and then choose “Solid Line” and a color of “Black”
SNAGHTML6567d3b

Your chart should now look like this:image

8) Change Pie Chart Series Border Style to 3Pt

Now if you have left the “Format Data Series” dialog box, select the chart and press CTRL+1 to bring it up again.  This step is not necessary, but you may want to do this if your pie chart slices don’t stand out enough.  Now from the “Format Data Series” dialog box, select the Border Styles options and then choose a larger “Width” of something like 2pt.

Your chart should now look like this:image

 

9) Move Pie Chart Series to 2nd Axis

Okay, here is where the MAGIC happens with this Excel Chart.  Now if you have left the “Format Data Series” dialog box, select the chart and press CTRL+1 to bring it up again.  Then from the “Series Options”, select “Secondary Axis” from the Plot Series on section.SNAGHTML65c8eb8

Now your chart should look like this:image

Looks almost like the real one from the USA Today.

 

10) Delete Legend and Add Chart Title

Now to make a few last changes, lets select the legend and press your delete key.

Then select the chart and from the Layout Ribbon, select Data Labels and Chart Title to give the Pie Chart more information for the reader. image

My final USA Today Infographic recreated in an Excel Pie Chart looks like this:image

Here is the original.

 20130408_220237

Looks pretty good to me. What do you think?  Let me know in the comments below.

Also, here is a real quick representation of the radar pie chart that I showed at the top:image

Video Demonstration

Here is a video tutorial how to make your very own Excel Pie Chart Infographic:  http://youtu.be/y7wOcO6V11c

 

Please don’t forget to subscribe to my blog so that you get the latest posting delivered directly into your inbox as soon as it is ready.

Steve=True