Excel Forms

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.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Forms (wallstreetmojo.com)

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
  • Quantity
  • Price
  • Amount

Below are the steps for adding the form feature in excel –

  1. Enter these headings into the excel column-wise.

    Form Step 1

  2. Convert these Column Names/ Headings into a Table and Go to Insert; then, click on Table under the Tables section. Or press CTRL+T.

    Form Step 2

  3. A box will open for creating a table then, tick the checkbox “My table has headers” and click on OK.

    Form Step 3

  4. Now, the table looks like the below screenshot.

    Form Step 4

  5. Go to File; a menu bar will open in the left then, click on the options tab available in the left pane.

    Form Step 5

  6. A dialog box will open for Excel Options then, Click on the Customize Ribbon tab in the left pane.

    Form Step 6

  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.

    Form Step 7

  8. This will show the following option then, Choose the Commands Not in the Ribbon.

    Form Step 8

  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.

    Form Step 9

  10. Then click on New Tab Refer below screenshot.

    Form Step 10

  11. After clicking on New Tab, it will add New Group under this heading, as shown below screenshot denoted by a red box.

    Form Step 11

  12. Select the New Group (Custom) as shown in the below screenshot and click on Add.

    Form Step 12

  13. It will add Forms functionality under this. Refer to the below screenshot.

    Form Step 13

  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 to the below screenshot.

    Step 14

  15. While renaming the New Group, it also asks for an icon image, selects the icon image, if you want, and click on OK.

    Step 15

    After renaming, it will look like the below screenshot.

    Step 16

  16. Exit from the Excel Options dialog box

    Now you can see there is a new tab added in the Ribbon “FormTab.” Refer to the below screenshot.

    Form tab

How to Create Data Entry Form in Excel?

You can download this Data Entry Form Excel Template here – Data Entry Form Excel Template
  • Step 1: Select your table and click on this Form Tab then, click on the Form option under Form Group. Refer to the below screenshot.
Form Example 1
  • Step 2: A new dialog box will open, as shown below, screenshot.
Example 1-1
  • 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.
Example 1-2
Example 1-3
  • 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.
Example 1-4
  • 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 to the below screenshot.
Example 1-5
  • Step 8: Use the New or Delete button for modifying the records.
Example 1-6
  • 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
Form Example 1-7
  • 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.
Form Example 1-8
  • 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

Recommended Articles

This has been a guide to Data Entry Form in Excel. Here we discuss how to create a 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *