This is a follow-up to “How to create a dynamic drop-down list in your financial model”, but this article focuses on creating a dynamic drop-down list from a horizontal range or data array. A dynamic drop-down list can equally update and expand seamlessly, whenever additional items are added to a horizontal array of data.

Excel’s Data Validation is one of the more underutilised and understated features in Microsoft’s spreadsheet application.

Quite simply it is a great, VBA macro-free tool, in which to improve the internal controls and corporate governance surrounding the management of a financial model.

A dynamic drop-down list is a great way to force spreadsheet users, to select a specific array of inputs for a cell in a financial model. This dynamic approach combines the Defined Name function, OFFSET and COUNTA functions to provide a drop-down list, which flexes whenever there are additions or deletions to the data array of inputs.

The drop-down list is a common tool to experienced financial modelling professionals. This alternate approach, a ‘radio button’ drop-down list, does not require Excel VBA macros but instead uses the Data Validation tool.

This is a fast approach to add a level of sophistication and reduce input error in your spreadsheet. It is a great technique because it will flex, based on the value chosen in a precedent drop-down list.

Here is an effective and simple technique to incorporate a dynamic approach to a drop-down list of inputs. It offers a more effective approach than creating a dynamic name range.