How to Create a Data Entry Form in Excel With Examples

How to Make Data Entry Form in Excel

Step 1: Select Any Cell in Your Dataset > Press the Ctrl+T keys together to Convert it to a table.

Step 2: Now Click on the Form Button in the Ribbon

Step 3: Pressing the Form button generates a data entry form for your table, featuring fields corresponding to the Column headings.

In the dynamic world of Microsoft Excel, there's an often-overlooked gem that can revolutionize how you input, manage, and validate data within your spreadsheets - the Excle Data Entry form. Whether you're a novice user, you can unlock the power of data entry forms and streamline your spreadsheet operations.

Excel Data Entry Form

Data Entry Form is a form that helps to enter the data with the help of a form in which the data can be added, searched, and previous data can be deleted. Data entry in Excel without forms can be very time-consuming. There are two major issues faced while conducting data entry without forms in Excel:

1. Time-consuming: Without forms, data needs to be entered in one cell at a time, then go to the next cell and enter the data for that cell, and so on. Sometimes, due to confusion, the user might need to scroll up and see which column is it and what data needs to be entered and then come back to the current position. Similarly, in the case when the user needs to scroll right and then come back to the beginning.
2. Error-prone: If there is a huge dataset that needs 100 entries to be entered in the form. In that case, there may be a possibility that the user unintentionally enters the wrong data in the cell.

Data entry using forms in Excel helps to overcome all these limitations and makes the task of entering data less time-consuming and less error-prone.  In this article, Let's have a look into how to Data Entry Forms in Excel. 

How to Add the Form Tool to Excel

To add the form tool, we need to make it visible as its hidden by default. For this, we need to add it to the ribbon.

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.

How to Make Data Entry Form in Excel

If you've ever struggled with typing data directy into Excel cells, Excel offers a nifty solution called a "Data Entry Form", designed to make entering, managing, and validating data a breeze. You just need to enter your data in a table and and click on form button.Follow the below steps to create a Data Entry form:

Step 1: Prepare your Data

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

Step 2: Create or Convert to a Table

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

Step 3: Create the Form

Now place your cursor anywhere wihtin the table, and now click on the "Form" button.

You 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:

How to Add 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.

How to Search for Records in Data Entry Form

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

Find Next

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.

To Find Previous Data

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.

How To Delete Row in Data Form

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.

How to Update and Restore 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 you come across an entry that is outdated or contains incorrect information, use the Criteria or navigation buttons to find and select the specific record you wish to update.

Step 3: Update the Information and Press Enter

Once you 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 you accidentally make changes but haven't yet pressed Enter, you can revert to the original record by clicking the "Restore" button.

Step 6: Undo Accidental Changes (After Saving)

In case you've already saved your 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 4: Preview the Data

The required data is obtained using the criteria.

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

How to Use Data Validation with Data Entry Form

You can set up a Data Validation rule for one or more columns to restrict user input to a specific data type and your 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 you've applied then it will show you an error alert message.

How to Open Data Entry Form with VBA

Your 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, you 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:

Your table begins in A1 or

There is a name "Database" referring to your table( a defiend 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

You can learn VBA in detail using How to Insert and Run VBA code.

Excel Data Entry Form not Working

If Data form does not work in your Excel, the following troubleshooting tips may be helpful to determine the Exact problem:

Too many fields in the Data form

When you try to craete a data form for a table that contains more than 32 columns, you can face this error.

To sort this error you can reduce the number of columns in the table or insert one more column breaking your big tabke into a few smaller ones.

Cannot Extend List or Database

When you try to add a new row of data, but Excel says that it cannot extend a list or database, that means there is some other data belwo your table and adding a new record would overwrite the existing data.

To solve this error, first remove any data below your table if any, so it can extend downward.