Microsoft Office Tutorials and References
In Depth Information
Updating the Pivot Report
Click Move to Column Labels . This is what happens (Figure 8–14):
Figure 8–14. The same data, now running horizontally
We ’ v e pi v ote d the r e por t. T he Sa l e spe r son n a me s n ow occupy a succe ssi on of columns stretching
horizontally across the report, framing a new way for looking at precisely the same data. ( Note: You
can also move the buttons from one area to another by clicking on a button and dragging it into a
di ffe r e n t ar e a. You’ l l al so g r adual l y di scov e r , to y our de l i g ht or be fuddl e me n t, that Pi v otT abl e s offe r
many ways for doing the same things.) And we’re also learning that the Column Labels area is really a
horizontal version of Row Labels—as with that latter area, any source data assigned to the Column
Label space will be listed uniquely .
Updating the Pivot Report
There’s something else to keep in mind, this time about the source data that contributes to a pivot
report. If you add new records to the source data, or if even if you change any of the data in an existing
record, you need to refresh the pivot report. Unlike standard Excel formulas, PivotTables won’t
automatically recalculate when the data contributing to them is changed. To refresh a PivotTable, click
PivotTable Tools Refresh (ke y boar d e qui v al e n t: Al t+F5). T o de mon str ate , l e t’ s add a r e cor d to our
source data. Return to the source data, and in Row 801 enter these data: UK, Buchanan, 2/5/10, 11111,
$567.00. Then click back onto the pivot report and click the Refresh command. You’ll see (Figure 8–15):
Figure 8–15. The pause that refreshes: updating, or refreshing, the PivotTable with a new record—a new
Buchanan sale
Note that Buchanan’s sales total has bumped up to 69359.35, reflecting the additional $567.00 sale.
Way to go, Buchanan.
The Refresh All option, r evealed by the dr op-down menu by Refresh, also refreshes source data
that may be drawn from an external source, such as a Web site or an Access database (You’d click Use
an external data source in the Create PivotTable di a l og box to con n e ct to such a sour ce .). Note a s we l l
that if you build several different pivot reports, simultaneously drawing on the same source data—and
you can—all will be refreshed.
Changing the Calculation
Next point. Suppose instead of having the pivot report calculate the total sales for each salesperson—
that is the default calculation—you want to determine the average sale in dollars each salesperson has
executed. To do this (among other ways), first click anywhere in the pivot report data label area, (where
the sales numbers appear), then click PivotTable Tools Options Summarize Values By Average
(you’ll see this command on the resulting drop-down menu) (Figure 8–16):
Search JabSto ::

Custom Search