Toggle Between Charts

The following is a very simple method to change the chart type without the need for VBA.  It uses a simple trick using named ranges and a single picture to enable you to choose many different charts from a single drop down.  The following is an example of the final output where you choose the chart type to appear on the page.

First , we create 3 charts Revenue, Revenue and Expenses and EBIT. The three charts are three normal Excel Charts but for illustrating the method , they should be different chart types.

I have created a line chart, a column chart and a Column and Line Chart.  The following is an example of placing the named range around the chart.

Observe how the name manager in the top right circled in Red is highlighted and all of the cells surrounding the chart are highlighted.  

The named range is from F47:M61.  This is the area encompassing the Chart and its name is EBIT.   Create a further 2 named ranges called RevExp and Rev.  Ensure these named ranges are around the chart also.

Now type the named ranges in a place in the file.  For demonstration purposes I put the Names in a sheet in the Data tab from O2:O4

These will be required for the combo box that is to be used to choose which chart to display.

On the sheet you want the charts to appear, create a combo box.  In Excel 2010 on the Ribbon click on the developer - Choose the Toolbox.

The Combobox option is highlighted in red.  Choose a place in the document to place the combo box and right click on the combobox.

The following dialog will appear

Notice above I have referred to the Input Range as Data O2:O4.  The cell link I usually hide in the cell behind the combo box.

One further named range needs to be created using the following formula.

=CHOOSE(Output!$E$8, Rev, RevExp, EBIT)

Output E8 is the reference cell for the drop down you can see this in the Format Control above.  Call the named range MyChart.

Now it is just about all set up.  One final thing remains, put a picture on the sheet with your combo box.  This can be any picture. 

Click on the picture and type=MyChart in the formula box. After you hit enter the chart will change from the dashing picture above to one of the charts you just made. I have attached a file that shows a working Excel example of the toggle chart process which you might want to adapt.