Excel Forms for Data Entry

Excel Forms for Data Entry (Table of Contents)

• Data Entry Form in Excel
• Steps for Adding the Form Feature in Excel
• How to Create Data Entry Form in Excel?

Data Entry Form in Excel

In excel, we have a Form command which we can use it for creating Forms in Excel. To activate the forms in excel, go to Option in the File menu, then select Forms from the Customize Ribbon menu list. The form is available under the Commands Not In Ribbon dropdown. Once activated, on selecting the Form from the menu tab, we will get the custom form that will pick all the headers from the sheet as its fields.

Steps for Adding the Forms Feature in Excel

We can add the FORM in excel by customizing the ribbon. In order to add the Form to the Home tab, follow the below steps.

Step 1 – Go to the File menu and Choose Option.

Step 2 – We will get the below dialogue box as shown below. Next, choose the Customize Ribbon.

Step 3 – We will get the customize ribbon dialogue box where we can add or remove the ribbon.

Step 4 – Search for the FORM button, create a new group in the HOME menu.

Step 5 – After creating ‘ New Group ‘ click on ‘ Add ‘

Step 6 – So that form will be added under Main Tabs in New Group as shown below.

We can see that FORM has been added in the new group in the Main menu, as shown in the below screenshot highlighted in yellow color.

How to Create Data Entry Forms in Excel?

The creation of the data entry form in Excel is very simple and easy. Let’s understand how to create a data entry form in excel with some examples.

Example #1

Creating Form in excel

In order to create a proper excel data entry form, we should maintain accurate data in the table so that it will be easier for the end-user to add, view, and edit it.

Let’s see in this example how to create a normal employee data entry form in excel, which is shown below.

Now we have created an employee database with their designation and KRA.

Let’s see now how the excel form is used to add and delete the employee record in the below examples.

Adding New Employee

In this example, we are going to see how to add a new employee record in the existing employee database by following the below steps as follows.

• Click on the Form button on the Home Menu.

• Once we click on Form, we will get the form dialogue box as shown below.

The form dialogue box contains command buttons like New, Delete, Restore, Find Prev, Find Next, Criteria, and Close.

• New: Which will add new records to the database.
• Delete: Delete the specific records of the employee.
• Restore: Restore the old record.
• Find Next: Finds the next record.
• Find Previous: Find the Previous record.
• Criteria: Displays the records based on the employee criteria.
• Close: This will close the form window.
• Scroll Bar: Where we can scroll up and down to see the record fields.

Now let’s add a new EMPLOYEE record by hitting the New Command button.

• Click on the NEW command button, So that it will ask for the employee data.

• Update the fields like DATE, EMP ID, NAME, DESIGNATION, and KRA.

Here we have updated employee records with the specified data, which will be added to the database as per date-wise order. Therefore, new employee records will be added to the database as shown below with new employee records, and we can see the employee count has been increased to 21.

Example #2

Deleting Employee Record

In this example, we will see how to delete the employee’s record. Then, follow the below steps to delete the employee records.

• Click on the selected cell.
• Click the form button so that we will get the form dialogue box which is shown in the below screenshot.

• We can see that the total number of records has been displayed as 21 of 21 records in the FORM window.
• Click on the command button to search which employee data needs to be deleted using the Find Next and Find Previous command button.
• Click on delete.

• Once we click on the DELETE command button, we will get the below msg box shown below; the Form window will confirm whether to permanently delete the data. Click OK.

Here note down that we will delete the employee name VAMSEE KRISHNA, whose EMP ID is 1010, for our reference to check the data is deleted or not.

After deleting the record, we will get the below database as follows, where we can see that the employee count has been decreased to 20, which means that EMP ID 1010 has been deleted successfully.

Example #3

How to Edit and Update Names in excel data entry forms

In this example, we will see how to edit and update the saved employee name database by following the below steps.

Consider the below employee database where it has employee id with their name, designation, and KRA. Assume that we need to edit and update employee designation from the saved employee list database.

We can edit and update the employee designation by following the below procedure.

Step 1 – The first click on the cell which designation name we need to edit, and in this example, select the first designation name MIS- OPERATION, which is shown below.

Step 2 – Click the form button so that we will get the form dialogue box which is shown in the below screenshot.

Step 3 – Now edit the Designation Name as Marketing which is highlighted in yellow color as shown below; after editing the Designation name as MARKETING, click the Close button.

Step 4 – The Designation name will be getting updated as MARKETING, which is highlighted in yellow in the below screenshot.

Example #4

Fetching the record Criteria wise

In this example, we will see how to fetch the records based on the employer’s criteria. For example, assume that management wants to fetch the records of employees who work under the MYNTRA field. In this scenario, “CRITERIA” can be used to fetch the exact records as follows.

We will consider the same employee database where we can see KRA is unique for each employee, which is highlighted in the below screenshot.

To find the criteria wise employee database, follow the below steps:

• First, Click on the specific cell KRA as shown below.

• Click on the form button so that we will get the form dialogue box which is shown below. Now click on the CRITERIA button,

• Once we click on the CRITERIA button, all the fields will become blank, as shown below.

• Place the cursor on the KRA field.
• Type the KRA name as JAVA which is shown in the below screenshot.

• Now hit the enter button.
• Once we hit the enter button, FORM will search for the CRITERIA named JAVA, and it will fetch all the employee records, which are shown in the below screenshot.

As we can see for the criteria name “JAVA,” which is the third record in the database showing 3 of 21 in the form, which is marked in the above screenshot in red color and it fetches all the employee records like date, emp id, name, and designation.

Things to Remember in excel forms

• Maintain a proper table with appropriate records so that it will be easy for the excel data entry form templates.
• Make sure that always select the cell before using the form button, or else excel will throw an error alert message.

Recommended Articles

This has been a guide to Data Entry Form in Excel. Here we discuss how to create Data Entry Form in Excel, practical examples, and a downloadable excel template. You may learn more about excel from the following articles –

1. One-Variable Data Table in Excel
2. Data Filter in Excel
3. Excel Data Formatting
4. Excel Data Visualization