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.

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.

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

define name

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 chart 4.

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

give name

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.

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

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

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

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.

This is what your final chart will look like:

profit trend

sales and costs

sales and costs acme

Template

You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell