Follow the steps below to create an interactive chart in Excel. We are going to use a dummy data in our example.
Create data set
On a new sheet (give this sheet the name “Data and Chart interactive sheet”), first create a table and add data in it. Create all the charts you want.
Note: In this case the first chart is Sales and Costs chart so select all the cells of Sales (m$) and Costs (m$).
Repeat the process for all the charts.
Note: We are making 4 charts in this example so we will name them in this order: chart1, chart2, chart3 and chart 4.
In a separate range of cells, list down all the chart names. Give this range a name like 1stChartTypes.
Add a new sheet to your workbook. Give it a name. We will call our interactive output sheet.
Right click on Combo Box and press CTRL+1 keyboard shortcut. Specify input range 1stChartTypes and cell links as a blank cell in your output sheet (in our case interactive sheet output).
Now we are going to pull corresponding chart based on user selection. Enter a named range called selChart.
Click on FORMULAS tab and then click on Define Name. Give the name as selChart and define it as
=CHOOSE (Linked_cell, Chart1, Chart2, Chart3, Chart4)
Now, go back to Data and Chart sheet. Select Chart1 range.Press CTRL+C to copy it. Go to Output sheet and past it as linked picture.
Click on the picture, go to the formula bar and type =selChart and press enter. That’s it, you have now created an interactive sheet.
This is what your final chart will look like: