Interactive Chart is not difficult to make – Part 3/3

Using Option Button to display different items on chart

So far, we have made an interactive chart with check box to highlight items with negative growth; with scroll bar to see more items in a confined space.  In this post, we will go one step further: Adding Option Button to display either “Values” or “Units”:

Excel Tips - Interactive Chart with Option Button

You may download a Sample File to follow through.

The data for “Unit Sold” resides on a separate worksheet <Data in Units>.  Luckily enough it follows exactly the same structure of  the “Values table”.

This makes the enhancement an easy job, if you know how to use CHOOSE function.

The syntax of CHOOSE

CHOOSE(Index_num, [value1],[value2]…)

Index_num simply tells CHOOSE to return which value argument.  For example, Index_num 1 means to return value1; 2 means value2 and so on and so forth. Index_num needs to be from 1 to 254.  In order words, we may input up to 254 value arguments in CHOOSE.

CHOOSE is the perfect function to accompany Option Buttons that assign sequential number to a specific cell.

You will see how in the following steps.

Inserting two Option Buttons and adding control

  1. Go to Developer Tab –> Insert –> Option Button (Form Controls)
  2. Repeat step 1 to insert one more Option Button or alternately copy the first Option Button and Paste a new one next to it
  3. Rename the Option Button 1 and 2 to Sales and Units respectively
  4. Assigned linked cell to P5 (Tip: Test the buttons to ensure the number displayed on the linked cell is in the correct order you intended to)
  5. Move the two Option Buttons on top of the chart

Excel Tips - Interactive Chart with Option Button 2.PNG

Editing the interactive chart title

Do you remember we made an interactive chart title before?  We can now modify it to fit the new feature just added:

="Top "&$F$3&" to "&$F$12&" selling items in FY17" & 
  CHOOSE($P$5," ($)"," (Units)")

What the formula does is to concatenate the blue portion to the original formula, i.e. appending either ” ($)” or ” (Units)” to the chart title depending on which Option Button is selected.  Do you see the simplicity of CHOOSE?  When the first option button is selected, it shows “$”; when the second one is selected, it shows “Units”.  Isn’t it nice?

Setting up the (intermediate) helper table

Well, we have done it before.  What we need to do is to edit the formulas as shown below:

Excel Tips - Interactive Chart with Option Button 3.PNG

Believe it or not.  Technically only three formulas (in G3 to I3) are required to modify and then copy down.  That’s all what we need to make the Option Buttons perfectly incorporate into the Interactive Chart.

Original formulas:

In G3, =INDEX(A$3:A$52,$F3)
In H3, =INDEX(B$3:B$52,$F3)
In I3, =INDEX(C$3:C$52,$F3)

Modified formulas:

G3 =CHOOSE($P$5,INDEX(A$3:A$52,$F3),INDEX('Data in Units'!A$3:A$52,$F3))
H3 =CHOOSE($P$5,INDEX(B$3:B$52,$F3), INDEX('Data in Units'!B$3:B$52,$F3))
I3 =CHOOSE($P$5,INDEX(C$3:C$52,$F3),INDEX('Data in Units'!C$3:C$52,$F3))

Modified formulas explained:

=CHOOSE($P$5,
  INDEX(A$3:A$52,$F3),
  INDEX('Data in Units'!A$3:A$52,$F3))

Where $P$5 is the value determined by the Option Buttons,
[Value1] is the original formula that retrieves data from the "Value Table"
[Value2] is the formula that retrieves data from the "Unit Table"

With CHOOSE, we can use Option Buttons to determine which value (or formula) to be returned to the helper table, that will be fed into the table for charting eventually.  In our modified formula above, when Option Button 1 (assigned to Values) is selected, the formula look into the dataset with value data; when Option Button 2 (assigned to Units) is selected, the formula look into the dataset with unit data.  You can imagine that up to 254 different options could be added to your chart (although in real world, you would never have more than 5 options in my opinion.  If you have too many options, a combo box or a drop down menu maybe a better alternative).

Hey, once you modified the formulas from G3:I3, don’t forget to copy them down.

Everything is ready.  Let’s hide the data and helper rows.

Oh… What happened to my beautiful chart?

Excel Tips - Interactive Chart with Option Button 4

The final step

We need to check a very hidden check box…Excel Tips - Interactive Chart with Option Button 5

Where is it?

Select the chart –> Chart Tools –> Design Tab –> Select Data –> Hidden and Empty Cells

Excel Tips - Interactive Chart with Option Button 6

There you see the Hidden and Empty Cells settings.

It is all done.  Interactive Chart is not difficult to make, is it?

Side Notes – The thinking process

Reverse Thinking

Although I showed you step by step approach, I started by drafting the final output that already had all the three format controls in mind.  From there, I worked backward to study the data that I have; to determine how to get the data for the chart, how to interact the charting data with different form controls; what formulas required.  Another key consideration is whether I will need to enhance the work in the future?  It could be quite different to build something for one-time use and something for long-time use with potential “upgrade” required.

Make use of helper cells is not a shame at all

As you see, the (intermediate) helper tables make the whole process super easy.  Without the helper tables, you will find unnecessary complexity when you need to add a new control to your chart.

Put it into action

Once we see the door, we have to walk through it in order to discover what’s behind.  Don’t be afraid of failure.  Trial and error is the most valuable learning process.  Isn’t it?

Test the output

Honestly I made a lot of modification in the process.  You won’t realize some minor but obvious mistakes until you test it, again and again.  Ideally show it to a third person for fresh idea.  You are more than welcome to provide comments and suggestions to make it a better one.  🙂

I hope you’d enjoyed this short series of making Interactive Chart.

Advertisements
This entry was posted in Chart, Excel Tips and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s