How to Create Interactive Charts in Excel

Last Updated: June 22, 2023
puneet-gogia-excel-champs

- Written by Puneet

A chart is a perfect tool to present data in an understandable way. But sometimes it sucks because we overload it with data. Yes, you heard it right.

I believe that when it comes to charts it should be neat and clean and the best solution to this problem is using interactive charts. 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 an interactive chart in excel vs normal chart

Do you think this is a 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 an interactive chart in excel chart

What do you think?  Are you ready to create your first interactive chart in Excel?

Steps to Make an Interactive Chart in Excel

In today’s post, I’m going to show you the exact steps which you need to follow to create a simple interactive chart in Excel. Please download this file from here to follow along.

1. Prepare Data

  1. First of all, copy this table and paste it below the original table.
    nt/uploads/2016/12/how-to-create-an-interactive-chart-in-excel-paste-table-data
  2. Now, delete the data from the second table.
    how to create an interactive chart in excel delete data
  3. In the Jan month cell of target and achievement, insert the following formulas and copy those formulas into the entire row (Formula Bar).
    =IF($A$1=1,B3,NA())
    how to create an interactive chart in excel add formula in tagret achievement
  4. After that, into the Jan month of profit and copy it into the entire row.
    how to create an interactive chart in excel add formula profit
  5. In the end, Jan month of market share copy it into the entire row.
    how to create an interactive chart in excel add formula in market share

Your interactive data table is ready.

how to create an interactive chart in excel data with formulas

All the cells in this table are linked with A1. And when you enter “1” in the A1 table will show you data for target and achievement only. For “2” it will show profit and market share for “3”.

2. Insert Option Buttons

  1. Go to developer tab ➜ Control ➜ Insert ➜ Option button.
    how to create an interactive chart in excel insert option buttons
  2. Insert three option buttons and name them as following.
    • Button-1 = TGT Vs. ACH
    • Button-2 = Profit
    • Button-3 = Market Share
    how to create an interactive chart in excel name option buttons
  3. After that, right-click on any of the buttons and select “format control”.
    how to create an interactive chart in excel select format control
  4. In format control options, link it to the cell A1 and click OK.how to create an interactive chart in excel link cell
  5. Now, you can control your data with these option buttons.
    how to create an interactive chart in excel data with option buttons

3. Insert Secondary Axis Chart

  1. First of all, select your table and insert a column chart.
  2. After that, select your data bars & click on “change chart type”. Go to Design Tab ➜ Design ➜ Change Chart Type.
    how to create an interactive chart in excel click change data type
  3. Now for profit and market share, change the chart type to the line with markers and tick the secondary axis for both.
    how to create interactive chart in excel change charts type secondary axis

Congratulations! your interactive chart is ready to rock. You can also make some formatting changes to your chart if you want. 

how to create interactive charts in excel ready to use

Sample File

Download this sample file from here to learn more.

Conclusion

In the end, I just want to say that with an interactive chart you can help your user to focus on one thing at a time.

And you can save a lot of space in the dashboards as well. Sometimes you feel that it’s a lengthy process but it’s a one-time setup that can save you a lot of time. I hope this tip will help you to get better at charting, but now, tell me one thing.

What kind of interactive charts do you use? Share with me in the comment section, I’d love to hear from you, and please don’t forget to share this tip with your friends, I’m sure they will appreciate it.

More Charting Tutorials

36 thoughts on “How to Create Interactive Charts in Excel”

  1. Hey puneet

    How do you change the legend to make it work according to the prompts? Mine still displays with all of them.

    Reply
  2. So glad I stumbled onto this great tutorial. Now I just need to learn how to embed an interactive chart on a webpage. If anyone has any ideas please feel free to share. Thanks in advance!

    Reply
  3. Wow.
    Glad that I came across this, that really helps to make my data presentation much more reader-friendly!!! But I got a question, I can’t really hide the legends that only apply to one chart when I switch to another one, it still shows, any solution to this?

    Reply
  4. Wow, thanks for sharing the idea! I really respect the efforts you took and the way you explain everything.
    Already saved the link to this page for reference in one of my Excel workbooks. 🙂

    Reply
  5. Very good, Wish I understood the breakdown of the formula (specifically the NA tag). I am curious how I might adapt them as I want to make one graph interactive to pull data from different tables, not lines.

    Reply
  6. Hi Puneet,

    It was helpful indeed. We appreciate the way you presented above.
    I just liked it.

    Regards,
    Sushant

    Reply
  7. Hi,

    This is a very good tutorial. However, we can also make interactive charts using slicers and the methodology is much simpler to follow. What could be pros of using this method over slicers?

    Reply
  8. Great Sir,

    I really helpful for your all such kind of formula’s

    Again thanks a lot

    Regards
    Sandeep SIngh

    Reply
  9. Hi Puneet..Love the idea of being able to change the same graph to show other information. I loooove it…..

    Reply
  10. very informative & impressive. I have reviewed it in detail and found it very convenience & practical, will also apply it in my upcoming dashboard presentation

    Reply
  11. 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!

    Reply

Leave a Comment