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.”
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.
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.
As an example, we’ll insert a drop-down list using a form control.
Click the Combo Box icon from the form controls and draw the box.
Select the Combo Box and click “Properties” in the Controls section of the ribbon.
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.”
You then have a nice and neat selectable list.
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.
Right-click the Checkbox control, select “Edit Text,” and enter the text you want to display.
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.”
We then follow the same process to create our next checkbox. Now our form is really taking shape!
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.
Go to the Review tab and select “Protect Sheet” in the Protect section of the ribbon.
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.”
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.
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.
