Tanjim Reza
When working with Excel, sometimes, we need user inputs. Forms make this easy to take inputs from users and process them accordingly. In this article, I will show you 2 suitable ways to design a form in Excel.
How to Design a Form in Excel: 2 Suitable Examples
Generally, we see two types of forms in Excel. One is the Data Entry form and the other one is the drop-down form. Go through the article below to learn about designing all these forms in Excel.
1. Design a Printable and Automated Basic Drop-Down Form in Excel
You can use the Form Controls group to design a basic automated drop-down form in Excel. Follow the steps below to do this.
📌 Steps:
- First and foremost, insert your form’s row headers.
- At this time, for drop-down list options, insert your list in the sheet.
- Afterward, for Gender options, go to the Developer tab >> Insert tool >> Form Controls group >> Option Button (Form Control) option.
- Following, drag an area to establish the option in the proper place.
- As a result, an option button will appear.
- Subsequently, rename it as Male.
- Follow the same procedure to create another option button named Female.
- Following, click on the option that you want.
- Afterward, for Department fill up, go to Developer tab >> Insert tool >> Form Controls group >> Combo Box (Form Control) option.
- Afterward, drag an area to establish the combo box.
- Following, right-click on the box and choose the Format Control… option.
- As a result, the Format Object window will appear.
- Following, go to the Control tab >> choose the cells E5:E8 in the Input range: text box >> click on the OK button.
- As a result, the drop-down would appear in the desired place.
- Following, choose the option as your desire from the dropdown list.
- Now for the last user input, we need checkboxes.
- To do this, go to the Developer tab >> Insert tool >> Form Controls group >> Check Box (Form Control) option.
- Following, drag an area to place the checkbox in the proper place.
- Subsequently, rename it as Yes.
- Now, follow the same procedures to establish another check box named No and tick your desired option.
- In this type of form, you need to protect your sheet.
- To do this, go to the Review tab >> Protect tool >> Protect Sheet option.
- Consequently, the Protect Sheet window will appear.
- Subsequently, tick the first two options and click on the OK button.
Finally, your printable basic form is ready. As you have protected the sheet, it would not change accidentally. And, the final design of the form should look like this.
Read More: How to Create Data Entry Form with Drop Down List in Excel
2. Design a Data Entry Form in Excel
Now, you might need to design a data entry form. You can use this by Form command. Go through the steps below to do this.
📌 Steps:
- First, create a table named Data_Entry_Form that you need to fill by user inputs.
- Afterward, right-click on your Excel ribbon.
- Following, choose the Customize the Ribbon… option.
- As a result, the Excel Options window would appear.
- Following, choose the Customize Ribbon tab >> click on the New Group button.
- Following, click on the Rename… button on the same tab of the same window.
- As a result, the Rename window would appear.
- Following, write Form in the Display name: text box and click on the OK button.
- Afterward, click on the New Tab button on the same tab of the same window.
- Now, at the same tab of the same window, choose the option All Commands from the Choose commands from: options.
- Following, choose the Form… option from the All Commands list.
- Subsequently, click on the Add button and finally the OK button.
- As a result, there will be the Form command in a new tab at your Excel toolbar.
- Now, for easier access, you can add the command in your ribbon.
- To do this, go to your Excel ribbon and right-click on the ribbon.
- Choose the option Customize Quick Access Toolbar… from the options.
- As a result, the Exel Options window will appear.
- Following, go to the Quick Access Toolbar tab >> All Commands option from the Choose Commands from: options >> Form… option from the All Commands list >> Add button >> OK button.
- As a result, the Form command is added to your toolbar for easier access.
- Now, click inside your table (cell B5 here) and click on the Form icon in the toolbar.
- As a result, the Data Entry Form window would appear. And there are text boxes according to the headers of the created table.
- Now, insert values in every text box according to your requirements.
- Subsequently, click on the New button.
- As a result, the new information is inserted into the first row of the table.
- Now, for clicking the New button, the same form window would appear again and you can insert the next cells’ values.
- If you click on the New button again, these data will be input and another form would appear for the next cells’ inputs as long as the table range appears.
- Besides, You can add Data Validation in your form. Say, you want to restrict the comments to 15 texts.
- To do this, select the cells (E5:E9 here) where you want to apply your data validation.
- Afterward, go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation option.
- As a result, the Data Validation window would appear.
- Following, go to the Settings tab >> choose the option Text length in the Allow: options list >> less than option from the Data: options list >> insert the value 15 inside Maximum: text box >> click on the OK button.
- Thus, your data validation would be applicable to the comment cells of your table. And, if you insert a comment greater than 15 text characters, it would not take the input and would show you the reason.
Now, Following the previous ways of form inputs, you can fill the whole table with your required data. And, the table would look like this finally after using the designed form.
Note: This type of form is applicable to tables with headers.
Read More: How to Make a Fillable Form in Excel
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, I have shown you 2 suitable ways to design a form in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations. Thank you!
