<< . .

. 42
( : 51)

. . >>

The formulas used to create calculated fields and calculated items are not standard Excel
formulas. In other words, you do not enter the formulas into cells. Rather, you enter these
formulas in a dialog box, and they are stored along with the pivot table data.
The examples in this section use the worksheet database table shown in Figure 20-16. The
table consists of five fields and 48 records. Each record describes monthly sales information
for a particular sales representative. For example, Amy is a sales rep for the North region,
and she sold 239 units in January for total sales of $23,040.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 485

Figure 20-16: This data demonstrates calculated fields and calculated items.

Figure 20-17 shows the basic pivot table created from the data. This pivot table shows sales,
broken down by month and sales rep.

Figure 20-17: This pivot table was created from the data in Figure 20-16.

The examples that follow will create
¦ A calculated field, to compute average sales per unit
¦ A calculated item, to summarize the data by quarters

Creating a calculated field in a pivot table
Because a pivot table is a special type of data range, you can™t insert new rows or columns
within the pivot table. This means that you can™t insert formulas to perform calculations with
the data in a pivot table. However, you can create calculated fields for a pivot table. A
calculated field consists of a calculation that can involve other fields.
Part III ¦ Beyond Mastery: Initiative within Office

A calculated field is basically a way to display new information in a pivot table. It
essentially presents an alternative to creating a new Data field in your source database. A
calculated field cannot be used as a Row, Column, or Page field.
In the sales example, for instance, suppose you want to calculate the average sales amount
per unit. You can compute this value by dividing the Sales field by the Units Sold field. The
result shows a new field (a calculated field) for the pivot table.
Use the following procedure to create a calculated field that consists of the Sales field
divided by the Units Sold field:
1. Move the cell pointer anywhere within the pivot table.
2. Using the PivotTable toolbar, choose PivotTable _ Formulas _ Calculated Field.
Excel displays the Insert Calculated Field dialog box.
3. Enter a descriptive name in the Name field and specify the formula in the Formula
field (see Figure 20-18). The formula can use other fields and worksheet functions.
For this example, the calculated field name is Avg Unit Price, and the formula
appears as the following:
=Sales/™Units Sold™
4. Click Add to add this new field.
5. Click OK to close the Insert Calculated Field dialog box.

Figure 20-18: The Insert Calculated Field dialog box.

You can create the formula manually by typing it or by double-clicking items in the Fields list
box. Double-clicking an item transfers it to the Formula field. Because the Units Sold field
contains a space, Excel adds single quotes around the field name.

After you create the calculated field, Excel adds it to the Data area of the pivot table. You
can treat it just like any other field, with one exception: You can™t move it to the Page, Row,
or Column area. (It must remain in the Data area.)
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 487

Figure 20-19 shows the pivot table after you™ve added the calculated field. The new field
displays as Sum of Avg Unit Price. (You can change this text, if desired, by editing any of
the cells in which that text appears.) The calculated field also appears on the PivotTable
Field List toolbar, along with the other fields available for use in the pivot table.

Figure 20-19: This pivot table uses a calculated field.

The formulas that you develop can also use worksheet functions, but the functions cannot refer
to cells or named ranges.

Inserting a calculated item into a pivot table
The preceding section describes how to create a calculated field. Excel also enables you to
create a calculated item for a pivot table field. Keep in mind that a calculated field can be an
alternative to adding a new field to your data source. A calculated item, on the other hand,
uses the contents of items within a single field.
The sales example uses a field named Month, which consists of text strings. You can create a
calculated item (called Qtr-1, for example) that displays the sum of Jan, Feb, and Mar.
You also can do this by grouping the items, but using grouping hides the individual months
and shows only the total of the group. Creating a calculated item for quarterly totals is more
flexible because it shows the total and the individual months.
Part III ¦ Beyond Mastery: Initiative within Office

