How to Create a Basic Form in Microsoft Excel







Microsoft Excel Logo

You have used tools like Microsoft Forms, Google Forms, or even Microsoft Word to create forms. But if you’re most comfortable using Excel, why not use it to create the form you need? Here’s how.

Enable the Developer Tab in Excel

In order to use the tools that you need to create a form in Excel, you must enable the Developer tab. If you already see this tab, you’re one step ahead and can move to the next section. But if not, here’s how to show the Developer tab.

Open Excel and go to File > Options. Click “Customize Ribbon” on the left side of the Excel Options window.

On the right, use the Customize the Ribbon section and select “Main Tabs” in the drop-down box. Then below, check the box for Developer and click “OK.”

Show the Developer tab in Excel

When the Excel Options close, you should see the Developer tab.

Create a Form in Excel

To add controls to your form such as drop-down lists, buttons, or list boxes, head to the Controls section on the Developer tab.

Advertisement

Go to a spot on your sheet where you want to add a control and click the Insert drop-down arrow. Select the control you want to add and then use the plus sign that appears to draw its size.

On the Developer tab, click Insert for the form controls

As an example, we’ll insert a drop-down list using a form control.

Note: You can also add a drop-down list in Excel using the Data Validation feature as well as a dependent drop-down list.

Click the Combo Box icon from the form controls and draw the box.

Draw a combo box

Select the Combo Box and click “Properties” in the Controls section of the ribbon.

On the Developer tab, click Properties

On the Control tab, enter the Input Range which are the cells containing the list items. You can also drag through the cells to populate that field. Click “OK.”

Add the combo box Input Range

You then have a nice and neat selectable list.

Combo box in Excel

Advertisement

As another example, we’ll insert a couple of checkboxes for Yes and No. Click the Checkbox icon from the form controls and draw the first checkbox. Be sure to make the size wide enough to accommodate the text you want next to the checkbox.

Draw the checkbox

Right-click the Checkbox control, select “Edit Text,” and enter the text you want to display.

Right-click and pick Edit Text

To adjust additional options for the form control, such as displaying the box as unchecked, select the control and click “Properties” in the ribbon. Make your adjustments and click “OK.”

Edit the checkbox properties

We then follow the same process to create our next checkbox. Now our form is really taking shape!

Checkboxes in Excel

You can use these types of controls for your form or choose others like the buttons, scroll bars, and option buttons.

Protect Your Form

If you plan to share the form digitally, you may want to protect it from unwanted changes to the text and controls.

Advertisement

Go to the Review tab and select “Protect Sheet” in the Protect section of the ribbon.

On the Review tab, click Protect Sheet

Select all of the options you want and optionally include a password. Note that if you don’t require a password, another user can unprotect the sheet. Click “OK.”

Choose the items to protect and click OK

When you share the spreadsheet, others will be able to use the form controls but not edit them or other items in the sheet you protect. If they try, they’ll receive an error message letting them know the sheet is protected.

Protected sheet error in Excel

If you want to make additional changes to your form, click “Unprotect Sheet” on the Review tab.

For more help with locking specific cells to protect them from editing, check out our how-to for locking cells in Excel.

<