How to Create Interactive Charts in Excel

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 to it. Create all the charts you want.

Interactive Charts data table

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).

Interactive Charts define name

Interactive Charts new name

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 chart4.

In a separate range of cells, list down all the chart names. Give this range a name like 1stChartTypes.

Interactive Charts give name

Add a new sheet to your workbook. Give it a name. We will call our interactive output sheet.

Combo Box

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.

Interactive Charts combo box

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).

Interactive Charts format control

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

See also  Chart with combo box

=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.

Interactive Charts paste 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.

A chart

This is what your final chart will look like:

Interactive Charts profit trend

Interactive Charts sales and costs

Interactive Charts sales and costs acme

Free Interactive Charts template to download here