This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Latest Stories

Latest Stories

Show-and-Tell in Real Time

Link a spreadsheet to a PowerPoint slide for up-to-the-minute visuals.

BY KIMBERLY A. KILLMER AND NASHWA GEORGE

Related

TOPICS

Uncategorized Article

our 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.

Key to
Instructions

To help
readers follow the instructions in this
article, we use two different typefaces.

Boldface type is used
to identify the names of icons, agendas
and URLs.

Sans serif type
indicates commands and instructions that
users should type into the computer and
the names of files.

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.

Exhibit 2

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.

Exhibit 3

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.

Exhibit 4

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).

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 .

Exhibit 6

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.

Exhibit 7

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.

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).

Exhibit 9

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

Exhibit
10

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

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 .

Exhibit
12

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.

Exhibit
13

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 killmerki@mail.montclair.edu
. NASHWA GEORGE, PhD, CMA, is an associate
professor at Montclair State University. Her
e-mail address is georgen@mail.montclair.edu
.

An Invitation

If you have a special how-to
technology topic you would like the
JofA to consider for
inclusion in this series, or an
application shortcut you devised and
would like to share with other
professionals, contact Senior Editor
Stanley Zarowin. His e-mail address is
zarowin@mindspring.com
.

Are you working with the best technology? Do you know how to help your clients determine if their technology stack measures up? In this free report, J. Carlton Collins, CPA, explains how to answer those questions via a technology assessment engagement.