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.

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.

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
- First of all, copy this table and paste it below the original table.

- Now, delete the data from the second table.

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

- In the end, insert the formula with 3 into the Jan month cell of market share and copy it into the entire row.

Your interactive data table is ready.

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
- Go to Developer tab ➜ Controls ➜ Insert ➜ Option Button (Form Control).

- Insert three option buttons and change their text as follows.
- Button-1 = TGT Vs. ACH
- Button-2 = Profit
- Button-3 = Market Share
- After that, right-click on any of the buttons and select “Format Control”.

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

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

3. Insert Secondary Axis Chart
- First of all, select your table and insert a column chart.
- After that, select your data bars and click on “Change Chart Type”. Go to Chart Design Tab ➜ Change Chart Type and select Combo.

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

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

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.
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.
More Charting Tutorials
- How to Add a Horizontal Line in a Chart in Excel
- How to Add a Vertical Line in a Chart in Excel
- How to Create a Bullet Chart in Excel
- How to Create a Dynamic Chart Range in Excel
- How to Create a Dynamic Chart Title in Excel
- How to Create a Sales Funnel Chart in Excel
- How to Create a HEAT MAP in Excel
- How to Create a HISTOGRAM in Excel
- How to Create a Pictograph in Excel
- How to Create a Milestone Chart in Excel
- How to Insert a People Graph in Excel
- How to Create PIVOT CHART in Excel
- How to Create a Population Pyramid Chart in Excel
- How to Create a SPEEDOMETER Chart [Gauge] in Excel
- How to Create a Step Chart in Excel
- How to Create a Thermometer Chart in Excel
- How to Create a Tornado Chart in Excel
- How to Create Waffle Chart in Excel
Thank you so much. It was really useful.
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?
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. 🙂
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.
Thanks so much Puneet, keep up with your great work 🙂
I appreciate the effort you put in this interactive chart because it is easy to understand and apply.
Thanks
Hi Puneet,
It was helpful indeed. We appreciate the way you presented above.
I just liked it.
Regards,
Sushant
Hi
Nice post.
I want to know how to use quote for this VISIT 22 DAY 1 in the if statement
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?
Great Sir,
I really helpful for your all such kind of formula’s
Again thanks a lot
Regards
Sandeep SIngh
I have a suggestion: Show us a graph using drill down.
Great, thanks
It’s really good stuff and I would love to use it, thank you Puneet Sir for posting
You’re Welcome 🙂
Hi Puneet..Love the idea of being able to change the same graph to show other information. I loooove it…..
I’m glad to know you like it 🙂
Big Thanks for sharing.This will help me a lot
That’s great, Latha.
very innovative chart.. really usefull
I’m so glad you liked it. 🙂
Puneet… This is really impressive.. a simple example for interactive dashboard!
I’m so glad you liked it. 🙂
Nice didactics as well… Love it how you split this topic into 3 learning steps.
I’m so glad you liked it.
very informative & impressive. I have reviewed it in detail and found it very convenience & practical, will also apply it in my upcoming dashboard presentation
All the best, Noman.
An small enhancement idea could be to 100% overlap target vs achievement, Puneet.
Yup, absolutely. Even we can hide/show it with check box.
yup, Yup. 🙂
That won’t work if achievement exceeds target – you wouldn’t see the target value.
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!
Hey Shantanu, I’m so glad you liked it.
Awesome! Thanks so much!