Microsoft Excel provides very easy and effective tools to make dynamic dashboards, summaries and professional looking financial models in the shape of controls. There are two types of controls, one and form controls and the other are ActiveX controls. Normally, you use ActiveX controls along with VBA. It is possible to use ActiveX controls without VBA. Today you will see how you can use form controls in your model irrespective of the type of model i.e dynamic summary, dynamic dashboard or dynamic financial models.
- In order to use form controls, Developer Tab in the ribbon should be visible. If it is not visible then click File Tab next click Options afterwards click Customize Ribbon and finally select the check box against Developer on the right hand side in excel 2010/2013. In case of excel 2007 Click the Microsoft Office Button , and then click Excel Options, click Popular, and finally select the Show Developer tab in the Ribbon check box.
- Click Developer Tab, go to Controls group and click on Insert, the below dialogue box will appear
- Click on combo box, your cursor will change to plus sign, draw the combo box at the desired place on the sheet.
- Right click on the combo box and select format control and move to control. In the input range select the range that you want to show in the combo box. Against cell link enter the cell that will be linked with the values in the combo box list.
- Finally use the offset function to show the values against the graph data. You can also use If Function, Lookup Functions, Index Function or Choose Function in place of Offset Function.