If you’re new to Microsoft Excel, or if you’re looking for a way to streamline your work flow, creating a basic form can be a great way to get started. In this article, we’ll show you how to create a simple form in Excel, and how to use the form’s various features. To start, open Microsoft Excel and create a new workbook. (If you don’t have Microsoft Excel installed on your computer, you can download it for free from the Microsoft website.) Next, click the File tab and select New > Workbook from the menu bar. In the New Workbook dialog box, enter the name of your new workbook (for example, “Forms”), and then click OK. Now that we’ve created our workbook, let’s start creating our form. To do this, first select all of the cells in the worksheet that you want to include in your form. To select multiple cells at once, press Ctrl+A (or use your mouse). Then use your keyboard arrow keys to move through the cells until you’ve selected all of them. Once you’ve selected all of the cells that you want to include in your form, click on one of them (by clicking on it with your mouse or pressing Ctrl+click). This will open up the Formulas tab of the ribbon window. In this tab, click on the Formulas button (invisible until now), and then select New > Formula from the menu bar. The New Formula dialog box will appear. In this dialog box, type =Form1() into the Name field (where Form1() is simply an abbreviation for “the cell containing our formula”), and then press OK. The result should look like this: ..
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.
