Where Are The Form Controls In Different Versions Of Excel

Mon 24th January 2011

Form controls in different versions of Excel are located in different places, so they can be hard to track down. Learn where to find these essential controls in any Excel version and learn how to use one of the most popular ones, the Combo box.

Form controls in Excel worksheets let users add items such as check boxes, combo boxes and buttons to a worksheet and data can then be added to one or more worksheet cells by using these control rather than entering data directly.

Form controls can be used, for example, with data tables, calculations or charts to allow easy data entry and data control. However the location of these controls differs between versions. This article describes how to find these controls in Excel 97-2003, Excel 2007 and Excel 2010 and then describes how to use one of the controls, the combo box.

Locating Form Controls in Excel 97-2003

The controls are found in the Forms toolbar. To show the toolbar, click on View in the upper menu bar, then Toolbars and choose Forms. You'll see the various controls on this toolbar such as label, group box, Button, Check Box, Option Button, List Box, Combo Box, Scroll Bar, Spinner, and Toggle Grid.

Locating Form Controls in Excel 2007

To access the form controls in Excel 2007 you need to turn on the Developer tab. You can do this by choosing the Office Button at the top left, then click on Excel Options on the lower part of the screen. In the Popular section click the checkbox "Show Developer tab in the Ribbon" and click OK to finish. Now you'll see the Developer tab in the Excel 2007 Ribbon. Click the Developer tab and in the Controls group click the Insert button. You'll see all the form controls on the pop down. These are Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Group Box, Label and Scroll Bar.

Locating Form Controls in Excel 2010

To access the form controls in Excel 2010 you also need to turn on the Developer tab, but you do this in a slightly different way. To do this, click the File tab, then click Options in the left hand index, then click Customize Ribbon. In the Customize the Ribbon panel you'll see two columns. In the right hand column look under Main Tabs and click into the checkbox for Developer tab and click OK to finish. Now you'll see the Developer tab in the Excel 2010 Ribbon. Then form controls are the same as in Excel 2007. To see them Click the Developer tab and in the Controls group click the Insert button and you'll see the same form controls in the pop down.

Using a Combo Box Form Control in Excel

We'll now describe how to use a popular form control - the Combo Box. This control enables the user to add data to a chosen cell by selecting values from a list of data. This list can be created earlier in a different part of the spreadsheet. When the Combo is first created, a setup panel appears allowing you to specify which cells containing your list of values, and which cell you want the Combo box to add the data to.

The Combo actually adds a number to the specified cell which corresponding to the position of the chosen data in the original list. You might then use a lookup formula to convert this number into the actual value from the original list.

We begin by typing in the actual staff names in an empty part of the worksheet. For example we'll type the names Mary, John, Jane and James in successive cells downwards, starting in cell M1. So M1 contains Mary, M2 contains John and so on. We also need to number the names from 1 upwards, so in cell L1 type a 1, in cell L2 a 2 and so on, so that the staff names are numbered 1 to 4.

Now we'll set up the Combo to enable the user to select a staff name and add its corresponding number to cell D1. To create the combo click once on the Combo Box control either on the Forms toolbar or in the Developer tab. Then to the right of cell D1 press and hold the mouse left button and drag out a rectangle shape. Next right click on the resulting Combo and choose Format Control. In the Format Control panel click, look for the button with the red mark at the right of the " Input Range box". Click this and then highlight the four staff names you typed earlier, and press Enter key to return to the panel.

Do the same for "Cell Link" but this time click into cell D1, and press Enter. Then click Ok to complete. In these steps we have set the Combo to choose the names from our list and we have also linked the choice to cell D1. So now if you choose a name using the Combo you'll see the corresponding number appear in cell D1.

In cell E1 we want to see the actual name. So in E1 type this formula =VLOOKUP(D1,L1:M4,2) and press Enter. Now you should the selected name in cell E1. Try changing the Combo selection and cells D1 and E1 will change to show the chosen number and name.

Interested in learning more about form controls and associated functions such as lookups in Excel? A really effective way would be to attend a training course and find out much more about these very useful features.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1413-where-are-form-controls-in-different-versions-excel.html