Form in Excel (Table of Contents)
- What are Excel Forms?
- Steps for Adding the Form Feature in Excel
- How to Create Data Entry Form in Excel?
What are Excel Forms?
Enter data one by one for tables in excel is a long time procedure and little tedious task. Also if we want to check the row-wise data, sometimes it’s confusing too. For overcoming with this problem, Excel has a very interesting inbuilt feature which is “Excel Forms”.
The forms in Excel provide a facility to create Data Entry Form in Excel. This is the best way to enter data in table format quickly. The best part of this feature is it’s predesigned and flexible.
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 Quick Access Toolbar option available under 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
Enter these headings into the excel column-wise.
- Convert these Column Names/ Headings into a Table and Go to Insert then, click on Table under Tables section. Or Press CTRL+T.
- A box will open for creating a table then, tick the checkbox “My table has headers” and click on OK.
- Now the table looks like the below screenshot.
- Go to File a menu bar will open in the left then, click on options tab available in the left pane.
- A dialog box will open for Excel Options then, Click on Customize Ribbon tab in the left pane.
- 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.
- This will show the following option then, Choose the Commands Not in the Ribbon.
- 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.
- Then click on New Tab Refer below screenshot.
- After clicking on New Tab, it will add New Group under this heading as shown below screenshot denoted by a red box.
- Select New Group (Custom) as shown in the below screenshot and click on Add.
- It will add Forms functionality under this. Refer below screenshot.
- 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.
- While renaming the New Group, it also asks for icon image, select the icon image, if you want and click on OK.
- After renaming, it will look like below screenshot.
- 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?
- Select your table and click on this Form Tab then, click on Form option under Form Group. Refer below screenshot.
- A new dialog box will open as shown below screenshot.
- In the above screenshot, as we can see in left side there are labels which we created earlier as Column Headers of the table. Along these label’s, there are text boxes where we can insert the values/record/data one by one.
- Enter your data in each field. After filling all the fields click on New button to save this record.
- Repeat this process for all the records which you want to enter. After this click on Close button. Now we can see the data in your excel table. See below.
- Now you can use this Excel Form facility for modifying the data into a table.
- We can switch between the records by using Find Prev, Find Next buttons. Refer below screenshot.
- Use the New or Delete button for modifying the records.
- 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 this criteria, follow below steps:
Click on the Criteria Button. Enter the criteria. Here we want to see only coffee product details. Enter product “Coffee” into relevant field and click on the form button
- Now if you click on Find Prev and Find Next, you will be able to see only those records who meet that particular criterion.
- You can edit these criteria anytime by again click on this Criteria button. click on Close button for an exit.
Things to Remember About Data Entry Form in Excel
- You can use only 32 input boxes or field under this Data Entry Form table.
- The width of the input boxes depends on the worksheet column width. So make sure that the column width should be sufficient to show values under the field.
You can download this Data Entry Form Excel template here –Data Entry Form Excel Template
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 –