Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

How To Create Interactive Charts In Excel

    how to create interactive charts in excelA chart is a perfect tool to present data in an understandable way.

    But sometimes it sucks because we overload it with data.

    I believe that when it comes to charting it should be neat & clean.

    The best solution to this problem is using interactive charts in excel.

    By using interactive charts in excel you can present more data in a single chart. And, you don’t even have to worry about all that clutter.

    It’s a smart way.

    Look at the below chart where I am trying to show target vs. achievement, profit and market share.

    how to create interactive charts in excel vs normal chart

    But, do you think this is the decent way to present it?

    Of course not. 

    And, now check out this, where have an interactive chart which I can control with option buttons.

    how to create interactive charts in excel chart

    So, what do you think?

    Are you ready to create your first interactive chart in excel?

    So, let’s get started.

    Steps to create an Interactive Chart

    I have split this process into three parts.

    1. Interactive data table
    2. Using option buttons to control chart
    3. And, finally create a chart

    Creating Interactive data table

    I am using below table to create this chart. You can download this file from here to follow along.

    • First of all, copy this table and paste below the original table.

    how to create interactive charts in excel paste table data

    • Now, delete the data from the second table.

    how to create interactive charts in excel delete data

    • In Jan month cell of target & achievement insert following formulas and copy those formulas into the entire row.

    how to create interactive charts in excel add formula in tagret achievement

    • After that, into the Jan month of profit and copy it into the entire row.

    how to create interactive charts in excel add formula profit

    • In the end, Jan month of market share copy it into the entire row.

    how to create interactive charts in excel add formula in market share

    Now, your interactive data table is ready.

    how to create interactive charts in excel data with formulas

    So far so good.

    Let me tell you how it works.

    All the cells in this table are linked with A1.

    And when you enter “1” in A1 table will show you data for target and achievement only.

    For “2” it will show profit and market share for “3”.

    Using option buttons to control chart

    So next, you have to insert option buttons to automate value in cell A1. Here are the steps.

    • Go to developer tab -> Control -> Insert -> Option button

    how to create an interactive chart in excel insert option buttons

    • Insert three option buttons.
    • Now, name them as following.
      • Button-1 = TGT Vs. ACH
      • Button-2 = Profit
      • Button-3 = Market Share

    how to create interactive charts in excel name option buttons

    • After that, right click on any of the buttons and select “format control”.

    how to create interactive charts in excel select format control

    • In format control options, link it to the cell A1 and click ok.

    how to create interactive charts in excel link cell

    • Now, you can control your data with these option buttons.

    how to create interactive charts in excel data with option buttons

    Creating an Interactive Chart

    Now, it’s time to get down to the real thing.

    • First of all, select your table and insert a column chart.
    • After that, select your data bars & click on “change chart type”.
      • Go to Design Tab -> Design -> Change Chart Type.

    how to create interactive charts in excel click change data type

    • Now, for profit and market share, change the chart type to the line with markers. And, tick secondary axis for both.

    how to create interactive charts in excel change charts type secondary axis

    You can make some formatting changes in your chart if you want.

    Congratulations, your interactive chart is ready to rock.

    how to create interactive charts in excel ready to use

    Sample Words

    download sample file to learn more about how create interactive charts in excel

    Last Words

    In the end, I just want to say that by using interactive charts in excel you can make your user to focus on one thing at a time.

    You can save a lot of space in the dashboards with one interactive chart.

    Sometimes you feel that it’s a lengthy process but it’s a one-time setup which can save your lot of time.

    What’s Next?

    You can also use a dynamic chart title in your chart.



    • Pingback: Guest Post: A Step-by-Step Guide to Creating a Panel Chart in Excel()

    • Shantanu Das

      Puneet… Big Thanks for sharing the Idea of how to create a Interactive Chart in excel. I am a Reporting analyst and I am sure this will help me a lot. Thanks again!

      • Puneet Gogia

        Hey Shantanu, I’m so glad you liked it.

    • GraH

      An small enhancement idea could be to 100% overlap target vs achievement, Puneet.

      • Puneet Gogia

        Yup, absolutely. Even we can hide/show it with check box.

        • GraH

          yup, Yup. 🙂