2009 has been a year of sharing here at Juice. First there was our long-used DTP methodology for interactive Excel reporting. Then we released our JuiceKit SDK. Today, I want to share another bit of trickery we've used to solve a common PowerPoint presentation problem.

The challenge

Let's say you need to produce the same presentation month after month, updating the data each time. Or maybe you have a set of slides that need to go to a bunch of different audiences each with their own specific market, product, business line, or industry.

Updating all the slides by hand can be tedious, slow and error-prone. The presentation is basically the same, you simply want to swap out the underlying data. You need something that acts like a "mail merge" for PowerPoint.

Our approach

When we've helped clients with this situation, our approach has been to create re-usable PowerPoint slides (i.e. templates) that link directly to a database. This gives us the ability to stamp out new presentation by changing the raw data underneath. Simple enough to say; not quite so simple in practice. Here are a few of the hairy bits:

1. Data structuring. We populate the data into a Windows-accessible SQL database such as MS Access or SQL Server so we can use SQL queries to define the data needed for our charts and tables.

2. Slide templates. We create slides with charts, tables, and text boxes that are formatted to account for the variance in the data that may need to be displayed. Ensuring that the charts always look good is surprisingly hard.

3. Connect templates to data. Originally we rolled our own solution by creating a "templating" language that we embedded in the notes section of the slides. More recently, we discovered PTReportGen, a tool that extracts data from a data source and populates it into PowerPoint. PTReportGen allows you to connect objects in the slides (i.e. charts, tables, text boxes) to results from SQL queries from our data source. For each slide, there is a .PTR file that connects the contents of the slide to the database.

4. Scripted production. PTReportGen gives command line control, allowing us to write Python scripts to cycle through our data and populate the charts and tables in our template slides. Because we are interested in generating dozens (sometimes hundreds) of versions of a single slide, our script iterates over the database to pull different results across multiple dimensions. Below is a bit of pseudo-code to give a sense of how the scripting works to produce slides by market and by demographic:

markets = ('Market1','Market2','Market3')
demographics = ('Demo1','Demo2','Demo3')
PTRFileName = 'C:\Documents\UserName\Desktop\MyReportGenerator.ptr'

for demo in demographics:
    for market in markets:
        ReportFileName = 'PathName\FolderName\demo\market.ppt'
        cmd = 'PPTReport.exe PTRFileName -demo -market'

1. Post-processing. While most chart and data table designs can be achieved by clever template layouts, some advanced designs involve additional intervention to achieve the desired level of polish. A python script combs through the result template and adds coloration and layout improvements.

It isn't simple, but once constructed this "slide factory" is a valuable capability that can free up an enormous amount of time from presentation grunt work. Here's a short video that gives you a sense of what the process looks like. Personally, I find the production of slides vaguely hypnotic.

Other approaches and resources

We are not the first people to encounter or solve this problem. Below are a few other resources on the topic. I'd be curious if there is a native MS Office solution that I could include in this list.

• PowerPoint Automation Toolkit: "With the PPTATK, PowerPoint becomes a best-case union of a presentation tool and a report writer. With the Tookit, you can build presentations which combine static slides from a slide library and data-driven slides which display charts, tables, and graphs from structured data sources."

• PresentationPoint: "Generate new up-to-date multimedia reports with 1 click only - put real-time data in your presentations."

• Microsoft Help: "Working with PowerPoint Presentations from Access Using Automation. Create a PowerPoint slide presentation from scratch using Access data."

• Stack Overflow discussion on "PowerPoint Automation from MS Access…queries to chart?"

I recently ran a few training sessions about how to visualize and present complex data. The high point was a series of “extreme slide makeovers” in which I honed the message and cleaned up visuals from existing presentations. Here are some ideas to tame busy, confusing slides.

1. Simplify your slide master, make room for content. Fancy borders, elaborate fonts, and background images do little to impress your audience. They leave little room for communication, either. For those saddled with frilly corporate slides, you’ll have to take on the Brand Standards Police.

   It may help to get quantitative. Consider this PowerPoint standard slide master. Less than 50% of the total slide area (highlighted in green) is available for content.

   

2. Say something once, why say it again? The Talking Heads sang: You're talkin' a lot, but you're not sayin' anything / When I have nothing to say, my lips are sealed / Say something once, why say it again?

   Wordy slides can be confusing and tedious. The author is using a lot of words—and often lots of qualifiers—in hopes that the core point lies somewhere within. The burden of synthesis is shifted to the audience. That’s not fair.

