Create a Data Entry Form in Excel

A Data Entry Form in Excel is a built-in tool that helps users easily add, edit, search, and manage records in a table. Instead of entering data directly into rows and columns, the form provides labeled fields for each column, making data entry faster, more organized, and less error-prone. It is especially useful for managing structured data such as student records, employee details, or inventory lists.

1. Adding the Form Tool to Excel

The Form tool is hidden by default. Add it to the Quick Access Toolbar:

Step 1: Right-click on the Quick Access Toolbar

To add the Data Entry Form option on the Quick Access Toolbar. First, right-click on any of the existing icons in the Quick Access Toolbar. Then, click on Customize Quick Access Toolbar.

Step 2: Select All Commands

In the "Excel Options” box select "All Commands” in Choose commands from. 

Step 3: Select Form and Click Add

Select "Form" from the list of commands then click on "Add>>”, and then click Ok.

Step 4: Preview Form Icon

Now, the Form icon is added on Quick Access Toolbar.

2. Creating a Data Entry Form

Create a form to manage data, such as student records.

Step 1: Preparing our Data

Begin setting up our data in an Excel table. If we're starting from a scratch, type our column headings in the first row. If we're working with an existing dataset, then we can ignore this step.

Step 2: Create or Convert to a Table

we can create our table and enter the data in a new spreadsheet or if working on an already existing spreadsheet then select any cell within our dataset and press "Ctrl +T" together. This shortcut will transform our data into functional table.

Step 3: Create the Form

Now place our cursor anywhere with in the table, and now click on the "Form" button.

We can use the following keys for Navigation

• Tab- get to the next field
• Shift + Tab - get to the previous field.
• Enter - Save the current record and start a new one.

Excel input form buttons consists of multiple buttons as shown below:

3. Adding a New Record in Data Form

This section will discuss the steps to use the data entry form in Excel.

Step 1: Select the Cell and Click on Form Icon

Select any cell from any column and then click the "Form” icon.

Step 2: Click on New in the Sheet1 Box

After that to add a new row click on “New” in the Sheet1 box.

Step 3: Enter Information and Then Click Close

Then, Add Name, Age, and Class, and then click Close.

Step 4: Preview the Row

Now, our row is added.

4. Searching Records

When it comes to exploring our data, the data entry form offers some tools:

Find Next Record

Follow the steps below to find the next occurrence of the required data-

Step 1: Select any Cell and then Click the Form Icon

Select any cell from any column and then click the "Form” icon.

Step 2: Click on Find Next Button in the Sheet1 Box

Find the Find Next Button and Click on "Find Next” in the Sheet1 box.

Find Previous Record

Follow the steps below to find the previous occurrence of the required data:

Step 1: Select any Cell and Click on Form Icon

Select any cell from any column and then click the "Form” icon.

Step 2: Click on Find Prev in the Box

Click on "Find Prev" in the Sheet1 box.

5. Deleting a Row

Follow the steps below to delete a row:

Step 1: Select any Cell and then Click on the Form Icon

Select any cell from any column and then click the "Form” icon.

Step 2: Click on Delete in the Sheet1 Box

Click on "Delete” in the Sheet1 box.

Step 3: Click OK in the Alert Box

Click Ok in the Microsoft Excel Alert box.

Step 4: Preview the Deleted Row

The selected row is deleted.

6. Updating and Restoring Records with Data Entry Form

Follow the steps below to delete a row:

Step 1: Select any Cell and Click on Form Icon

Select any cell from any column and then click the "Form” icon.

Step 2: Click on Criteria

If we come across an entry that is outdated or contains incorrect information, use the Criteria or navigation buttons to find and select the specific record we wish to update.

Step 3: Update the Information and Press Enter

Once we have the record selected, make the necessary corrections to the inaccurate field(s).

Step 4: Save the Changes

After making the updates, simply press the Enter key to save and commit the corrected data to the table

Step 5: Undo Accidental Changes (Before Saving)

If we accidentally make changes but haven't yet pressed Enter, we can revert to the original record by clicking the "Restore" button.

Step 6: Undo Accidental Changes (After Saving)

In case we've already saved our changes by hitting Enter and need to reverse them, press Ctrl + Z to undo the modifications and restore the previous state of the record.

Step 7: Preview the Data

The required data is obtained using the criteria.

To Update the Records follow the same steps as mentioned above but instead of criteria click on Restore Button.

7. Using Data Validation with Data Entry Form

we can set up a Data Validation rule for one or more columns to restrict user input to a specific data type and our rules will be automatically carried over to the data entry form.

Note: If someone tries to enter a value that does not conform to the rule we've applied then it will show we an error alert message.

8. Opening Data Entry Form with VBA

our users might not even know about the Form button's existence, and that's where VBA can be used. It is like giving them a secret map that leads them directly to the form.

Assuming the Current sheet has a table, we need just a single code line to open the form :

Sub OpenDataEntrtyForm()
  ActiveSheet.ShowDataForm
End Sub 

However, there is a crucial caveat- the above code only works if either:

our table begins in A1 or

There is a name "Database" referring to our table( a defined name, not a table name).

Sub OpenDataEntryForm()
 Dim nName As Name
Range("B2").CurrentRegion.Name = "database"
ActiveSheet.ShowDataForm
For Each nName In ActiveWorkbook.Names
   If "database"= nName.Name Then nName.Delete
Next nName
End SubS