This post is intended to give you an introduction of CHOOSE function and Option Button (Form Control).

Here’s the situation:

We want to display the % of total in a 2-D table.  Nevertheless the % can be of column total or of row total.  We do not want to have two separate columns for displaying %.  Instead, we want to leave the control to user, like the demonstration below:

Don’t you think it is difficult to create?  It is quite simple indeed.

1) Set a cell as variable.  In my example, I used A1.

2) Input the dynamic formula using CHOOSE

In C4:

=CHOOSE($A$1,"Col%","Row%")

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.

Refer to our example:

=CHOOSE($A$1,"Col%","Row%")

• when A1 = 1, it returns “Col%”
• when A1 = 2, it returns “Row%”
• note: If a1 = 3 or above, it returns “#VALUE!” as there is only 2 arguments in the formula

Then input the following formula in C5:

=CHOOSE($A$1,B5/B$8,B5/$H5)
where B5/B$8 is the formula for Col%;
B5/$H5 is the formula for Row%
Note the use of $

In a word, when A1 is 1, give me Col%; when A1 is 2, give me Row%.

If you can follow the above, you should be able to complete the table by “Copy and Paste” the formulas to corresponding ranges on other columns.

Insert the Option Button to make it interactive

As we don’t want user to input 1 or 2 in A1, which is not user friendly at all. Option Button would do the job elegantly.

Go to *Developer Tab –> Insert –> Option Button under From Controls

After you click on “Option Button (From Control)”, your pointer will become a cross.  Move it to a position where you want to place the button on and then click.

Repeat the step to have two Option Buttons added onto the spreadsheet. Right-click on any one of them, select “Format Control…”

Input $A$1 in “Cell link:” –> OK

Tip: You may use mouse to point to A1 instead

Now you should see the value in A1 (the linked cell) changes based on your selection.

To edit the Text of the Option Button, right-click it and Edit Text.  i.e. to replace “Option Button 1” to whatever meaningful to you and your audiences.

Tip: To make it look better, hide row 1, or use your own way to make A1 invisible to user.

With a little bit re-positioning and editing, you should be able to get a similar result like the one below:

Not too difficult, right?

You may download the Sample File to follow through.

The CHOOSE function is quite straightforward and easy to use. Nevertheless, it is not commonly seen in workplace probably because many users do not find a practical application of it yet.  Please share with us your ideas of using CHOOSE by leaving your comments.

*p.s. In case you don’t have Developer Tab on your Ribbon,

1. Go to File tab –> Options –> Customize Ribbon
2. Check the “Developer”under Main Tabs