Creating Dynamic Chart Using Selectable Drop Down Lists

**montego** · 08-04-2010, 11:56 AM

Hello,

I would like to create a chart similar to what excel creates for you when you use the pivot chart wizard. Specifically I would like the drop down menus with clickable boxes to select a piece of data and plot it on a graph.

I am familiar with creating drop down lists using data validation, but I am stumped on how I create a selectable drop down list.

Basically I wish I could just add data to the pivot table chart as I want to graph data against pivot table data on the same chart, but I don't think that is possible.

Thanks in advance!

**meyero90** · 08-04-2010, 12:40 PM

Just start by create a pivot chart directly instead of pivot table. In excel 07, Insert>PivotTable>PivotChart. From there a "Create PivotTable with PivotChart" will pop up and then Select your Data range in "Select a table or range" and "Choose Where you want the PivotTable and PivotChart to be Place" (i.e.Sheet1 + Range A5)...You'll see as a PivotChart will be created along with the data on the side, then it's your choice to filter down the data the way you want. .

Take a look at my spreadsheet, it's just an example and see in "All Funds" tab, how the data is laid out starting at range A104.

**montego** · 08-04-2010, 01:40 PM

All right, I'm having some difficulty still so bear with me. I think what I really want is to create one large pivot chart using multiple pieces of data, however so far I have not been able to create a single pivot table from multiple data sets formatted even remotely near what I need. I can, however,create two seperate pivot table charts In the format I would like. It also does not help that my computer takes about 5 minutes to calculated based on how slow it is, and the size of the sheet (~150 MB).

Ill keep trying to create a single pivot table, but is there a way to overlay different pivot charts?

**montego** · 08-04-2010, 06:07 PM

I figured it out, Although Now I need to find a way to edit the Y-axis as some data is between 0-100 and the data Im comparing it to is around 5000000

**meyero90** · 08-05-2010, 08:26 AM

You can't really edit the PivotChat axis, but only format the axis. Click anywhere on the y or x axis, and right click on it to select "format axis". Also, you can think of formatting the PivotTable (where the data is displayed, for my file they were on row 115, click on the data you want to format (Dates, Average Gross, Net, in my example) and right click on it and select "Value Field Settings" and then at the bottom of the window click on the "Number Format".

This what I sent you helped