Display % of column and % of row in a dynamic way

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:Show percentage Row or Column.gif

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:


Display Percentage Row or Column 1

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:

  • 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:

where B5/B$8 is the formula for Col%;
B5/$H5 is the formula for Row%
Note the use of $

Display Percentage Row or Column 2

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 ControlsDisplay Percentage Row or Column 3

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…”Display Percentage Row or Column 4

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

Display Percentage Row or Column 5

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.

Show percentage Row or Column 6

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:Show percentage Row or Column

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 TabsPost 14.0 - Developer Tab
This entry was posted in Excel Tips, Formula 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