Apollo 11 & Excel – How did we do that?

Today's post is brought to you by Sam Radakovitz, a program manager on the Office User Experience team.

Thanks for all the support and kind words for our History Reimagined video series. We had a great time making these videos, especially the one about Excel 2010 and Apollo 11. :-)

After we posted the Excel video, some of you asked us how we achieved certain effects. Today, I'll walk you through a couple of the more interesting visualizations. To follow along, you can download a copy of the source workbook, which includes each of the examples described in this post. Also, if you want to snag the mission data, feel free to get it from the NASA History Program web site. 

Effect #1: The chart that animates the flight path

One of the coolest parts of the video is the chart that animates the flight path of the Apollo 11 spacecraft:

While this isn't the exact path followed by the spacecraft, we included this animation to showcase a charting trick or two.

In the source workbook, on the Example_Chart worksheet, you'll find a simplified version of the chart:

This chart has two series, dotted and normal. The dotted series represents the entire path, in this case 1-7. The normal series gets its values based on formulas, and the formulas check the position cell. If the row()-1 of the normal cell is less than or equal to the position, then the cell gets a number and the green line (normal series) will draw. If it's greater than the position cell, then it returns a #N/A, which means don't draw this on the chart. Based on that, you can change the position cell's number and get more or less of the green line (normal series) to draw on the chart. Then, you can create code that slowly increases the position number to get it to animate. If you want, you can also hook up a scroll bar form control to update the position cell — this appears undereath the chart in the screen capture above.
 

Effect #2: The Stage slicers

In the video, when the Stage slicers are clicked, the spacecraft images update accordingly.

In the source workbook, the Example_Slicer sheet includes a simplified version of this effect:

Here, we have a simple PivotTable along with a slicer. When you click the slicer buttons, the car or truck will respond by fading out and losing color. This grayed-out effect is achieved by using VBA code and stacked images. (In the screen capture above, I staggered the full-color and gray truck images so that you can tell that there are two objects.)

When the slicer changes, you get a PivotTable event, and on that event, you roll through the items and determine what is visible. So, if the truck is filtered out, you hide the full-color version of the truck, and the gray version remains visible. When the filter is removed, you make the full-color truck visible again. As long as the images are stacked, you won't need to hide the gray one.

Similarly, if you have multiple shapes or pictures that make up a single slicer item, you can group those objects, and then show/hide the group. 

It was a lot of fun for the product teams to work on the History Reimagined videos. We hope you enjoy them as much as we do! 

 -- Sam Radakovitz