The last few "Computing at Work" columns have
explained how to create charts in Excel and also how
to incorporate Excel data into Word. In this column,
we bring that information together in a "real world"
example, so you can get a better idea of how you
might use these features yourself. Here’s the situation:
recently, we did a survey of Computor
Companion readers. The resulting statistics were not only
interesting, but also forced me to remember how to use
Excel. I’m a numbers-phobe, so doing anything in
Excel makes me nervous. But when you have data to
be analyzed, you must persevere.
After I entered the data into my simple spreadsheet, I wanted to create a nice document that
summarized the results in Word. For that I needed
the type of nifty colorful pie charts that are easy to
create in Excel. But I wanted those pie charts in my
Word summary document. (I know, I’m demanding.)
Fortunately, I didn’t have to reenter the data into
Word because in recent versions of Office, Microsoft
has made it much easier for its programs to "talk" to
one another. To understand how you can get your
data back and forth among your programs, it helps to
understand a little about object linking and
embedding (or OLE for short). OLE is used by Office
programs to share data.
You probably already know about cutting and pasting. But, with OLE, there’s more than one
way to cut and paste. You can paste data into Word
as text or a picture, paste a link to the data that
automatically updates when you change the original source file, or embed it so you can double-click
the object and edit the data from within Word. To
follow the examples here, you need to have both Word
and Excel running. You use the Task bar at the bottom
of your screen to switch between them.
Creating the Spreadsheet
My Excel spreadsheet is super simple, in fact, it’s
so easy even a math class reject like me can figure it
out. There are probably cooler ways to enter data
using custom forms and other nifty tools, but my
approach has the advantage of being really fast. So, here’s
how I created my quick and dirty spreadsheet:
- I copied the survey questions from the Word
file I’d originally used for the survey itself into
the first column.
- I added a column (B) for the number of
responses to each answer.
- I put the total number of responses in a cell (E1).
- Then I created a column called Percent,
which contains a formula that is the number of responses divided by the total number of responses.
For example, in the first question, to figure out
the percentage of respondents that are between the
ages of 45 and 54, I clicked in cell C9 and entered
this formula into the formula bar: =B9/$E$1. The
number of respondents is in cell B9 and the total
number is in E1 (see Figure 1). The dollar signs mean
that the cell reference is "absolute" so when I copy
the formula, that cell reference doesn’t change. In
contrast, the reference to cell B9 is "relative"
which means that if I copy it to the next row (which I did)
it will update to B10. Understanding relative and
absolute cell references helps you know what’s going
on when you copy your formulas in a spreadsheet.
Figure 1. Calculating percentages in Excel.
So with this ultra-simple layout, entering the
survey data was easy. As I went through my survey
replies, I incremented the numbers in the
appropriate response, depending on the person’s answers.
Making Pies
To create my pie charts, I highlighted the
appropriate cells and then chose Insert|Chart. Under
Standard Types, I chose Pie, then clicked Exploded pie with
3-D visual effect, and then the Next button (see
Figure 2). I opted not to include a title on the chart in
Excel because I planned to put them into Word along
with my descriptive text. So I finished the Wizard,
selected the pie chart, and chose Edit|Copy. Then
I switched to Word.
Figure 2. Using Excel’s chart wizard.
Bringing Pies into Word
After you’ve copied the pie, you have some choices
to make. As noted before, with OLE you have a
number of ways of pasting. In this case, Excel is the
source program and Word is the
destination program. The way you choose to insert the information depends
on how you want to store and edit the data later.
You can link or you can embed. Each method has
advantages, depending on what you want to do. If
you choose Edit|Paste Special then click the Paste
radio button, you embed your Excel data into the
Word document. When you double click it, Excel tools
appear within Word (this technique is referred to as
in place editing). If you change the original
spreadsheet data, your Word file isn’t updated. In contrast, if
you click the Paste Link radio button, you just get a
picture of the original file. Any time you change the
Excel data, the changes appear in the Word
document as well. When you double-click to edit the file,
you go directly to Excel.
Sometimes getting the formatting of the charts right in Word can be tricky. I’m really fussy and
I wanted to remove a lot of the white space around
the chart. So in the Paste Special dialog box, instead
of leaving the default choice of pasting a Microsoft
Excel Chart Object, I switched it to a Picture (Enhanced Metafile). By pasting the chart as a picture,
I can use Word’s picture editing tools to modify
it. When I clicked my pie chart, the Picture Toolbar
appeared and I was able to crop out the extra
white space without the need to return to Excel
and twiddle with the chart options (see Figure 3).
Figure 3. Cropping the chart in Word.
Many times the choices you make have more to do with which software you feel most comfortable
with. For number-phobic types like me, editing a chart as
a graphic makes sense. However, if the charts were
going to change repeatedly, selecting Paste Link
and manipulating the charts in Excel would have been
a better option. Although software sometimes seems
to have an overwhelming number of features, by
learning more about your options, you can make the
best decisions for your situation.