To create a calculated item to sum the data for Jan, Feb, and Mar, follow these steps:
1. Move the cell pointer to the Row, Column, or Page area of the pivot table that
contains the item that will be calculated. In this example, the cell pointer should be
in the Month area.
2. Use the PivotTable toolbar, and choose PivotTable _ Formulas _ Calculated Item
from the shortcut menu. Excel displays the Insert Calculated Item dialog box.
3. Enter a name for the new item in the Name field and specify the formula in the
Formula field (see Figure 20-20). The formula can use items in other fields, but it
can™t use worksheet functions. For this example, the new item is named Qtr-1, and
the formula appears as follows:
4. Click Add.
5. Repeat Steps 3 and 4 to create additional calculated items for Qtr-2
(=Apr+May+Jun), Qtr-3 (=Jul+Aug+Sep), and Qtr-4 (=Oct+Nov+Dec).
6. Click OK to close the dialog box.

Figure 20-20: The Insert Calculated Item dialog box.

If you use a calculated item in your pivot table, you may need to turn off the Grand Total display
to avoid double counting. In this example the Grand Total includes the calculated item, so each
month is counted twice. To turn off Grand Totals, use the PivotTable Options dialog box (see the
“Pivot Table Options” sidebar, earlier in this chapter).

After you create the items, they appear in the pivot table. Figure 20-21 shows the pivot table
after you™ve added the four calculated items. Notice that the calculated items are added to
the end of the Month items. You can rearrange the items by selecting and dragging. Figure
20-22 shows the pivot table after rearranging the items logically. (Calculated items were
made bold.)
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 489

Figure 20-21: This pivot table uses calculated items for quarterly totals.

Figure 20-22:The pivot table, after rearranging the calculated items.

A calculated item appears in a pivot table only if the field on which it is based also appears. If
you remove or pivot a field from either the Row or Column category into the Data category, the
calculated item does not appear.

It™s also possible to get quarterly summaries by grouping items. Because the month names
are not actual dates, the grouping must be done manually. Figure 20-23 shows the pivot
table after creating four groups. You create the first group by selecting the Jan, Feb, and Mar
items. Then you right-click, and choose Group and Show Detail _ Group from the shortcut
menu. Excel inserted the default name, Group 1 ” which you then change to Qtr 1. Next,
right-click the group item and chose Field Settings to display the PivotTable Field dialog
box. In this dialog box, you would specify the Sum function to summarize the grouped data.
Finally, you then repeat this process for the other three quarters.
Part III ¦ Beyond Mastery: Initiative within Office

Figure 20-23: Grouping items to show quarterly summary information.

This chapter demonstrated the powerful capabilities of Excel™s pivot tables. Hopefully, you
now have the knowledge and ability to create the kind of reports and calculations that will
make your work easier. Key points from the chapter include:
¦ After you create a pivot table, you can rearrange the information in almost any way
imaginable and even insert special formulas that perform new calculations.
¦ You can create a pivot table from a database by executing the following steps:
Specifying the data location, specifying the data, and specifying how you want to
display the relationship between that data and completing the table.
¦ One of the more useful features of a pivot table is the ability to combine items into
groups. To group items, select them, right-click, and choose Group and Outline _
Group from the shortcut menu that appears.
¦ A calculated field is basically a way to display new information in a pivot table. It
essentially presents an alternative to creating a new Data field in your source
database. A calculated field cannot be used as a Row, Column, or Page field.
¦ ¦ ¦

PowerPoint . . . .

Presentations In This Chapter

Creating user

Adding hyperlinks
to slides

S elf-running presentations do their jobs without any
Placing action
intervention from the audience or from you. If a self-running
presentation runs at a trade show and there is no one to hear it, it
runs nonetheless.
In contrast, user-interactive shows also lack a human facilitator or user-interactive
speaker, but they rely on an audience™s attention. The audience presentations
presses buttons, clicks a mouse, or clicks graphics or hyperlinks
on-screen to advance the show from one slide to the next, and
. . . .
they might even be able to control which content is displayed.
(See the “Interactive Presentation Ideas” section at the end of this
chapter for some usage ideas.)

What Is a Hyperlink?
The navigational controls you place in your presentation take
various forms, but are all hyperlinks. A hyperlink object is a bit of
text or a graphic that you (or your audience) can click to jump
somewhere else. When you click a hyperlink, you might jump to
a different slide in the same presentation, to a different
presentation, to another program on your computer, or even to an
Internet Web page.

Most people associate the word hyperlink with the Internet be-
Note cause of their familiarity with the Web and with hyperlinks on Web
pages. However, a hyperlink is simply a link to somewhere else;
it does not necessarily refer to an Internet location.
Part III ¦ Beyond Mastery: Initiative within Office

The most common type of hyperlink is underlined text. Hyperlink text is typically
underlined and a different color than the rest of the text on-screen. In addition, followed
links may be a different color from ones that you have not yet checked out, depending on the

If you want a hyperlink that never changes its color, place a transparent object over it, such as
Tip a rectangle, and apply the hyperlink to that object rather than the text. The user will think he is
clicking the text, but he will actually be clicking the rectangle. You can also assign a hyperlink to
a whole text box (manual text boxes only, not placeholder text boxes) as opposed to the text
within it.

You are not limited to underlined bits of text for your hyperlinks. You can also use graphics
or any other objects on your slides as hyperlinks. PowerPoint provides some special-purpose
graphics called action buttons that serve very well with hyperlinks. For example, you can
assign a hyperlink to the next slide to the action button that looks like a right arrow, as you
see in Figure 21-1 in the following section.

Navigational Control Choices
Figure 21-1 shows a slide with several types of navigational controls, any of which you can
use in your own slides.

Figure 21-1: Use one or more of the navigational aids shown here.
Chapter 21 ¦ Designing User-Interactive PowerPoint Presentations 493

¦ Action buttons: These graphics come with PowerPoint. You can set them up so
that clicking them moves to a different slide in the presentation. The ones in Figure
21-1 move forward (to the next slide) and back (to the previous slide).
¦ Hyperlink with helper text: The text “Click here to learn more” in Figure 21-1,
for example, provides built-in instructions for less technically sophisticated users.
The hyperlink could refer to a Web site, as in Figure 21-1, to a hidden slide in the
same presentation, or to any other location.
¦ Hyperlink without helper text: The text “Customer Satisfaction Surveys” in
Figure 21-1 is a hyperlink, but the audience must know enough about computers to
know that clicking those underlined words jumps to the slide containing more
¦ ScreenTip: Pointing at a hyperlink displays a pop-up note listing the address to
which the hyperlink refers. Viewers can jot it down for later exploration if they
don™t want to visit the page right now.
¦ Bare Internet hyperlink: The Internet address in Figure 21-1,
http://www.superiorquality.org, is also a hyperlink ” in this case, to
a Web page on the Internet. This kind of hyperlink can be intimidating for begin-
ners who don™t recognize Internet syntax, but it is very good for the advanced
audience member because it lists the address up front. No clicking or pointing is
required to determine the address.
¦ Instructions: If you do not build specific navigation controls into the presentation,
you may want to add instructions on the slide that tell the reader how to move
forward and backward in the presentation. The instruction box at the bottom of
Figure 21-1 does just that.

Choosing Appropriate Controls for Your Audience
Before you dive into building an interactive presentation, you must decide how the audience
will navigate from slide to slide. There is no one best way; the right decision depends on
your audience™s comfort level with computers and with hyperlinks. Consider these points:
¦ Is the audience technically savvy enough to know that they should press a key or
click the mouse to advance the slide, or do you need to provide that instruction?
¦ Does your audience understand that the arrow action buttons mean forward and
back, or do you need to explain that?
¦ Does your audience understand hyperlinks and Web addresses? If they see under-
lined text, do they know that they can click it to jump elsewhere?
¦ Is it enough to include some instructions on a slide at the beginning of the show, or
do you need to repeat the instructions on every slide?
Think about your intended audience and their needs and come up with a plan. Here are some
sample plans:
Part III ¦ Beyond Mastery: Initiative within Office