3. Make one point per slide. The take-away sentence on your slide should clearly state your point; the data on the slide should support that point. Any information that is tangential to the key concept can be pushed to an appendix or supporting slide.

4. Redundancies cause unnecessary repetition. I was surprised in my slide makeovers how often I found information that could be consolidated to simplify the slide. Redundancy came in many forms: multiple graphs repeating the same legend, axis labels that are described in a chart title, restating the same point.

5. Christmas is over, take down the decorations. Clear out clip art, “screenbeans”, and other images used to dress up the slide. Most effects are less “dazzling” than you might think. Eliminate gradients, shadows, 3D effects, and most animations. These design effects were exciting 10 years ago. But if they don’t help you communicate, move on.

   On the other hand, consider using full-screen photos as a way to convey a idea or theme, accompanied by few words. Here’s an example from a presentation I gave a few months back:

   

6. Reduce chart-junk. Excel and PowerPoint charts come pre-packaged with a heaping helping of chart-junk (“unnecessary or confusing visual elements”). Here are a few things I change in a default column chart: no shaded background, grey gridlines, no chart outline, no y-axis line, no column outlines, turn off auto resize text, change column colors to increase contrast. If you want to save yourself from chart-junk induced carpal tunnel syndrome, check out Chris’ chart cleaner Excel add-in. Sometimes charts aren’t necessary at all. If you’re using a pie or stacked column chart to show a single data point, the number alone will do the job more clearly.

   

7. Delete your “Text-junk” too. Text can contain “chart-junk” too—visual distractions in text that dilute your message.

   • Title Capitalization or Other Excessive and inconsistent use of Capital Letters. Title caps doesn’t make sense to use and is more difficult to read.
   • Underlining. If you want to emphasize a word or phrase, use bold or italics.
   • Don’t use bullets when there is only one item or sentence. People have become so accustomed to using bullets that they’ll use them when they are totally unnecessary.
   • Bad fonts: The worst is Comic Sans MT, as the LMNOP blog describes: “These days, just like an e-mail from an “@ aol.com” address has a distinct lack of credibility, an e-mail written in this font makes the sender seem ridiculous and out of touch.”

8. Simplify style and formatting. Inconsistent colors, fonts, font sizes, and other styles are a subtle distraction. Limit yourself to three font colors (emphasis!, normal, low-emphasis), three font sizes, and three font styles. Here’s an example.

   Three colors

   

   Three sizes

   

   Three font-styles

   

   Putting it all together

   

All these points can be summed up as: Make everything on your slide serve your story. Best wishes for 2008!

Developer's Guide - Google Chart API - Google Code

Beautiful stuff, particularly the Venn diagram.

Align Journal - BI Worst Practices

We often see articles on BI "Best Practices" here is an article telling us what NOT to do.

flot - Google Code

Attractive Javascript plotting for jQuery.

ongoing · On Communication

Interesting blog post about how different forms of communication rank for immediacy, lifespan, and audience reached.

The Excel Magician: 70+ Excel Tips and Shortcuts to help you make Excel Magic : Codswallop

SlideShare

Source for presentation ideas.

Find and Download Great-Looking Excel and PowerPoint Charts

Chart Chooser is an online tool that answers two questions we commonly get:

1. What type of chart should I use to show my data?
2. How can I make good looking Excel or PowerPoint charts?

Chart Chooser is easy:

1. Check the boxes on the left that best describe your objective
2. Select the chart that you want to use
3. Choose from Excel or PowerPoint downloads to get a formatted chart template

A few notes about Chart Chooser:

• Thanks to Andrew Abela of Extreme Presentations for inspiring Chart Chooser with his “Choosing a Good Chart” post and for working with us to put this tool together.
• We’ve tried to make the charts both Tufte-compliant (i.e. minimal chart-junk) and visually attractive (thanks to Google for the color scheme).
• Feel free to suggest other types of charts that you’d like to see in the Chart Chooser. Send an example to chartchooser@juiceanalytics.com.
• If you’d like a customized version of Chart Chooser for your organization, write us at chartchooser@juiceanalytics.com or call me at 202.251.7750.

Death by PowerPoint » SlideShare

Quick inspiration for great presentations.

Role Modellers - Home

Software to manage human collaborative work via a workflow motif. Driven by Human Interaction Management.