What are Excel Forms?

Forms in Excel are a hidden feature of Excel. It is not available in Ribbon Tools. We need to add this feature 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 performed:

  • First, we need a table to insert data or at least column headers of the data.

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

Steps for Adding the Form Feature in Excel

Let us assume we have the following column headers or fields in which we need to enter the data:

  • Product IDProduct NameQuantityPriceAmount

How to Create a Data Entry Form in Excel?

  • Step 1: First, we must select the table and click on this “Form Tab,” then click on the “Form” option under the “Form” Group. Refer to the 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 that 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: We must enter the data in each field. After filling all the fields, click on the “New” button to save this record.

  • Step 5: We can repeat this process for all the records we want to enter. After this, click on the “Close” button. Now, we can see the data in the Excel table. See below.

  • Step 6: Now, we can use this “Form” facility for modifying the data into a table.Step 7: We can switch between the records using the “Find Prev” and “Find Next” buttons. Refer to the below screenshot.

  • Step 8: We can also use the “New” or “Delete” button to modify the records.

  • Step 9: This “Form” feature provides important functionality, “Criteria.“ If we want to see only those records in the table which meet certain criteria/conditions, use this “Criteria” button.For example, we want to see only “Coffee” product details in the above data. For these criteria, we must follow the below steps:Step 10: Click on the “Criteria” button. Enter the criteria. Here, we want to see only “Coffee” product details. Therefore, we must enter the product “Coffee” into the relevant field and click on the “Form” button.

  • Step 11: Now, if we click on “Find Prev” and “Find Next,” we can see only those records that meet that particular criterion.

  • Step 12: We can edit these criteria anytime by clicking on this “Criteria” button again. Finally, click on the “Close” button for an exit.

Things to Remember

  • We 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 excelColumn Width In ExcelA user can set the width of a column in an excel worksheet between 0 and 255, where one character width equals one unit. The column width for a new excel sheet is 8.43 characters, which is equal to 64 pixels.read more. So, we must ensure that the column width is sufficient to show values under the field.

This article is a guide to Data Entry Form in Excel. Here, we discuss creating a data entry form in Excel, practical examples, and a data entry form Excel template. You may learn more about Excel from the following articles: –

  • We must first enter these headings into the Excel column-wise. After that, we need to convert these “Column Names/ Headings” into a table and go to “Insert.” Then, click on “Table” under the “Tables “section. We may also press “CTRL+T.” Consequently, a dialog box may open for creating a table. Then, tick the checkbox “My table has headers” and click on “OK.” Now, the table may look like the below screenshot. Next, we need to go to “File.” A menu bar will open on the left. Then, click on the “Options” tab available in the left pane. A dialog box will open for “Excel Options.” Then, click on the “Customize Ribbon” tab in the left pane. As a result, it will open a dialog box for adding the ribbons/commands, which are not available at the initial stage. Then, click on “Choose commands from” dropbox as shown below. Now, it will show the following option: “Choose the Commands Not in the Ribbon.” It will display many options under this. Then, scroll the bar in the above-shown box and click on “Form.” Then, refer to the below screenshot. Then click on “New Tab,” as shown in the below screenshot. After clicking on “New Tab,” it will add “New Group” under this heading, as shown below screenshot denoted by a red box. We need to select next to the “New Group (Custom)” as shown in the below screenshot and click on “Add.” Consequently, it will add “Form” functionality under this. For example, refer to the below screenshot. We can rename the “New Tab (Custom)” and “New Group (Custom)” by using “Rename.” It is easy to understand from a future perspective. Like we replace this new word with form, refer to the below screenshot. While renaming the “New Group (Custom), it may also ask for an icon image. We can select the icon image and click on “OK.” After renaming, it may look like the below screenshot. Now, exit from the “Excel Options” dialog box. We can see a new tab added in the ribbon “Form Tab.” Refer to the below screenshot.

After renaming, it may look like the below screenshot.

We can see a new tab added in the ribbon “Form Tab.” Refer to the below screenshot.

  • Remove Duplicates from Column in ExcelOne Variable Data Table in ExcelRefresh the Pivot TableDynamic Tables in ExcelMaximum Number of Rows in Excel