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 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 Commands Not In Ribbon dropdown. Once activated, on selecting the Form from the menu tab, we will be getting the custom form which will pick all the headers from the sheet as its fields.
Steps for Adding the Form Feature in Excel
We can add the FORM in excel by customizing the ribbon. In order to add Form in 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.
Now we can see that in the Main menu, FORM has been added in new group as shown in the below screenshot which is highlighted in yellow color.
How to Create Data Entry Form in Excel?
Creation of data entry form in Excel is very simple and easy. Let’s understand how to create 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 button 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 in the database as per date wise order. New employee record will be added in 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. Follow the below steps to delete the employee records.
- Click on the selected cell.
- Click form button so that we will get the form dialogue box which is shown in the below screenshot.
- In FORM window we can see that the total number of records has been displayed as 21 of 21 records.
- Click on the command button to search for which employee data needs to be deleted using 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 which is shown below, Form window will ask for confirmation whether to delete the data permanently or not. Click OK.
Here note down that we are going to delete the employee name called 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 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 – 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 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 – So that Designation name will be getting updated as MARKETING which is highlighted in yellow color 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. Assume that management wants to fetch the records of employees who work under 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
- Maintain 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 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 along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