¦ For a beginner-level audience: Begin the presentation with an instructional slide
explaining how to navigate. Place action buttons on the same place on each slide
(using the Slide Master) to help them move forward and backward, and include a
Help action button that they can click to jump to more detailed navigation instruc-
¦ For an intermediate-level audience: Place action buttons on the same place on
each slide, along with a brief note on the first slide (such as the instruction in
Figure 21-1) explaining how to use them.
¦ For an advanced audience: Include other action buttons on the slide that allow the
users to jump around freely in the presentation ” go to the beginning, to the end,
to the beginning of certain sections, and so on. Advanced users understand and can
take advantage of a more sophisticated system of action buttons.

Understanding Kiosk Mode
Kiosk mode places the keyboard and mouse in limited functionality mode during the
presentation, to give you more control over the audience™s experience.
Specifically, here™s what happens when you use Kiosk mode:
¦ The keyboard does not work, except for the Esc key (which exits the presentation).
¦ The mouse can be used to click on action buttons and hyperlinks, but clicking in
general does not do anything.
¦ The control buttons do not appear in the bottom left corner of the display, and you
cannot right-click to open their menu. Right-clicking does nothing.
To turn on Kiosk mode, do the following:
1. Choose Slide Show_Set Up Show. The Set Up Show dialog box opens.
2. Click Browsed at a Kiosk (Full Screen).
3. Click OK.

Caution If you turn on Kiosk mode, you must use action buttons or hyperlinks in your presentation.
Otherwise users will not be able to move from slide to slide.

Using Action Buttons
Action buttons, which you saw in Figure 21-1, are the simplest kind of user-interactivity
controls. They enable your audience members to move from slide to slide in the presentation
with a minimum of fuss. PowerPoint provides many preset action buttons that already have
hyperlinks assigned to them, so all you have to do is place them on your slides.
Chapter 21 ¦ Designing User-Interactive PowerPoint Presentations 495

The action buttons that come with PowerPoint are shown in Table 21-1, along with their
preset hyperlinks. As you can see, some of them are all ready to go; others require you to
specify to where they jump. Most of the buttons have a default action assigned to them, but
you can change any of these as needed.

At first glance, there seems little reason to use action buttons that simply move the slide show
Tip forward and backward. After all, isn™t it just as easy to use the keyboard™s Page Up and Page
Down keys, or to click the left mouse button to advance to the next slide? Well, yes, but if you
use Kiosk mode, described in the preceding section, you cannot move from slide to slide using
any of the conventional keyboard or mouse methods. The only thing the mouse can do is click
on action buttons and hyperlinks.

Table 21-1
Action Buttons
Button Name Hyperlinks to
None Nothing, by default. You can add text or fills to the
button to create custom buttons.

Home First slide in the presentation. (Home is where you
started, and it™s a picture of a house, get it?)

Help Nothing, by default, but you can point it toward a
slide containing help.

Information Nothing, by default, but you can point it to a slide
containing information.

Back or Previous Previous slide in the presentation (not necessarily
the last slide viewed; compare to Return).

Forward or Next Next slide in the presentation.

Beginning First slide in the presentation.

End Last slide in the presentation.

Return Last slide viewed, regardless of normal order. This
is useful to place on a hidden slide that the
audience will jump to with another link (such as
Help), to help them return to the main presenta-
tion when they are finished.

Document Nothing, by default, but you can set it to run a
program that you specify.

Part III ¦ Beyond Mastery: Initiative within Office

Table 21-1 (continued)
Button Name Hyperlinks to
Sound Plays a sound that you specify. If you don™t
choose a sound, it plays the first sound on
PowerPoint™s list of standard sounds (Applause).

Movie Nothing, by default, but you can set it to play a
movie that you specify.

Setting up action buttons
To place an action button, follow these steps:
1. If you want to place the button on the Slide Master, display it
(View_Master_Slide Master).


<< . .

. 42
( : 51)

. . >>