Microsoft Office Tutorials and References
In Depth Information
Using data from multiple sheets
Figure 9-23: This workbook contains data in three worksheets.
4. In Step 2b, you specify the ranges. Select range A1:D13 in the first
worksheet and click Add. Repeat this step for the other sheets. The All ranges
box should display three ranges.
5. In Step 3, specify New worksheet and click Finish. In the pivot table, notice
that the Page field displays generic item names (Item1, Item2, and Item3).
6. Drag the Page field button to the left of the pivot chart. Doing so converts
the Page field to a Row field. You can now change the item’s names to
more meaningful text: 2000, 2001, and 2002.
7. The grand total column is not meaningful, so delete it. Right-click, choose
Table Options, and remove the checkmark from the Grand Total for Rows
Figure 9-24 shows the pivot table and pivot chart after making these changes.
Now it’s time to fix the pivot chart. Excel creates a stacked-column chart, which is
not at all appropriate. Start by selecting only one year to display — year 2000. Then:
1. Select the chart and convert it to a clustered column chart.
2. Access the Format Data Series dialog box for the Ratio series and assign it
to the secondary axis.
3. Select the Ratio series and change the chart type to a line chart.
Now the pivot chart is looking good (see Figure 9-25).
But wait: Change the Year field so that the chart displays a different year’s data,
and you’ll discover that the Ratio series reverts back to a column series, and the
secondary axis is gone! You’ve just experienced a serious problem with pivot
charts. Changes made to a data series are not maintained when the chart layout is
changed or when the pivot table is refreshed.