How to Use Form Controls in Excel (Detailed Analysis)

This article will focus on different types of form controls and how to use them in Excel. You can give your worksheets form-like functionality without ever having to use VBA. In order to achieve this, you will have to use form controls. Form controls are accessed from the Developer Tab in Excel and can greatly enhance one’s worksheet, report, or dashboard.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

What Are Form Controls in Excel?

Form controls in Excel are objects we can insert in the spreadsheet to use for different purposes to work with the data in the spreadsheet. These are meant as controls for the actions of a sheet.

We can assess them through the Developer tab of the ribbon.

8 Different Types of Form Controls That You Can Use in Your Excel Spreadsheet

To use any type of form control in Excel, you first need to have the Developer tab in your ribbon. If you don’t have one, click here to see how you can display the Developer tab on your ribbon.

Once you have that, you can try out the steps in the sections dedicated to each form control feature. Keep reading to find out about each one or find the one you need from the table above.

1. Button

This is one of the most useful features of the form controls in Excel. It is similar to the radio button used in programming languages. Usually, we can assign a macro to it. So every time we click on the “button”, the macro runs. For the demonstration, we are choosing a VBA code that fills a cell with blue color.

This is the spreadsheet before adding any button or running any code.

We are going to add a button on it that can turn any selected cell blue. For that, we are following the steps below.

Steps:

• First, let’s add the VBA code to the file.
• For that, go to the Developer tab on your ribbon.
• Then select Visual Basic from the Code group.

• Next, click on the Insert tab and select Module from the drop-down list.

• Now select the module and enter the following code in it.

Sub blue_fill()
Selection.Interior.ColorIndex = 37
End Sub

• After that, close the window and go to the Developer tab on your ribbon again.
• Then select Insert from the Controls group.
• Now select Button (Form Control) from the drop-down list.

• The mouse cursor will turn into a cross sign after that.
• Now click and drag over the area of the spreadsheet where you want your button to be.
• After that, select the macro through the sub name in the Assign Macro box and click on OK.

• As a result of all these steps, a button will appear over the area you dragged in the previous step.

• You can also rename the button by right-clicking on it and then selecting Edit Text from the context menu.

• This is our final result.

• Finally, Now if you select a cell and click on the button it will turn blue.

This is how we can utilize the button feature of the form controls option in Excel.

2. Combo Box

Next up, we are going to demonstrate how to use the combo box from the form controls in this section in Excel. The combo box is helpful when you have to select from a bunch of lists every time. Instead of manually typing the values every time, the combo box can do the same with a few clicks.

Follow these steps to use the combo box feature in Excel.

Steps:

• First, go to the Developer tab on your ribbon.
• Then from the Controls group, select Insert.
• After that, select Combo Box (Form Control) from the drop-down list.

• Then click and drag over the area where you want your combo box to be in on the spreadsheet. After that, it will look something like this.

• Now right-click on the combo box and select Format Control from the context menu.

• Next, go to the Controls tab of the Format Control box and select the range B5:B9 as the Input Range. You can also change other parameters to your liking at this point.

• Finally, click on OK.

Now if you click on the downward-facing arrow of the combo box, you will see the list of the selected range as a drop-down menu.

This is how we can utilize the combo box feature of the form controls options in Excel.

3. Check Box

In this section, we are going to discuss the check box of the form controls feature in Excel. These checkboxes can be used to sum up, binary decisions from it such as whether a task is complete or not. For the demonstration, we are going to use the following dataset.

Here, we are going to insert checkboxes in column C, and from it, we will display a value of whether the item is sold or not in column D.

Follow these steps to see how we can do that.

Steps:

• First, go to the Developer
• Then select Insert from the Controls group.
• After that, select Check Box (Form Control) from the drop-down menu.

• Now click and drag over the area of the spreadsheet where you want to insert the check box. Here, we have done it over the area of cell C5.

• We are removing the text by right-clicking on the box and selecting Edit Text from the context menu and then clearing the cell.

• Now right-click over the check box again. This time, select Format Control from the context menu.

• Next, go to the Control tab on the Format Control box and select cell D5 as the linked cell.

• Finally, click on OK. It will look something like this after this.

• Now repeat this for all of the rows you want.

If you check any of the checkboxes now in this spreadsheet, the cell value beside it will turn into TRUE at this point.

This is how we can utilize the check box feature of the form controls in Excel.

4. Spin Button

In this next section, we are going to add a spin button to our Excel spreadsheet. The main function of this feature is to change the values of a cell by a set of intervals. This is helpful if your values can only be in a certain range and can have certain intervals in between them.

Follow these steps to see the usage of the feature in our dataset. We are going to change the quantity sold through a spin button.

Steps:

• First, go to the Developer tab on the ribbon.
• Then from the Controls group, select Insert.
• After that, select Spin Button (Form Control) from the drop-down.

• Next, click and drag the mouse icon over the area you want the spin button to be in. Here, we are selecting the area over cell F5 for the demonstration. Upon release it should look like this.

• Now right-click on the spin button and select Format Control from the context menu.

• As a result, the Format Control box will pop up.
• Now go to the Control tab in it.
• And select the minimum, maximum, and incremental change according to your liking. We have selected the following values for the demonstration.
• And most importantly, put the value of the linked cell beside the Cell Link Here, we have selected cell D5 for it.

• Finally, click on OK.

Now the value of cell D5 will go up by the increment if we press the up button of the spin box.

And the value will go down if we click on the down arrow.

This way we can use the spin button feature from the form controls option in Excel.

5. List Box

In the same dataset, we are going to add a list box. In list boxes, as the name suggests, you can add multiple items from a list. This is particularly helpful when you don’t want to use a drop-down list from a combo box. Otherwise, the end result is almost the same.

Follow these steps to use a list box from the form control options in Excel.

Steps:

• First, go to the Developer tab on your ribbon.
• Then select Insert from the Control group.
• After that, select List Box (Form Control) from the drop-down menu.

• Next, click and drag the area on the spreadsheet where you want the list box to be.

• After that, right-click on the list box and select Format Control from the context menu.

• As a result, the Format Control box will pop up.
• Now go to the Control tab in it.
• After that, select the range B5:B9 as the Input range.
• Also, we are selecting cell G5 as the linked cell.

• Finally, click on OK. This will pop up on the spreadsheet.

Now let’s modify the sheet and select an option from the list.

This is one way to use the list box from the form controls feature in Excel.

6. Combining Group Box with Option Button

We can insert Option Button similar to the previous features. But doing so will only add one option to the object or area we will create. So, it is often better to use it in combination with a group box. Also, we are going to use the IF function with this combination later on.

Follow these steps for the full process.

Steps:

• First of all, go to the Developer tab on your ribbon.
• Then select Insert from the Control group.
• After that, select Group Box (Form Control) from the drop-down list.

• Now click and drag the area on the spreadsheet where you want to add the group box.

• After that, modify it by right-clicking on it and then selecting Edit Text from the context menu.

• Next, select the Option Button(Form Control) from the drop-down from the Insert of the Control group of the Developer tab.

• Now click and drag over the area on the group box where you want to add options.

• Similar to all other objects of the article, you can right-click the area and select Edit Text to rename the option.

• After renaming, copy and paste the options box and edit the texts.

• Now right-click on the group box and select Format Control from the context menu.

• In the Format Control box, go to the Control tab and link the cell to C13.

• After that, click on OK. Now select an option and there will be a number on the cell.

• Next, select cell C14 and write down the following formula.

• After pressing Enter it will look like this.

• Finally, let’s make the content of cell C13 To do that, select the cell, and press Ctrl+1 on your keyboard.
• Then select the Number tab in the Format Cells box and select Custom on the left.
• On the right, under Type, type in ;;; (three semi-colons).

• Finally, click on OK.

The sheet will finally look like this.

If you choose another option now, the value in cell C14 will change accordingly because of all these steps.

7. Label

The label feature from the form controls doesn’t have any practical use besides just labeling. It can be used as an object that can contain comments. If you want to use labels from the form controls in Excel, follow these steps.

Steps:

• First, go to the Developer tab in the ribbon.
• Then select Insert from the Control group.
• After that, select Label (Form Control) from the drop-down.

• Then click and drag over the area of the spreadsheet where you want to place the label.

• Similar to all objects of the article, you can edit the label by right-clicking on it and then selecting Edit Text from the context menu.

Finally, the label from the form control will look like this on the Excel spreadsheet.

8. Scroll Bar

Finally, we are going to talk about another feature of the form controls options in Excel. This is called the scroll bar. It is another way to pick values from a range. As the name suggests, it is a scrollable button that gives an output depending on the position you set in the bar. This is particularly helpful if the values in a cell fluctuate for a wide range and you don’t want to enter them manually.

Follow these steps to see how you can add a scroll bar from the form controls easily in Excel.

Steps:

• First, go to the Developer tab in your ribbon.
• Then select Insert from the Controls group.
• After that, select Scroll Bar (Form Control) from the drop-down menu.

• Now click and drag over the area where you want the scroll bar to be.
• Upon release, there will be a scroll bar on top of the spreadsheet.

• Now right-click on the scroll bar and select Format Control from the context menu.

• Next, go to the Control tab in the Format Control box and select the suitable Minimum value, Maximum value, and Increment change. Here, we have selected the range 0 to 100 with an increment change of 1.
• Also, make sure to link the scroll bar with a cell in the Cell link For this dataset, we have selected cell D5 for the purpose.

• Finally, click on OK.

The scroll bar is ready to use on our spreadsheet at this point.

If we move the scroll bar now the value of cell D5 will change.

Form Controls vs ActiveX Controls in Excel

If you go down to the drop-down list of the Insert from the Controls group of the Developer tab, you can see there are two types of objects available to insert – Form controls and ActiveX controls. While they may seem the same, there are some basic differences between them.

• One major difference between them is that you can access ActiveX controls through the VBA editor and control them programmatically.
• Form controls provide much simpler functions and designs.
• Although ActiveX is a bit complex compared to Form controls, these provide a much more flexible design and provide functionalities that Form controls cannot provide.
• Many systems by default won’t trust ActiveX controls and you need to manually activate them through the trust center.
• Another key difference you can find is that ActiveX is a Microsoft-based technology. So you cannot use it on a system with the Mac operating system.

Read More: Key Differences in Excel: Form Control Vs. ActiveX Control

Things to Remember

👉 You can toggle between design and edit mode easily for the form control objects by right-clicking on the object and clicking elsewhere.

👉 To delete a form control object, you need to go to the edit mode and then press Delete on your keyboard. Another way of doing the same is to right-click on the object and then select Cut from the context menu.

👉 There is another option to go into edit mode for the objects of the form controls through the Excel ribbon. You can toggle the design mode off and on through the option under the same name in the Controls group of the Developer tab.

Conclusion

That concludes the discussion on different types of form controls in Excel. Hopefully, you have grasped the basics of using different form controls and can use them individually and in combination with each other in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com.

Related Articles

• How to Remove a Form Control in Excel
• How to Create Chart Slider in Excel
• How to Make Games in Excel

<< Go Back to Form Control in Excel | Learn Excel