Graphing Two Pivot Tables on One Graph?

[aseykora]

I have created multiple pivot tables (one that counts number of projects open and one that counts number of projects closed) I would like to graph these two data series on the same graph. How can I accomplish this? I’m operating on Windows XP edition using Excel 2003.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

 

[Jon Peltier]

You can plot pivot table data flexibly in a regular chart. Start by selecting a blank cell that's not near either pivot table. Start the chart wizard, and in step 2 (Source Data), click on the Series tab. Add each series and select its data one by one.

 

[jenna_may17]

I also have this problem but with excel 2007. I have a workbook which has two data sheets-one for raw data for each month and one with the data with duplicate records removed. (When exporting, our database creates a separate line for each type identified within a single record.) I need a stacked column graph to show the proportions of each type, but I need a trendline on the same graph showing total records. If I use a normal graph to include the series from both pivottables manually it won't expand when new months are added to the pivottable. If I use a pivotchart it will update when new data is added however I can't seem to be able to include data from two different pivottables on one chart. Any help would be appreciated!

E.G. Raw Data
1-January-Socks-Black
1-January-Socks-Blue
2-January-Socks-Green
3-February-Socks-White
3-February-Socks-Blue
4-February-Socks-Green

Sheet 1 would show all records with individual lines for type as above. Sheet 2 however removes duplicates and would only show total records, e.g. below:
1-January-Socks-Black
2-January-Socks-Green
3-February-Socks-White
4-February-Socks-Green

So my series on the column graph would show the percentage of socks of each colour by month (from sheet 1), but my trendline needs to reference sheet 2 and display that there were two records in January (1 & 2) and two in February (3 & 4).

I'd appreciate any ideas on this! Thanks

 

[Jon Peltier]

If I use a pivotchart it will update when new data is added however I can't seem to be able to include data from two different pivottables on one chart.

That's the problem. Another problem is that any pivot chart with extensive formatting will lose the formatting when the pivot table updates.

My approach to this would be to have a VBA procedure to rebuild the chart on demand.

 

[jenna_may17]

Thank Jon, I was thinking I'd have to go that way! In the end I created a sub for my macro to add another column to the raw data sheet to indicate unique entries using if & countifs (Unique Count in Excel Pivot Table With PowerPivot). I then updated the pivottable to include values for both count of types and sum of the "unique" column for total records. I swapped the rows & columns so months now go across the page instead of down, removed the "grand total" and updated my chart references to go all the way across to where the 12 months will be, so as they're added they're already included. The pivottable looks a little messy by displaying two data sets, and the graph displays future months with no data, but this is all fine for my application as the pivotcharts are only used as source data for the graphs and the graphs only go internally so it's not a big issue having the blank columns. Thought I'd post my solution incase it helped anyone else-I know the forums have been a goldmine for me!