How to Design Form in Excel (2 Suitable Examples)

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.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Suitable Examples to Design a Form in Excel

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.

Row Headers to Design a Basic Form in Excel

  • At this time, for drop-down list options, insert your list in the sheet.

Department List to Create Drop-Down in Form Design

  • Afterward, for Gender options, go to the Developer tab >> Insert tool >> Form Controls group >> Option Button (Form Control) option.

Access 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.

First Gender Option

  • Follow the same procedure to create another option button named Female.
  • Following, click on the option that you want.

Gender Options

  • Afterward, for Department fill up, go to Developer tab >> Insert tool >> Form Controls group >> Combo Box (Form Control) option.

Insert Combo Box (Form Control) Tool

  • Afterward, drag an area to establish the combo box.
  • Following, right-click on the box and choose the Format Control… option.

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.

Format Object Window to Design a Form in Excel

  • As a result, the drop-down would appear in the desired place.
  • Following, choose the option as your desire from the dropdown list.

Dropdown Option in Form Design in Excel

  • 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.

Insert Check Box (Form Control) to Design Form in Excel

  • Following, drag an area to place the checkbox in the proper place.
  • Subsequently, rename it as Yes.

First Checkbox Option in Form Design in Excel

  • 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.

Enable Protect Sheet Feature to Use and Print the Designed Form in Excel

  • Consequently, the Protect Sheet window will appear.
  • Subsequently, tick the first two options and click on the OK button.

Protect Sheet Window

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.

Basic Printable Drop-Down Form Design in Excel

Read More: How to Create Data Entry Form with Drop Down List in Excel (2 Methods)


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.

Data_Entry_Form Table

  • Afterward, right-click on your Excel ribbon.
  • Following, choose the Customize the Ribbon… option.

Choose 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.

Excel Options Window to Design Form in Excel

  • Following, click on the Rename… button on the same tab of the same window.

Rename the Created Group

  • As a result, the Rename window would appear.
  • Following, write Form in the Display name: text box and click on the OK button.

Rename Window

  • Afterward, click on the New Tab button on the same tab of the same window.

Click on New Tab to Design Data Entry Form in Excel

  • 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.

Add Form Command to Toolbar to Design Form in Excel

  • As a result, there will be the Form command in a new tab at your Excel toolbar.

Form Command in New Tab

  • 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.

Choose the Customize Quick Access Toolbar Option to Design Form in Excel

  • 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.

Add Form Command to Ribbon to Design Form in Excel

  • 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.

Click on Form Icon to Design Form in Excel

  • As a result, the Data Entry Form window would appear.  And there are text boxes according to the headers of the created table.

Designed Data Entry Form Window

  • Now, insert values in every text box according to your requirement.
  • Subsequently, click on the New button.

Fill the Design Form for First Row

  • As a result, the new information is inserted into the first row of the table.

Information in the First Row from Design Form in Excel

  • Now, for clicking the New button, the same form window would appear again and you can insert the next cells’ values.

Input Second Row Input from Designed Form

  • 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.

Two Rows Filled by Designed Form in Excel

  • 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.

Access Data Validation Tool

  • 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.

Data Validation Window to Validate the Comment Column

  • 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.

Designed Data Entry Form in Excel

Note:

This type of form is applicable to tables with headers.

Read More: How to Make a Fillable Form in Excel (5 Suitable Examples)


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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo