MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple Charts from One Pivot Table (Excel 2000)


Posted by Jim Darcy on December 13, 2001 6:15 AM

I have my data summarized in a pivot table. One of the data elements has numbers much larger than the other five. I need to make a line chart, but due to the wide variance in data, I need to create two charts from the same pivot table (one for the product with large numbers, and the other for the rest of the data), otherwise the smaller quantity products get compressed and seemingly merged near zero - making the chart useless. I could do this fairly easily with the older version of Excel, but Excel 2000 doesn't seem to allow two independent charts to run off the same pivot table (changes made on one chart override the pivot table and therefore the second chart). Thank you!


Posted by Mark W. on December 13, 2001 10:04 AM

Why not create separate PivotTables and Group your
'Numbers' field by...

1. Right clicking on the 'Numbers' field button...
2. Choosing the Group and Outline | Group... command
from the popup menu
3. Set your interval...
4. Do Steps 1-3 for the 2nd PivotTable and set
a complementary interval.

Posted by Jim Darcy on December 13, 2001 12:13 PM

Thanks for the reply. I've been trying to use groups without success so far, but will keep trying. I was thinking the solution lay in "data series" as that was how I controlled this issue in the older version of Excel...again without success so far.

Posted by Mark W. on December 13, 2001 12:19 PM

The key to successful PivotTable Charting is that
you should always select the "Entire Table" before
creating the chart. That way if the PivotTable
resizes your chart will accommodate the new range.

Take a look at the Excel Help topic, "Group or
ungroup numeric items in a PivotTable or
PivotChart field" (Excel 2000) or "Group and
ungroup numeric items in a PivotTable" (Excel 97).

: Why not create separate PivotTables and Group your : 'Numbers' field by... : 2. Choosing the Group and Outline | Group... command : from the popup menu : 3. Set your interval... : 4. Do Steps 1-3 for the 2nd PivotTable and set : a complementary interval.