This page describes how to use spinner boxes, drop-down (combo boxes) and other control forms in excel. These spinner box, combo box, check box and other user forms can be very effective in your corporate and project finance models when presenting contract structuring issues, presenting scenario analysis, making flexible graphs and many other things. It is essential to include the sheet name in the cell link of the user forms so you can use the forms on different sheets. With the control forms you can create flexible summary sheets and diagrams. As with using short-cut keys, participants in classes that I teach sometimes complain that they will not remember how to use the forms — spinner boxes, drop-down boxes (called combo boxes), check boxes and list boxes.
The video and file below are intended to be a reminder for how to used the developer tab and these forms. User forms or controls allow you to control inputs from different pages. This means that if you have an input in the assumptions page, say for the gearing percent of a project, this input can be controlled from multiple places including a summary sheet and a graph. These form controls can be very helpful in summarising data and making presentations with excel or just showing off to your boss. I use these forms extensively in other videos and files but I do not know if there is a place where I explain them slowly. The video below does this. It begins with explaining how to find the developer tab and then use the WINDSCREEN method to allow you to move the files (thanks to Tatiyana in Russia). My son tells me the developer tab is like a secret handshake in excel — when somebody tells you they are really good at excel and there is no developer tab on their computer, you say yes you are really good. But you don’t believe them.
The spinner boxes require that you put the developer tab in your excel menu. You just have to go to the excel options and then to the customize ribbon. Then you get a screen with check boxes. Just click make sure he developer tab is clicked.
Sun Edison Case Study.xlsm
Video for Simple Macro with Goal Seek
As with the short-cut keys, participants in my classes sometimes say they will not remember how to make a macro. This little section is intended to be a reminder. You can do really fancy things with macros and functions that I hope are explained at various points in this website. This includes reading data from the internet, creating fancy scenarios, fixing excel to interpolate data, and resolving circular references. If you have not made a macro, however, do not be intimidated. The first macro I ever made sometime in the early 1990’s was to fix the goal seek in excel so you could repeat it without having to keep going back to the menu and keep changing the target etc. All you have to do is press the record button and then make your goal seek and then make absolutely sure to stop the record button once you have finished with the goal seek. This process is described in the video below. The file that I have used to explain this simple macro is associated with my WACC and interest shield stuff and listed below the video.
WACC and Growth with Goal Seek Macro.xlsm
This page describes how to use spinner boxes, drop-down (combo boxes) and other control forms in excel. These spinner box, combo box, check box and other user forms can be very effective in your corporate and project finance models when presenting contract structuring issues, presenting scenario analysis, making flexible graphs and many other things. It is essential to include the sheet name in the cell link of the user forms so you can use the forms on different sheets. With the control forms you can create flexible summary sheets and diagrams. As with using short-cut keys, participants in classes that I teach sometimes complain that they will not remember how to use the forms — spinner boxes, drop-down boxes (called combo boxes), check boxes and list boxes.
The video and file below are intended to be a reminder for how to used the developer tab and these forms. User forms or controls allow you to control inputs from different pages. This means that if you have an input in the assumptions page, say for the gearing percent of a project, this input can be controlled from multiple places including a summary sheet and a graph. These form controls can be very helpful in summarising data and making presentations with excel or just showing off to your boss. I use these forms extensively in other videos and files but I do not know if there is a place where I explain them slowly. The video below does this. It begins with explaining how to find the developer tab and then use the WINDSCREEN method to allow you to move the files (thanks to Tatiyana in Russia). My son tells me the developer tab is like a secret handshake in excel — when somebody tells you they are really good at excel and there is no developer tab on their computer, you say yes you are really good. But you don’t believe them.
The spinner boxes require that you put the developer tab in your excel menu. You just have to go to the excel options and then to the customize ribbon. Then you get a screen with check boxes. Just click make sure he developer tab is clicked.
http://www.youtube.com/embed/J3wm-WTp_jk Sun Edison Case Study.xlsm
Video for Simple Macro with Goal Seek
As with the short-cut keys, participants in my classes sometimes say they will not remember how to make a macro. This little section is intended to be a reminder. You can do really fancy things with macros and functions that I hope are explained at various points in this website. This includes reading data from the internet, creating fancy scenarios, fixing excel to interpolate data, and resolving circular references. If you have not made a macro, however, do not be intimidated. The first macro I ever made sometime in the early 1990’s was to fix the goal seek in excel so you could repeat it without having to keep going back to the menu and keep changing the target etc. All you have to do is press the record button and then make your goal seek and then make absolutely sure to stop the record button once you have finished with the goal seek. This process is described in the video below. The file that I have used to explain this simple macro is associated with my WACC and interest shield stuff and listed below the video.
WACC and Growth with Goal Seek Macro.xlsm