Those not familiar with the Data Entry Form feature in Excel often relies on Excel VBA user form to create a user-friendly visually engaging data entry form in Excel. In this tutorial, you will learn how to create data entry forms in Excel without using VBA or Coding. I would also teach you how to activate the Excel built-in Form, how to input data, update, search and delete data from a spreadsheet all from the Data Entry Form tool in Excel without writing a single line of code. Excited?

Requirements and availability

Data entry forms are supported in Excel 365, Excel 2019, Excel 2016, Excel 2010 and Excel 2007, but not available on Excel Online (Excel for the web). 

What is the Excel Data Entry Form?

Excel offers the ability to make data entry easy by using a user-friendly form that has the full create, read, update and delete (CRUD) functionality that can be very tasking to create from scratch in VBA. In addition, the form also has a search function for existing entries.

Here is how to create data entry form in Excel without using VBA or Coding:

How to add the Data Form tool to Excel

Though not visible on the main menu tabs, the Data Entry Form tool already exists in Excel and can be accessed easily from the Quick Access Toolbar. 

Below are the straightforward steps to create data entry form in Excel without using VBA or Coding

1. Click on the small down-pointing arrowhead at the far right of the Quick Access Toolbar, and then choose More Commands in the pop-up menu.

2. In the Excel Options dialog box that comes up, under “Choose commands from” change the selection from “Popular commands” to “All commands”.
3. Scroll down the list of commands till you find Form, then select it.
4. Click the Add button in the middle of the Quick Access Toolbox, this makes the tool visible and accessible on your Quick Access Toolbar.
5. Click on the OK button to save all changes.

Notice the form icon will appear on your Quick Access Toolbar and will be available in all your Excel windows.

How to create an entry form in Excel

The data entry form can only be used when your table is formatted as an Excel Table.

1. Select the entire table in your sheet having a dataset and convert it to a Table by using the shortcut key Ctrl + T or selecting Format as Table on the Home menu.

2. After converting your data to a Table, click on the Form icon on the top left Quick Access Toolbox and the entry form will come out.

• New: This allows you to create new records.
• Delete: This allows you to delete an existing record from your table.
• Restore: Use to restore data in the previous form.
• Find Prev: This finds previous records.
• Find Next: This finds the next record.
• Criteria: Use to find specific records in your data table.
• Close: To close the entry form.

3. Fill all the empty fields in the data form and click on the New button, this will automatically add the record to the table and get a blank form for the next record.

How to search for records

• Criteria button: This button is used to find specific records in your data table, by entering a specific value in any of the empty cells in the form.

• Find Next: Next step is to put the value you want to find into one of the empty cells and click on the Find Next button.

Automatically, all the values for the word MD appear and fill in the empty cells in the form.

How to delete from records

The first step in deleting a record from an entry form is to first click on the criteria button.

Then you type in the value you want to delete by clicking on the Find Next button.

Lastly, you can delete an entry from your table by clicking on the delete button. A confirmation window appears. Click the OK button to delete.

How to close your entry form

Closing the entry form is usually the last step after all data entry processes are done. This is usually done by clicking on the Close button icon on your form.

Check out our YouTube channel for more information.