York Times has been developing excellent data visualizations for years. A
couple of weeks ago, I came across one of the latest editions and I was amazed
by the simplicity and elegance of their graph on the U.S. tax burden evolution.
Take a look at the original article: http://www.nytimes.com/interactive/2012/11/30/us/tax-burden.html
this visualization so clever, is the fact that they have a ton of data
displayed in a single image, but they only display the values that the users
selects by hovering over the graph. In this way, you get a detailed picture of
30 years of data over 8 tax brackets without losing yourself. If you would have
placed this amount of data into slide, you would need a dozen or so to get the
So I immediately thought “but could this also be done on PowerPoint?”
And the answer is “Yes, it can”
article I will explain how this can be achieved. You can download a free copy
of the presentation at the end of this article.
1. Gather data and create chart
As the amount of data is pretty significant, we will use Excel to store our values. In Excel, we will also create the chart since you have more charting options available.
We will not
cover the entire creation process of the excel data and chart. Instead, you can
dive into the data yourself when you download the files. Basically this is the
setup in Excel:
For every year, we have created a separate workbook in excel. You could also create one, large excel file with tabs per year, but for now we have worked with multiple workbooks.
For every year of data, we are going to setup a separate slide.
Provide a slide name by entering a Title name, so you can distinguish the separate years easily.
3. Copy the
Excel chart and Paste in PowerPoint
Select the appropriate chart in excel and copy (CTRL+C) and use Paste Special > Paste Link to paste the chart in the correct slide.
Change the chart size and position in such a way that it fits your slide perfectly.
By using the Paste special option, every change in the original excel chart is reflected in PowerPoint. Even when the data changes or when you change the lay out of the chart (series), it is immediately updated.
Note: when you open up the PowerPoint file, you are asked if you want to update the links. Select "Update links" and wait for a couple of seconds for PowerPoint to update all charts.
mouse over hyperlinks by using Actions
There are several ways to add navigation to your slides. One way is to use triggers, a technique described in one of our earlier articles. Another way is to add Actions to shapes or pictures in your slide.
With Actions, you create hyperlinks to other locations in your presentation. You can choose between two options:
- Hyperlink with mouse click
- Hyperlink with mouse over
option is a fun feature since it will follow the hyperlink as soon as you hover
with your mouse over the shape (instead of clicking the shape).
Normally, this would make you a bit nervous since the slightest mouse movement could trigger a hyperlink, but in this case it is exactly what we need to mimic the NYT chart behavior.
We know that per tax bracket, there is 30 years of data. And that we have 31 slides (one for every year) in our presentation.
By adding a “invisible” shape to the chart (one for every year), we can add an Action hyperlink ( type mouse over) to create the required navigation.
- Insert a rectangle shape from the shapes library menu.
- Change the width to 0.05
- Align the shape to the first series in the chart
Tip: use the visibility and selection pane to add names to the shapes so you can easily identify them:
the shape and choose Insert > Actions > With mouse over and enter the slide
number you would like to navigate to. So for rectangle shape 1980, you would
create an Action hyperlink to slide 1980.
To this for every year in the graph. You end up with 31 very narrow rectangle shapes.
- Align them using the Align tool menu so they match the underlying charts series.
- Group the rectangle shapes
- Copy and Paste the grouped shapes for each tax bracket and align them with the chart series:
5. Copy and paste the grouped shapes to all slides
Before you Paste the grouped shapes to all slides, make sure that the fill level
and line color are set to “no fill”. This makes them "invisible" when you present your slides
And now it is time to see the magic!
Hit F5 (presenter mode) and navigate through your chart and see the chart values change!
What actually happens under water is that your navigate very fast through your 13 slides, because every slide references to all other slides in the same way…simple huh?
You can download the presentation + all the related excel files here.
Note: when you download the zip file, make sure that the excel and PowerPoint files are all extracted and remain in the same folder.
Note II: when you open the PowerPoint file, select yes to update the chart values from the source files (excel).