Show-and-Tell in Real Time

Your boss asks you to make an important financial presentation to a group of potential shareholders who are not particularly adept at reading spreadsheets crammed with numbers. Although the financial information is collected and calculated in Excel, he wants the presentation to be viewer friendly and have more pizzazz than a spreadsheet. It may be a cliché, he says, but a picture is worth a thousand words—or in this case it’s worth more than a thousand numbers.

So you tell your boss you’ll put on a sprightly financial show-and-tell in PowerPoint, the Microsoft presentation application.

“Whoa,” he interrupts, “don’t you know that last-minute sales data will come in from the accounting department just minutes before your scheduled presentation— which means there’s no way you’ll have time to plug the new numbers into the PowerPoint slides?”

“I can solve that problem,” you reply confidently. “With just a few keystrokes and mouse clicks, I can link the Excel cells to the appropriate slides in PowerPoint so that when the accounting department changes the spreadsheet data, those numbers will change immediately in PowerPoint, too.”

HOW IT’S DONE

This article will demonstrate how to create such a link. Since all integrated software suites—which typically include a word processor, a spreadsheet, a database and a presentation application—allow users to create links between any of its applications so that data changes in one will be reflected in the other, what you learn here also can be adapted for use in other office suite applications.

For this demonstration, using the 2000 version of Microsoft Office Suite, we’ll demonstrate how to convert an Excel spreadsheet into a chart and then paste and link it to a slide in PowerPoint.

As you know, it’s a cinch to simply copy a chart created in Excel and paste it into another application. However, that method only lets you create a static image. If the Excel numbers change and you want them to be reflected in the copy made in the other application, you’ll have to make the changes manually. If, however, you link the original spreadsheet to the copy, changes in the original will flow automatically to the copy—in this case to a PowerPoint slide. To accomplish that, we’re applying a technology called OLE (object linking and embedding).

GETTING STARTED

We created a simple spreadsheet in Excel that shows quarterly and annual revenues of Main Golf Course, broken down by revenue sources. Using those data, we created a pie chart to illustrate the revenue contributions (exhibit 1). Since the data and the chart are automatically linked, any changes in the underlying data in the spreadsheet change the chart at the same time.

So let’s get started. Open PowerPoint, choose Blank Presentation and click on OK. That brings up the New Slide dialog box. Select a blank slide and click on OK. Your screen should now resemble exhibit 2. This is the slide where you want to place the linked chart.

Return to Excel and open the file containing the data and chart you want to link to PowerPoint. Highlight the pie chart and you will see black selection handles (see exhibit 1). In addition, Excel places colored borders around the data that were used to create the chart.

Now click on the Copy button on the toolbar or right-click for a menu and click on Copy. That copies the chart to the Clipboard. You should now see a moving border around the selected chart.

Return to the PowerPoint slide and select Edit, Paste Special. That will bring up the Paste Special dialog box (exhibit 3), which enables you to create an active link between the Excel chart and the PowerPoint slide.

Caveat: Don’t just switch to PowerPoint and click on the Paste button. If you do, you’ll paste the Excel chart in the presentation but you won’t create an active link.

Now on the Paste Special dialog box, click on Paste link, making sure Microsoft Excel Chart Object is selected in the As: box; this tells PowerPoint the shortcut to the link. Technically, the Excel chart is placed in the PowerPoint slide as an object (exhibit 4). You can now resize the chart object or center it in the available space.

To test the link, change the data in the Excel chart and observe the update in the associated PowerPoint chart.

Troubleshooting: If your changes don’t show up in the slide, it’s likely that the PowerPoint link is defaulted to manual—not automatic. To correct that, go to Edit, Links; that will evoke the Links dialog box. Make sure the Update option button is set to Automatic, and then click on Update Now and close the box.

EXCEL IN POWERPOINT

There’s still another way to use the power of Excel while you’re in PowerPoint. You can embed an Excel spreadsheet in a PowerPoint slide, thus actually creating a fully functioning worksheet while you’re working in PowerPoint. Whenever you access the embedded spreadsheet—by double-clicking on it—you can use all the features of Excel to revise the spreadsheet. It’s not even necessary to have Excel running before you create the embedded spreadsheet; PowerPoint automatically accesses Excel when you create or edit the spreadsheet and closes it when you finish working.

Here’s how it’s done: Open PowerPoint, create a new file and click on Insert, New Slide (exhibit 5).

Click on Insert, Object; that opens an Insert Object dialog box. From the menu select Microsoft Excel Worksheet and click on Create New (exhibit 6) and click on OK.

That will embed a blank Excel workbook into the slide (exhibit 7). Notice the dark ropelike border and the selection handles that surround the spreadsheet. These indicate that Excel is activated. Notice, too, that all Excel’s toolbars, menu commands and rulers are displayed on the screen—so you’ll have full access to all its functions.

To edit the Excel worksheet from within the PowerPoint presentation, double-click on the worksheet. That will swap the menu bars to Excel’s, and the worksheet will display row and column headings. To save any changes in the spreadsheet, just click anywhere outside the worksheet on the PowerPoint slide and the data is saved. Since the Excel file is entirely embedded in the PowerPoint presentation, it isn’t saved as a separate Excel file.

HYPERLINK A PRESENTATION

Another way to incorporate Excel data into your presentation and make your presentation interactive in real time is to add a hyperlink to your Excel data—to either a Web site or to a file in another open program—in our case an Excel workbook.

Creating a hyperlink to an Excel workbook is similar to creating a hyperlink to a Web site. Let’s create a hyperlink to the workbook from which we linked our chart earlier.

Start by creating a new slide by clicking on Insert, New Slide. Your screen should resemble exhibit 8.

Click to the right of the bullet, where it says Click to add text. Enter the text that will become the hyperlink to your Excel Workbook; we will type 2001 Revenue Data. Click on the text just entered to highlight it (exhibit 9).

To create the hyperlink, click on Insert, Hyperlink (exhibit 10).

That will evoke the Insert Hyperlink dialog box (exhibit 11).

Under Browse for: click on File; that will open the Link to File dialog box (exhibit 12). From this dialog box locate your target Excel file. Once you find it, click on OK and you’ll be returned to the Insert Hyperlink dialog box. You should see the name of your spreadsheet in the box under Type the file or Web page name. Once you confirm the file name, click on OK.

You should also see the text you entered in the slide appear underlined, indicating a “clickable” hyperlink (exhibit 13). When you run your presentation and click on the link, PowerPoint will launch Excel and open the Excel Workbook.

As you can see, creating a dynamic PowerPoint presentation that automatically updates when changes are made to the underlying spreadsheet data is not difficult. Setting up your spreadsheet with automatic links will save you valuable time in preparing presentations as well as ensure that the information in your slide show matches the data in your spreadsheet.

KIMBERLY A. KILLMER, PhD, is an assistant professor at Montclair State University, Upper Montclair, New Jersey. Her e-mail address is [email protected]. NASHWA GEORGE, PhD, CMA, is an associate professor at Montclair State University. Her e-mail address is [email protected].