The basic of Form Controls

Want to work like an Excel Pro?  Learn more about Form Controls.

Have you seen someone using Check box, Option Buttons, Scroll Bars on their worksheets and be impressed?  You may wonder how they made it and think that is something requiring professional Excel skills…


Indeed, it is easy to insert Form Controls and use them to interact with data on worksheet.

You may download a Sample File to follow through.

First thing first,

Where is Form Controls?

First, you have to add the Developer Tab.  Please don’t be afraid of the word “Developer”.   You don’t need to know a single line of code in order to use Form Controls.

Go to File –> Options –> Customize RibbonExcel Tips - Form Controls 1.PNG

Check the “Develop” and OK.  Then, you should see the Developer Tab on your ribbon.

Go to Developer Tab –> Insert , where you find the Form ControlsExcel Tips - Form Controls 2.PNG

Now you know where they are.

So What? are they?

Excel Tips - Form Controls 3.PNG

There are 9 Form Controls you can select from.  The final three are inactive and they are for Excel version 5.0 dialog sheets only.

You may find the official descriptions HERE so I am not going to repeat them.  I believe it you may be more interested in learning about HOW to use these From Controls to interact with your data.

How to insert?

Just select the Form Control you need; then move your cursor to where you want to insert, click on it.  As simple as that.

Excel TIps - Form Controls 4.gif

Note: You may edit the size by drag and drop; or edit the text anytime you want.

How to make Form Controls Interactive?

Assign Macro

Basically you can Assign Macro to any of the Form Controls.

Excel Tips - Form Controls 5.PNG

Nevertheless, the benefits of using Form Controls is to interact with cell data without VBA.  And this is the focus of the post.

Interact with data – Format Control

You may have noticed that after you have inserted Form Controls, you can click on it but nothing happens.  Yes, nothing will happen until you “Format Control…

Combo box and List box

For Combo box and List Box, you need to assign an Input Range, a list on your worksheet that you want to feed into the Combo Box or List Box

Note: Make sure you are on the “Control” tab in the Format Control dialogue box. However, you won’t find “Control” tab for Button and Label.

How they interact with the cell linked?

You may expect that they return the value selected.  However, they returns the position of your selection in the list. (Observe the change in A1 and E1):Excel TIps - Form Controls 8.gif

How about other forms?

Let’s go through them one by one.

Check Box

Excel Tips - Form Controls Checkbox.PNG

How it interacts with the cell linked?  

It simply returns TRUE or FALSE.

Excel TIps - Form Controls CheckBox.gif

Option Buttons

Excel Tips - Form Controls Option Button.PNG

How it interacts with the cell linked?

It returns an index number according to the order you placed the Option buttons; even the newly added Option button.

Excel TIps - Form Controls Option Button.gif

However, if you want to insert a new Option Button that is independent to the existing group of buttons, you will need Group Box. See demo below:

Excel TIps - Form Controls Option Button1.gif

Spin button and Scroll Bar

They have more controls.  You need to set Min and Max Value, as well as Incremental change.

Excel Tips - Form Controls Spin Scroll.PNG


  1. Scroll Bar allows “Page change“, i.e. a big jump in numbers (see demo below)
  2. You may place a horizontal Scroll Bar… but there is no way to make a horizontal Spin button… 😦

How they interacts with the cell linked?

They return a whole number in the range of the Max and Min value set.

Excel TIps - Form Controls Spin Scroll.gif


As you see, inserting Form Controls and using them are easy.  I would suggest you play with the various Form Controls and get yourself familiarize with how they interact with cell data.  It requires some efforts and thoughts to apply them in your Excel sheets.     Let’s talk about some examples later.  Stay tuned. 🙂

Meanwhile, below are some posts with examples using Form Controls.


Wait… What about “Label”?

Well, I have no idea why and when I need to use “Label”.  Why not just use Text Box?


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

6 Responses to The basic of Form Controls

  1. FenderT says:

    How do you do expand a table which has form controls without having to adjust the cell links one at a time? I’ve tried everything with the format control box i.e. using the “$” in when referencing the cell to no avail.


    • MF says:

      Hi FenderT, would you please elaborate a bit to help me understand better what you want to achieve ?


      • FenderT says:

        I have a table in excel which has about 30 rows and 10 columns . 5 of the 10 columns are cells with a “spin button” ( up and down arrow) that I linked to its adjacent cell.

        I need to expand the column (prolly to 100 rows) however when I copy the one row all the controls are still linked to the cell referenced from the row it was copied from.

        Which means I would have to re link each button to it’s new adjacent cell. So far that’s what I’m doing. It will take up a lot of my time.


  2. says:

    Hey mate this is very nice!, woud you mind sharing the file from the first picture? Like how do you highlight the bars on focus ? (on the right side chart). Hope to hear from ya!


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.