What are Excel Forms?
Form in Excel is a hidden feature in Excel and it’s not available in Ribbon Tools. We need to add this feature by using the Quick Access Toolbar option available under the FILE tab. The Excel Forms allow you to view, add, edit and delete one record at a time in a horizontal orientation which makes data entry easier.
Before adding the Excel Forms facility in Excel, certain points need to be remembered or steps need to be done:
- You need a table to enter data or at least column headers of the data.
Steps for Adding the Form Feature in Excel
Let’s assume we have the following column headers or fields in which we need to enter the data.
- Product ID
- Product Name
- Step 1: Enter these headings into the excel column-wise.
- Step 2: Convert these Column Names/ Headings into a Table and Go to Insert then, click on Table under Tables section. Or press CTRL+T.
- Step 3: A box will open for creating a table then, tick the checkbox “My table has headers” and click on OK.
- Step 4: Now the table looks like the below screenshot.
- Step 5: Go to File a menu bar will open in the left then, click on the options tab available in the left pane.
- Step 6: A dialog box will open for Excel Options then, Click on the Customize Ribbon tab in the left pane.
- Step 7: This will open a dialog box for adding the Ribbons/Commands which are not available at an initial stage then, click on Choose commands from Dropbox as shown below screenshot.
- Step 8: This will show the following option then, Choose the Commands Not in the Ribbon.
- Step 9: It will display a lot of options under this then, Scroll the bar in the above-shown box and click on Form Refer below screenshot.
- Step 10: Then click on New Tab Refer below screenshot.
- Step 11: After clicking on New Tab, it will add New Group under this heading as shown below screenshot denoted by a red box.
- Step 12: Select the New Group (Custom) as shown in the below screenshot and click on Add.
- Step 13: It will add Forms functionality under this. Refer below screenshot.
- Step 14: We can Rename the New Tab and New Group by using Rename It easy to understand from a future perspective. Like we replace this New word with Form. Refer below screenshot.
- Step 15: While renaming the New Group, it also asks for icon image, selects the icon image, if you want and click on OK.
- After renaming, it will look like the below screenshot.
- Step 16: Exit from the Excel Options dialog box.
Now you can see there is a new tab added in the Ribbon “FormTab”. Refer below screenshot.
How to Create Data Entry Form in Excel?
- Step 1: Select your table and click on this Form Tab then, click on Form option under Form Group. Refer below screenshot.
- Step 2: A new dialog box will open as shown below screenshot.
- Step 3: In the above screenshot, as we can see on the left side there are labels which we created earlier as Column Headers of the table. Along with these labels, there are text boxes where we can insert the values/records/data one by one.
- Step 4: Enter your data in each field. After filling all the fields click on the New button to save this record.
- Step 5: Repeat this process for all the records which you want to enter. After this click on the Close button. Now we can see the data in your excel table. See below.
- Step 6: Now you can use this Form facility for modifying the data into a table.
- Step 7: We can switch between the records by using Find Prev, Find Next buttons. Refer below screenshot.
- Step 8: Use the New or Delete button for modifying the records.
- Step 9: This Form feature provides very important functionality which is Criteria. If you want to see only those records in the table which meets certain criteria/condition, Use this Criteria button. For Example, in the above data, we want to see only Coffee product details. For these criteria, follow the below steps:
- Step 10: Click on the Criteria button. Enter the criteria. Here we want to see only coffee product details. Enter product “Coffee” into the relevant field and click on the form button
- Step 11: Now if you click on Find Prev and Find Next, you will be able to see only those records who meet that particular criterion.
- Step 12: You can edit these criteria anytime by again click on this Criteria button. Click on the Close button for an exit.
Things to Remember
- You can use only 32 input boxes or fields under this Data Entry Form table.
- The width of the input boxes depends on the worksheet column width in excel. So make sure that the column width should be sufficient to show values under the field.
This has been a guide to Data Entry Form in Excel. Here we discuss how to create data entry form in excel along with practical examples and data entry form excel template. You may learn more about excel from the following articles –