Overlaying Pivot Table Charts

**montego** · 08-05-2010, 12:33 PM

Hello All,

I have two Identical pivot table Charts inserted as objects in a sheet in my workbook. I would like to overlay the two Charts So that you could select the data to display individually on each pivot chart, and see them both displayed on the same graph with the ranges being somewhat equalized.

This is because some data ranges between smaller data sets is ~ 0-30, and the data I want to compare it to can be rather large, ~30000-50000. However, relative range of the data sets are similar. Basically I am looking for a quick way to graph two pieces of data in order to observe trends.

I wish I could post an example with my data, but File size is about 10x larger then max allowed size of 1,000 kb.

**meyero90** · 08-05-2010, 01:05 PM

can't you just create a new pivotcharts incorporates all the data you need and then filter it in order to display on the charts what you want to?

**montego** · 08-05-2010, 04:02 PM

Yes, I can. However, when I try to filter the chart to compare one data series whose values are rather small to a data series whose values are rather large, the smaller data series appears as a practically straight line near zero.

I would like to basically have it autofit the charts Y axis for each set of data and then overlay the two ( or more) graphs

**meyero90** · 08-05-2010, 04:06 PM

in this case right click on the line that appears to be a "straight line" and "Format Series">"Series Options"> AND SET AS 'SECONDARY AXIS". That should do it

**montego** · 08-05-2010, 04:48 PM

O man! That's exactly what I was looking for!

Now, Is there anyway to automate it, or do more than 2 Y axis?

**meyero90** · 08-05-2010, 04:55 PM

No problemo

what do you mean by "automate", once you set it up this way it should remain as is, unless you change it in the future. What do you mean by 2 y-axis, you just got 2 Y-axis, by selecting "secondary axis" no?

**meyero90** · 08-05-2010, 05:08 PM

If I helped, Don't forget to add to my reputation (click on the little scale)
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)

Cheers

**montego** · 08-05-2010, 05:52 PM

It wont allow me to add any more rep to you because I think I rep'd you in the other post.

Anyways, by automate, I would Like to do it so that for any data you select it will automatically scale the Y axises (sp?).

Charting From a pivot table allows you to select between 1 and all data series (although I have my charts limited to 10 data series) and I am wondering If I could add up to 10 Y-axises for the 10 different series.

Anyways, that stuff would all be icing on the cake, Cause I am pretty happy with how I have it set up now.

Thanks again! marking it solved, But if you think any of the other stuff I suggested is possible, feel free to respond, or maybe Ill start a new thread and link this/edit it.

**meyero90** · 08-05-2010, 09:25 PM

I think the problem you have for the limited series you can plot stems from your "source data"..click on the pivottable (on any number for instance) and if you're running on xls '07, click on the tab that just got highlighted in red...OK...from there click on "source data" and just click and place your cursor anywhere where the range is set...you'll see if your source incorporates all the data you want to plot.
I never plotted personally more than 4 series..never tried above that...if the problem is data source...then add the new data to your filter and see if they appear on the chart.

Is there a way u can compress your file and send it over...it will make my life easier

Otherwise, you can still create new Solved post, will this last answer and add to my rep thx