The drop-down list is a common tool to experienced financial modelling professionals. On the other hand, an ActiveX or Form Control Option or radio button are less utilised or recognised, and arguably less user-friendly to employ across a large array of cell – unlike a drop-drown list.

This alternate approach, a ‘radio button’ drop-down list, does not require Excel VBA macros but instead uses the Data Validation tool. Refer to the attached Excel file, which outlines the steps to create a ‘radio button’ drop-down list; as per this post and video demonstration.

‘Radio button’ drop-down list example

[youtube]http://www.youtube.com/watch?v=6Kktws8YRJk[/youtube]

 

Practical business reasons for ‘radio button’ drop-down list vs radio button

Ideal for a company that has users operating either MS Excel or Excel for Mac, given the problems ActiveX macros or forms (like an Option button) experience on a Mac and/or if radio buttons (i.e. Form Control Option button) need to be inserted across a large array of data.

The adoption of a radio button drop-down list will greatly reduce the file size of the financial spreadsheet. This approach can offer greater customisation for the user, unlike the standard Option button in Excel. Finally, model users can effortlessly hide cells containing these drop-down lists, whereas Option buttons will often fail to anchor to their specific cell and jump to adjacent cells.

 

Steps to creating the ‘radio button’ drop-down list

First, you need to create two lookup tables that are located on the Lookup_Table worksheet. The first has two cells: the first cell is blank and the second cell contains the radio button. This table’s defined name is “luYes” – as in Look Up Yes.

The second table contains just one cell – this is blank. It’s defined name is “luNo” – as in Look Up No.

Radio button drop-down spreadsheet - GIF

 

A Two Radio Button Drop-Down List

Click the Cash_Payments worksheet and go to cell J5.

Select Data Validation and on the Settings tab, and choose List for the Allow parameter. In the source window type the following formula:

=IF(ISBLANK(K5),luYes,luNo)

Thus if the adjacent K5 cell (other bank cell for “XYZ Bank Inc”) is blank, the user can access the luYes lookup list containing the radio button. Alternatively K5 contains the radio button, and therefore the user can only choose luNo lookup list with the blank cell for the adjacent J5.

 

Repeat the above steps in Data Validation, but choose cell K5 cell, and type the following formula in the Source window:

=IF(ISBLANK(J5),luYes,luNo)

It is merely the opposite cell referencing of the previous formula reference in J5. If J5 is blank then the user has the luYes lookup list available, otherwise the user has to use the luNo lookup list if J5 is not blank.

 

A Multiple Radio Button Drop-Down List

Remember if you’re creating a multiple radio, it is necessary to account for each other cell in that corresponding group; otherwise the radio button will not work properly.

Select cell M5 (“Operating Account”), click Data Validation -> Settings tab -> specify “List” under the Allow area. This time in the Source window type this formula:

=IF(AND(ISBLANK(N5),ISBLANK(O5)),luYes,luNo)

This formula will permit the user to select the luYes lookup list, if both N5 (Capital Account) and O5 (Sundry Account) are blank; otherwise luNo lookup list will be available, because it assumes one of these two other cells is not blank.

 

Repeat the above Data Validation procedure steps in cell N5, but type this formula under the source window:

=IF(AND(ISBLANK(M5),ISBLANK(O5)),luYes,luNo)

Finally in cell O5, carry out the Data Validation steps, however type in the source window this formula:

=IF(AND(ISBLANK(M5),ISBLANK(N5)),luYes,luNo)

 

Key things to remember with Radio Button Drop-Down List
  • Failure to apply relative (i.e. =Sheet1!B1) rather than absolute (i.e. =Sheet1!$B$1) cell referencing, especially if copying the Data Validation properties down the page, will result in incorrect cell referencing
  • Data Validation cells can be erroneously deleted or copied over, and
  • The formula entered in the source window must be 100% accurate and correct – otherwise the radio button drop-down lists won’t be correct