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.
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.
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
You need to use below table to create this chart. Please download this file from here to follow along.
- First of all, copy this table and paste below the original table.
- Now, delete the data from the second table.
- In Jan month cell of target and achievement, insert following formulas and copy those formulas into the entire row.
- After that, into the Jan month of profit and copy it into the entire row.
- In the end, Jan month of market share copy it into the entire row.
Now, your interactive data table is ready.
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.
- Insert three option buttons and name them as following.
- 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 format control options, link it to the cell A1 and click OK.
- Now, you can control your data with these 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.
- Now, for profit and market share, change the chart type to the line with markers and tick secondary axis for both.
Congratulations! your interactive chart is ready to rock. You canalso make some formatting changes in your chart if you want.
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.
You must Read this Next
- Advanced Excel Charts and Graphs: Even there are a lot of advanced charts and graphs which we can create in Excel, but these are the top...
- Dynamic Chart Title: By creating a dynamic chart title you can make your Excel charts more effective. Just think this way...
- Add a Vertical Line in a Chart in Excel: You can highlight a specific point on a chart with a vertical line and out of all the methods, I’ve found this method (which I have…
- Add a Horizontal Line in a Chart in Excel: Let’s say you have an average value which you want to maintain in your sales throughout the year or a...
- Dynamic Chart Range in Excel: It happens sometimes that you create a chart and at the time when you update it, you have to change its range manually. But…
- Copy Chart Formatting to Another Chart: With paste special option, you can copy formatting from one chart to another just like you do...
- Change the Default Chart Type in Excel: You can change the default chart type. So that when you use a shortcut key to insert a chart you will get…
- Add Secondary Axis in a Chart: To create a combo chart you need to insert a secondary axis and you can use these simple steps….