Interactive Charts in Excel
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.
Select all the cells, corresponding to the first chart. Click on the FORMULAS tab and select Define Name. Give any name (in our case it’s chart 1).
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.
In the new sheet create a Combo Box by clicking on the DEVELOPER tab and then clicking INSERT and clicking on the Combo Box icon.
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:
Template
Further reading: Define Name Combo box Developer Tab Basic concepts Getting started with Excel Cell References
