How to use Form Controls in Excel

Form Control is a set of special objects in Excel. These objects when inserted into the Workbook will allow us to create:

1. List box.
2. Check box to control the Show/Hide of one or several objects.
3. Increase/Decrease button to control the value of a cell in Worksheet…

There are all 9 Form Controls in Excel, which are: Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Group Box, Label and Scroll Bar

Yes, and in this article I will show you how to use all the Form Controls listed above, except Button and Label.

For these 2 Form Controls because I have never used them, I can't give you more detailed instructions, so please understand… I will add more later.

#first. Add Developer tab in Excel

Method 1:

+ Step 1: Select File => select More… => select Options

How to use Form Controls in Excel Picture 1

+ Step 2: The Excel Options dialog box appears, then select Customize Ribbon => tick Developer => select OK

How to use Form Controls in Excel Picture 3

Method 2:

+ Step 1: Right-click File => select Customize the Ribbon.

How to use Form Controls in Excel Picture 5

+ Step 2: Then do the same as Step 2 in Method 1, guys.

#2. Steps to insert Form Controls in Excel

+ Step 1: Select Developer => select Insert => choose 1 of 9 Form Controls that Excel provides.

How to use Form Controls in Excel Picture 7

+ Step 2: Click anywhere in the Worksheet to insert.

How to use Form Controls in Excel Picture 9

#3. Some basic operations with Form Controls

As mentioned at the beginning of the article, these Form Controls are special objects so you cannot manipulate them like normal objects.

3.1. Resize Form Controls

Method 1:

Right click on Form Controls => then 6 'white circles' will appear drag and drop to resize.

How to use Form Controls in Excel Picture 11

Method 2:

+ Step 1: Right-click on Form Controls => select Format Control…

How to use Form Controls in Excel Picture 13

+ Step 2: In the Format Control box => select the Size tab => re-enter the size in the Height and Width boxes => select OK chọn

How to use Form Controls in Excel Picture 15

3.2. Move Form Controls

Right-click Form Controls => press ESC key on the keyboard => when the mouse pointer turns into a '4-way arrow', drag and drop the mouse to move.

How to use Form Controls in Excel Picture 17

3.3. Delete Form Controls

Right-click on Form Controls => press the ESC key on the keyboard => press the Delete key on the keyboard.

How to use Form Controls in Excel Picture 19

3.4. Open the Format Controls dialog box

Right-click on Form Controls => and select Format Controls.

How to use Form Controls in Excel Picture 21

In addition to the above method, you can also select Form Controls => and then select Properties on the Developer tab.

How to use Form Controls in Excel Picture 23

In this section, I cannot guide you in detail how to format the Form Controls, because each Form Controls will have different formatting options.

For details on how to format see part #4 below.

#4. The Most Used Form Controls in Excel?

In this section, I will present the 4 most used Form Controls. Specifically, I will describe the function, customization instructions and illustrative examples.

Combo Box and List Box; Spin Button and Scroll Bar have similar functions and customizations, so I only guide one Form Controls. Specifically, I will guide Combo Box and Spin Button.

4.1. Combo Box/ List Box

This Combo Box allows us to create a list, from which the user can select any item in this list.

List items are defined by the data in the cell block. This block of cells will be declared in the Format Controls dialog box.

The return value at the linked cell is the ordinal number of the items in the list:

+ Step 1: Create Combo Box

+ Step 2: Open the Format Controls dialog box => select the Control tab

1. Input range: Select the cell block containing the list.
2. Cell link: Select the link cell.
3. Drop down line: Select the number of lines to drop down.

+ Step 3: Select OK

How to use Form Controls in Excel Picture 25

4.2. Check Box

Details on how to use this Form Controls have been presented in the article Create a Check Box to control Conditional Formatting, please review if you have not read that article.

4.3. Spin Button/ Scroll Bar

Spin Button is one of the easiest to use Form Controls. With this Form Controls you can click on the 'black triangle buttons' to increase or decrease the value of the cell.

How to use Form Controls in Excel Picture 27

In the Format Controls dialog box, select the Control tab

1. Current value the current value.
2. Minimum value is the smallest value.
3. Maximum value is the maximum value.
4. Incremental change increment/decrement value on one time.

4.4. Option Button and Group Box

Option Button is never inserted individually but will be inserted in pairs or groups of many different Option Buttons. These Option Buttons will be grouped into a Group through the Group Box.

In the same Group at the same time, only one Option Button can be selected:

+ Step 1: First, insert the first Option Button.

How to use Form Controls in Excel Picture 29

+ Step 2: Then, right-click and select Edit Text to rename.

How to use Form Controls in Excel Picture 31

+ Step 3: Repeat Step 1 and Step 2 to create the remaining Option Buttons.

How to use Form Controls in Excel Picture 33

+ Step 4: Continue to insert Group Box

How to use Form Controls in Excel Picture 35

+ Step 5: Right-click on Group Box => and select Edit Text to rename…

How to use Form Controls in Excel Picture 37

+ Step 6: Continue to right-click on the first Option Button => select Format Control

How to use Form Controls in Excel Picture 39

+ Step 7: In the Format Control dialog box, select the Control tab => at Cell link select the cell to be linked. Here I will link to cell A3

=> The remaining Option Buttons will automatically receive cell A3 as a link cell.

How to use Form Controls in Excel Picture 41

Normally, Form Controls in general or Option Button and Group Box in particular only come into play when it is combined with a certain formula.

For example, in this example, I will assign the formula =IF(A3=1,"Excellent", IF(A3=2,"Good",IF(A3=3,"Good", 'Average')) ) for cell B1.

=> When the user selects any Option Button in the Group Box, cell B1 will automatically appear his graduation rank.

How to use Form Controls in Excel Picture 43

#5. Epilogue

Okay, so I just shared with you the knowledge about Form Controls, as well as how to use Form Controls in Excel.

Yes, Form Controls is one of the advanced features in Excel, because it is advanced so it is quite confusing and difficult to use. However, this is a great feature and it will help you a lot for your work.

Jessica Tanner

Update 15 June 2021