30 Advanced Excel Tips for 2026

Excel Champs / Excel Charts / Interactive Charts
By Puneet Gogia (Microsoft MVP)

How to Create Interactive Charts in Excel

Turn on the Developer tab (File → Options → Customize Ribbon, tick Developer), then go to Developer → Insert → Form Controls → Option Button and draw one button for each view you want to switch between, such as Target vs Achievement, Profit and Market Share.

Right-click any button, choose Format Control and set Cell link to an empty cell such as $A$1. All the option buttons on the sheet share that link, so A1 shows 1, 2 or 3 depending on which button is selected.

Copy your data table below the original, clear the numbers and fill the copy with formulas that return a value only when its button is picked: =IF($A$1=1,B3,NA()) for the Target and Achievement rows, =IF($A$1=2,B5,NA()) for Profit and =IF($A$1=3,B6,NA()) for Market Share, adjusting the row references to your layout. Fill each one across the months.

Build the chart from the copy, not the original: insert a column chart, then go to Chart Design → Change Chart Type → Combo (Design in older versions) and set Profit and Market Share to Line with Markers with Secondary Axis ticked. Each click on a button now redraws the chart with only that set of data.

Use NA() rather than "" for the hidden values: a text blank plots as zero and drags a line along the bottom of the chart, while #N/A is left out. The legend still lists every series, because #N/A hides the points but not the series, so delete the legend and let the button labels say what is on show.

If you would rather skip form controls, a drop-down in A1 from Data → Data Validation → List works the same way; test for the text instead of a number, as in =IF($A$1="Profit",B5,NA()).

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, they 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 one, where I 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 formula and copy it 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, insert the same formula with 2 instead of 1 into the Jan month cell of profit and copy it into the entire row.
    how to create an interactive chart in excel add formula profit
  5. In the end, insert the formula with 3 into the Jan month cell of market share and 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. When you enter “1” in A1, the table will show you data for target and achievement only. For “2”, it will show profit, and for “3”, market share.

Here are all three formulas for the Jan cells. B3 is the first target value in my original table, and B5 and B6 are the first profit and market share values; change them to match your table:

=IF($A$1=1,B3,NA())
=IF($A$1=2,B5,NA())
=IF($A$1=3,B6,NA())

The formulas return #N/A for the data you don’t want to see, and not 0 or a blank, because Excel doesn’t plot #N/A at all. With 0, the chart would still show flat lines and empty markers at the bottom.

2. Insert Option Buttons

  1. Go to Developer tab ➜ Controls ➜ Insert ➜ Option Button (Form Control).
    how to create an interactive chart in excel insert option buttons
  2. Insert three option buttons and change their text as follows.
    • 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 the Format Control options (Control tab), link it to cell A1 and click OK. As all three buttons are in one group, they share the same cell: the first button puts 1 in A1, the second 2, and the third 3.
    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 and click on “Change Chart Type”. Go to Chart Design Tab ➜ Change Chart Type and select Combo.
    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

Use a Drop-Down List Instead of Option Buttons

If you don’t want to use the Developer tab, you can control the same chart with a drop-down list. Create a drop-down list in a cell (G1 here) with Data ➜ Data Validation ➜ List and the three names as the source, and then enter the following formula in A1. It turns the selected name into 1, 2 or 3, so the rest of the table works just like before:

=MATCH(G1,{"TGT Vs. ACH","Profit","Market Share"},0)

Other Ways to Make a Chart Interactive

  • Slicers: create a pivot chart and insert a slicer (PivotChart Analyze ➜ Insert Slicer) to filter the chart with buttons.
  • Check boxes: link a check box to a cell for each series and use the same IF and NA() formula, so you can turn each series on and off on its own.
  • Chart filters: click the chart and use the Chart Filters button (the funnel next to the chart) to show or hide series and categories without any formulas.

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

Frequently Asked Questions

Why does my interactive chart show zeros instead of hiding the data?

The formulas return 0 or a blank instead of #N/A. Use NA() as the last argument of IF, so Excel skips that data in the chart.

Can I hide the #N/A values in the table?

Yes. Use a conditional formatting rule with the ISNA function (Format only cells that contain ➜ Errors also works) and a white font color, or keep the helper table on a hidden part of the sheet. The chart still reads the #N/A values.

Do option buttons work in Excel on Mac and the web?

Form control option buttons work in Excel for Mac. In Excel for the web, you can’t insert them, so use the drop-down list method there.

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

  1. 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
  2. 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
  3. 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
  4. Thanks so much Puneet, keep up with your great work 🙂

    Reply
  5. I appreciate the effort you put in this interactive chart because it is easy to understand and apply.
    Thanks

    Reply
  6. Hi Puneet,

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

    Regards,
    Sushant

    Reply
    • Hi
      Nice post.

      I want to know how to use quote for this VISIT 22 DAY 1 in the if statement

      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. I have a suggestion: Show us a graph using drill down.

    Reply
  10. It’s really good stuff and I would love to use it, thank you Puneet Sir for posting

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

    Reply
  12. Puneet… This is really impressive.. a simple example for interactive dashboard!

    Reply
  13. Nice didactics as well… Love it how you split this topic into 3 learning steps.

    Reply
  14. 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
  15. An small enhancement idea could be to 100% overlap target vs achievement, Puneet.

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

      Reply
    • That won’t work if achievement exceeds target – you wouldn’t see the target value.

      Reply
  16. 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