The Scenario
You are the manager and an associate is presenting data from an Excel workbook in a PowerPoint presentation.
Presentation #1 – No Animation
Here are the main points being conveyed by the associate:
- The countries with the highest sales are USA, Germany, and India.
- Although USA has the greatest sales in absolute terms, there is a large negative deviation to budget due to an unplanned shutdown. Germany is also behind budget, though not to as great a degree as USA.
- India is ahead of budget by more than 20% due to a successful product Launch
Presentation #2 – with Animation
The same information is conveyed, but by bringing in the objects one at a time, and in time when they are discussed, the information is not only easier to understand, but each additional block of information has greater impact. But why does animating the chart(s) add so much to the conversation.
Let’s look at the pros and cons.
Pros and Cons of Both Presentations
Presentation #1
The Cons
- All the information is presented the moment the slide appears. The audience does not know where to apply their attention regarding what you are discussing; they lose focus.
- The audience will begin examining data that is not being discussed and attempt to infer what the information about.
- The audience is no longer listening to the presenter and key points being discussed are missed due to the lack of audience focus.
- It’s difficult to identify individual characteristics of the data when all data points are the same color.
The Pros
- The slide was easy to make.
- More time to surf the Web for exciting XelPlus videos. 😊
Presentation #2
The Cons
- Takes more time to craft the story.
- Need to invest some time learning a few animation skills in PowerPoint.
- Not as much time to surf the web for exciting XelPlus videos.
The Pros
- The information appears on the screen as the story points are made by the presenter.
- Audience focus is maintained resulting in no missed information, reduced confusion, and increased comprehension.
- Story points are easily seen due to color changes.
- Audience doesn’t have to play “Match the Visual with the Speaker’s Message”.
Why is Animation so Discouraged in the Business World?
As with any tool, misuse of the tool can bring about a negative reputation.
Too many business presentations use visually jarring, distracting, or confusing animation. Animation that serves no purpose, other than to showcase the PowerPoint skills of the presenter, is a BIG problem with most animated slideshows.
Animation should serve a purpose; it should help tell the story. If you can point to a piece of animation and ask, “How does this help make the message easier to understand?”, and the answer is, “It doesn’t”, GET RID of the ANIMATION!!!
As with every element in a story, if it doesn’t help push the story in a positive direction, it shouldn’t be part of your story.
Let’s Use Animation EFFECTIVELY!
This will require a tag-team effort between Excel and PowerPoint. Our data and charts will be created in Excel but animated in PowerPoint.
Begin with Excel
Below is our data set.
Start by creating a chart for the Country vs. Actual information. We’ll select cells A3 through B10 and then select Insert (tab) -> Charts (group) -> 2D Clustered Column (top left).
Apply the following modifications to the chart:
- Right-click on a bar and select “Add Data Labels”
- Delete the vertical axis
- Remove the horizontal gridlines
- Delete the title (we’ll have our own title in the PowerPoint presentation)
- Right-click on a bar and select “Format Data Series”. Under “Series Options” (bar chart icon), set the Gap Width to 80% to bring the bars closer together.
- Select the “Fill & Line” icon (tilted paint bucket) and set the fill color to a solid gray.
The result should appear as follows.
Finding the Three Largest Values
We now need to create a second series of data that will highlight the bars of the top three countries in a different color. Because Conditional Formatting doesn’t work with charts, we must use a bit of chart trickery to get the look we a striving for. The best part about the second data series is that PowerPoint will allow us to display them in sequence, giving us the illusion of changing the color of the bars.
- Select cell D3 and add the title “3 Largest Sales”.
- Select cell D4 and enter the following formula:
=IF(B4>=LARGE($B$4:$B$10,3),B4,””)This formula answers two questions:
- (using LARGE) – Is the value of B4 greater than or equal to the 3rd largest value in range B4:B10?
- (using IF) – If so, display the value in cell B4; otherwise, display nothing (two double-quotes).
Fill the formula down from cell D4 through cell D10 and observe the results.
Add the New Series to the Chart
We will add the new column of “3 Largest Sales” values to the existing chart.
Right-click the chart and click “Select Data…”.
In the Select Data Source dialog box, click the “Add” button on the left under “Legend Entries (Series)”.
In the Edit Series dialog box, set the “Series name” to =Start!$D$3 and the “Series values” to =Start!$D$4:$D$10.
NOTE: In your spreadsheets, replace the word “Start” with the name of the sheet that contains the data.
Our updated chart should appear as follows.
ANOTHER NOTE: A super cool way to add the new data series to the chart is to highlight the data and it’s heading (cells D3:D10) and click COPY (or CTRL-C). Next, click in the corner of the chart and click PASTE (or CTRL-V). Like magic, the new data appears.
The chart needs a small amount of tweaking to be perfect, so select the second data series and perform the following actions:
- Set the Fill color to light green
- Set the Series Overlap to 100%
As an added touch, set the chart’s border (Shape Outline) to No Outline. The result should appear as follows.
Let’s Calculate Change to Budget
Our second chart will calculate the variance from Actual to Budget. The formula is quite simple.
Select cell E3 and add a heading, such as “Change”, and select cell E4 and enter the following formula.
=B4-C4Fill the formula down from cell E4 to cell E10.
To create the second chart for variance, select cells A3:A10, press and hold the CTRL key and select cells E3:E10. Add the second chart the same way you created the first chart: Insert (tab) -> Charts (group) -> 2D Clustered Column (top left).
The second chart is not easy to understand…
… so, we’ll perform the following modifications:
- Remove the Chart Area fill color (No fill). This will make it easier to see the two chart’s data when placed close to one another.
- Delete the vertical axis.
- Delete the horizontal gridlines.
- Add data labels to the bars.
- Set the Gap Width to 500%.
- Remove the title.
- Position the chart below the first chart and set them to as close to the same width as you can.
- Set the chart’s border (Shape Outline) to No Outline.
A modification that is not as straightforward as the previous is the removal of the horizontal axis labels (country names).
You would think it would be as simple as selecting a country and pressing the Delete key on the keyboard. It can be that simple, but that technique is too destructive. We want to remove the country names but not the horizontal line that represents the axis.
To remove the country names without affecting the line, select a country name. In the Format Axis panel select Axis Options -> Labels -> Label Position and set the dropdown to None.
A nice touch would be to have the negative value bars formatted in a different color. To achieve this, select a bar in the lower chart. Next, in the Fill options, check the box labeled “Invert if negative” and set the color to the same light green as the first chart.
Once the first (positive) color is set (the same light green as in the first chart), a second color picker will appear allowing you to select the negative value color. We’ll set ours to light orange.
The result is as follows.
Transferring the Charts to a PowerPoint Slide
If we were to leave the charts in Excel, a common strategy would be to group the charts together as a single object. This makes moving and resizing easier.
Because we wish to send these charts to a PowerPoint slide, we can’t group them together. Grouped object are rendered as pictures in the destination application, thus losing much of the chart functionality.
Our strategy will be as follows.
- Select a corner of the first chart and click COPY (or CTRL-C).
- Switch to PowerPoint and select the destination slide.
- Click the bottom part of the PASTE button and select “Keep Source Formatting & Ling Data (F)”.
- Perform steps 1-3 on the second chart.
- Position and resize the charts as you see fit.
Because we retained full chart functionality in our pasted charts, you can feel free to change chart colors, font sizes, or add/remove features without affecting the original charts in Excel. Because we are likely to show this to a group of people, we can do things like make the data labels larger. This makes the values easier to read for those farther back in the room.
What IS connected between the Excel charts and the PowerPoint charts is the data. If the data changes in Excel, the Excel charts will automatically update. This update will carry over to the PowerPoint charts to display the same information.
Time to Start Animating
As the charts stands (see previous image), it has a lot of information to impart.
We need to bring portions of the chart to the attention of the audience in small doses. This way, we maintain audience focus and improve comprehension.
Animating the Top Chart
Select the first chart (top) and switch to the Animations tab.
Although you can select any animation you wish, I find that when working with column charts, the Wipe effect is visually effective.
The Wipe effect gives the chart the illusion that the bars are “growing” out of the horizontal axis.
The default effect is to bring in the entire chart as a single object. We will change that setting by clicking Animations (tab) -> Animation (group) -> Effect Options -> Sequence -> By Series.
Remember that the first series in our chart are the gray bars and the second series are the green bars.
Animating the Bottom Chart
Select the second chart (bottom) and apply the Appear animation to the chart.
As with the Wipe effect used earlier, the entire chart appears in a single instance. We want each data point to appear one after the other. Select a bar in the lower chart and set the Effect Options to animate By Category.
This setting gives us the flexibility to bring each data point onto the screen in time with the telling of our story.
Since many of the data points contain little change, we want to focus on the countries with the most change (i.e. USA and India.)
We have two main adjustments to apply:
- modify the data points for USA and India to have a Wipe effect, leaving the remaining countries as Appear
- make the data points with little change appear at once. We will make an adjustment to the animation sequence of the countries Canada, Germany, China, and Brazil so they appear as a singe block of data points.
Activate the Animation Pane by selecting Animation (tab) -> Advanced Animation (group) -> Animation Pane.
Expand the animation list for the second chart (double down arrows) to reveal the individual animations.
NOTE: There is limited room to display the names of the chart elements. If you are unsure as to which object in the animation list applies to which object in the chart, you can hover over a list item to see the complete name of the associated chart object.
We will use the animation sequence numbers when reference items in the list (ex: 5 = USA and 10 = India.)
Select USA (sequence number 5) and set the animation effect to Wipe.
Next, change the direction of the Wipe effect for USA (sequence number 5) by changing the Effect Option from From Bottom to From Top. This adjustment is useful because USA has a large negative variance.
For the next four countries, because there is little difference between them, and their amounts are small, we want them to appear simultaneously.
Select sequence numbers 7, 8, and 9 (Germany, China, and Brazil) and set the Start option to With Previous. This will cause those 3 bars to appear when Canada’s data point (sequence number 6) appears.
Select sequence number 10 (India) and set the animation effect to Wipe. The default wipe direction is From Bottom which will work well for positive values.
The result should appear as follows:
Testing the Dynamic Nature of the Chart
To test the chart’s updating feature, we will return to the Excel file and change the Actual value for India to 9,200. This will produce a different “Top 3” color set.
If we return to PowerPoint, we can see the data has already updated.
If for some reason your chart does not update automatically, you can force an update by selecting Chart Design (tab) -> Data (group) -> Refresh Data.
Practice Workbook
Feel free to Download the Workbook HERE.
Feel free to Download the Slidedeck HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
