How to Create Interactive Charts In Excel

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

Yes, 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 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 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 exact steps whcih you need to follow to create a simple interactive chart in Excel.

1. Prepare Data for Interactive Chart

  • First of all, copy this table and paste below the original table.
how to create an interactive chart in excel paste table data
  • Now, delete the data from the second table.
how to create an interactive chart in excel delete data
  • In Jan month cell of target and achievement, insert following formulas and copy those formulas into the entire row.

=IF($A$1=1,B3,NA())

how to create an interactive chart 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 an interactive chart in excel add formula profit
  • 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

Now, your interactive data table is ready.

how to create an interactive chart 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”.

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

2. Insert Option Buttons to Control Chart

So next, you have to insert option buttons to automate value in cell A1.

how to create an interactive chart in excel insert option buttons
  • 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
  • After that, right click on any of the buttons and select “format control”.
how to create an interactive chart in excel select format control
  • In format control options, link it to the cell A1 and click OK.
how to create an interactive chart in excel link cell
  • 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 to Create 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 an interactive chart in excel click change data type
how to create interactive chart in excel change charts type secondary axis

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

how to create interactive charts in excel ready to use

Sample File

Conclusion

In the end, I just want to say that with a 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 which can save your 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.

Content Protection by DMCA.com
2018-11-16T06:13:21+00:00

15 Comments

  1. latha 20 Aug, 18 at 9:54 am - Reply

    Big Thanks for sharing.This will help me a lot

  2. Uma Reddy 23 Dec, 17 at 4:38 pm - Reply

    very innovative chart.. really usefull

    • Puneet 3 Aug, 18 at 6:26 am - Reply

      I’m so glad you liked it. 🙂

  3. Sailaja Perumalla 13 Aug, 17 at 10:58 am - Reply

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

    • Puneet Gogia 13 Aug, 17 at 3:36 pm - Reply

      I’m so glad you liked it. 🙂

  4. GraH 21 May, 17 at 6:46 am - Reply

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

    • Puneet Gogia 1 Jun, 17 at 4:54 am - Reply

      I’m so glad you liked it.

  5. Noman 18 May, 17 at 9:38 am - Reply

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

    • Puneet Gogia 18 May, 17 at 3:36 pm - Reply

      All the best, Noman.

  6. GraH 9 Apr, 17 at 11:48 am - Reply

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

    • Puneet Gogia 9 Apr, 17 at 11:55 am - Reply

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

      • GraH 9 Apr, 17 at 11:58 am - Reply

        yup, Yup. 🙂

  7. Shantanu Das 22 Feb, 17 at 7:59 am - Reply

    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 22 Feb, 17 at 10:56 am - Reply

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

Leave A Comment