Interactive Chart in Excel

Creating Excel Interactive Chart

Charts visuals are better to tell the story and everyone knows it but interactivity is what required with the chart to tell the story even better. Interactivity is nothing but when the user should be able to see certain values in the excel chart. They should be able to see that result when they click the button. For example, if the chart is showing “Sales & Cost” values and if the user wants to see the “Profit” chart then if they click on the “Profit” option they should see the chart accordingly.


Examples to Create Interactive Chart in Excel

You can download this Interactive Chart Excel Template here – Interactive Chart Excel Template

Example #1

For example, we have monthly sales values, as shown below.

Interactive Chart in Excel Example 1

Instead of seeing all the month’s values in the column chart in excel, we want to see single month values, and if we click on the forward button, we should keep seeing the next month’s values in the chart. Follow the below steps to create an interactive chart.

Step 1: Copy the above data table and paste-like the below image.

Example 1.1.0

Step 2: Below that, create a similar template but without values.

Interactive Chart in Excel Example 1.2.0

Step 3: Under the Excel DEVELOPER tabExcel DEVELOPER TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options more, click on “Insert” and choose “Scroll Bar.”

 Example 1.3

Step 4: Draw this button on the worksheet, as shown below.

Interactive Chart in Excel Example 1.4.0

Step 5: Right-click on the scroll bar in excel and choose “Format Control.”

 Example 1.5.0

Step 6: Now, the “Format Control” window comes up. In this window, choose the “Control” tab.

Interactive Chart in Excel Example 1.6

Step 7: Make the current value is zero, the minimum value is zero, and the maximum value is 12 because we have only 12 months of sales value here.

Next, make the incremental change as 1 because whenever we click on the forward button, it should change by 1, make “Page Change” as zero, and give a cell link as A6. Click on “Ok” to close out that window.

 Example 1.7

Step 8: Now click on the forward button of the scroll bar and see the value in A9 cell.

Interactive Chart in Excel Example 1.8

I have clicked on the “Forward Button” of the Scroll bar three times, and in cell A9, we have a value of 3.

Step 9: Similarly, if you click on the backward button, it will reduce by 1 every time.

Example 1.9

Step 10: Now, in the B5 cell, apply the IF condition, as shown below.

Interactive Chart in Excel Example 1.10

Let me explain the formula for you.

The formula says, “if A6 value (which is increased or decreased by scroll bar moment) is less than or equal to 1, then we need the value from B2 cell (Jan Sales Value), or else we need “#N/A” error value.

Similarly, change the formula for “Feb” month, as shown below.

Example 1.11.0

Since “Feb” is the second month, we need the value from “Feb” month cell (C2 cell) only when the scroll bar cell value is >=2, as of now scroll bar cell value (A6 cell) is 1, so the formula has returned “#N/A” error value.

Step 11:Like this change the numbers for each month.

Interactive Chart in Excel Example 1.12

Because the scroll bar value is 12, we have all the months’ sales value.

Step 12: Now, for this newly created table, insert the “Cluster Column” chart in excel.

 Example 1.13

This will create a chart like this.

Interactive Chart in Excel Example 1.14

Do some font change and color formatting for fonts and column bars.

 Example 1.15

Hide the rows of the actual table (first two rows).

Step 13:Now, click on the scroll bar backward button and see the magic.

Interactive Chart in Excel Example 1.16.0

Because the scroll bar linked, the cell value is 5, our chart is also showing only five months’ values, and the other month’s values are just empty.

Example #2

Now we will see one more interactive chart. Below is the region-wise sales data.

 Example 2

Create a drop-down list in excel of “Regions.”

Interactive Chart in Excel Example 2.1

Below this creates a replica of the table from the above table but deletes numbers.

 Example 2.2.0

Now apply the formula as shown below.

Interactive Chart in Excel Example 2.3

Like this, change the numbers for each region.

Example 2.4

The formula says if the drop-down cell value is equal to the respective region in this table, then we need that region values from the above table.

For this chart, create a line chart and format it as you need.

Interactive Chart in Excel Example 2.5

We get the following line chart.

Example 2.6

Now this chart will show values of the region as per the selection made from the drop-down list.

Interactive Chart in Excel Example 2.7

I have selected the “West” region from the drop-down list, and the chart is showing only that region values across years.

Things to Remember here

Recommended Articles

This has been a guide to an interactive chart in excel. Here we discuss how to create an interactive graph in excel with examples and a downloadable excel template. You can learn more from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion