How do you create a chart in PowerPoint that looks like this? It has:
- 2 different Y-axes each with a different scale
- 2 different chart types–column and line
I learned this feature of PowerPoint charts recently from my friend and fellow PowerPoint MVP, Echo Swinford. She is one of the top experts in the world on PowerPoint and specifically PowerPoint charts.
Charts like this are very useful when you’re comparing 2 very different types of data. This chart is from a course of practitioners of the Transcendental Meditation technique that took place in 1993 in Washington, D.C. It compares police statistics of homicides, rapes and assaults during the course with the number of people who were on the course, which lasted 8 weeks.
Here’s what the data looks like in Excel.
Note: I found the actual chart online and estimated the numbers from the chart. I didn’t have the actual numbers, so these numbers will be slightly different from the numbers used in the study.
Follow these steps to create a chart like this in PowerPoint 2007 or 2010:
1. In PowerPoint, right-click off the slide, choose Layout, and choose the Title & Content layout.
2. On the slide, click the Chart icon, which looks like a column/bar chart.
3. In the Insert Chart dialog box, choose one of the types of chart that you want. I chose Column. Click OK.
4. A temporary Excel spreadsheet opens with dummy data. Copy the data from your spreadsheet to the clipboard, switch to the temporary Excel spreadsheet, click in cell A1, and paste.
5. Drag the bottom-right corner of the blue border to fit your data.
Here’s what my data looks like.
6. Return to your PowerPoint slide to look at the chart. Here’s what mine looks like.
7. Oops! That’s not right. The weeks should be on the X -xis, along the bottom. You often have to switch the rows & columns. You do this on the Chart Tools Design tab; click the Switch Row/Column button in the Data group. Here’s the result.
That’s much better. Now, the weeks of the course are along the X-axis where they belong. Of course, the data, specifically for the % change in violence isn’t right and that’s what we’ll change.
8. Because the change in violence data is negative numbers, you can’t see that series. But click along the X-axis and you can select it, as you see here. Note the round “handles.”
9. First, we’ll change the scale for this series. Right-click the selected series and choose Format Data Series. In the Format Data Series dialog box, with the Series Options category selected, choose Secondary Axis, to plot that series on a secondary axis.
10. Now look at the chart in PowerPoint. All of a sudden, you can see the data!
11. But having both sets of data as columns is confusing. You can emphasize the difference — making the chart clearer — by turning one of the series into a line. Right-click the series again and choose Change Series Chart Type.
12. The Change Chart Type dialog box opens. It’s the same as the Insert Chart dialog box. Choose a different chart type. In this case, I chose a Line chart type. Click OK.
Make some minor adjustments and you have the slide I showed at the top of this post.
Do you have a use for this type of chart? Share your experiences with charts that have 2 chart types and 2 Y-axes.