How to Generate a Data Entry form in Excel

Using the “Form” command, you can easily generate a data entry form in Excel. Data entry forms provide a convenient means to insert, view, update or delete data. This post will show you how to generate Excel data entry forms and why and when to use them.

Note: The examples in this post have been created with Microsoft 365.

Content

When you should use Excel Data Entry Forms

  • This form is useful when you have more column which require you to scroll horizontally to add data.
  • This form does not contain advance form features such as dropdown list, radio buttons, list boxes etc… So, this is usefull when you do not need such options
  • This form can be use to add data to maximum of 32 columns. So, it is usefull when the number of columns are 32 or below.
  • You should have a basic data table. The first row is the column titles and should not have merged cells/ columns.

Why you should use Excel Data Entry Forms

  • Data entry form contains a single row of data at a time. So it is not conflicting with other rows. So, it will prevent accidental alteration in other rows.
  • It provide a convenient means to enter row of information to a table without scrolling horizontally.
  • The Excel forms allow you to add, eddit, find, display, and delete data easily.
  • You can display one complete row seperately from others.
  • It helps you to avoid most of the mistakes in data entry works.

How to generate a Data Entry Form in Excel

The default installation of Microsoft Excel does not add the Form feature to the main interface. So, you need to add this Form feature to the Ribbon or Quick access toolbar.

Where is the Excel Form command and how to add form command to Excel Quick Access Toolbar

First, you need to add the Form option to the Ribbon or Quick access toolbar. To do that,

  • Go to File > Options > Quick Access Toolbar
  • In “Choose command from” dropdown list, select “Commands Not in the Ribbon
  • Now, in the command list box scroll down and double-click on the “Form…” command. Then, you can see the Form command is added to the box in the Right side.
  • Click OK.
  • Now you can see the Form comand (form button) has added to the Quick Access Toolbar.

Video: Where is the Excel Form command and how to add form command to Excel Quick Access Toolbar

Create a table to insert data

To generate the Form, you should create the data table first. In your table, the top row should be the column headings. You should not have merged cells in your table.

Convert your data table to an Excel table. This is not necessary, but Excel can easily identify the table if you convert your data table to an Excel table.

To convert your table to an Excel table,

  • Select the data table
  • Press Ctrl+T and it will open up the Create Table dialog box (You can also use Insert > Table option).
  • Check the checkbox named “My table has headers
  • Click OK

Video: Create a table to insert data

You may also be interested in reading: 10 Benefits of Converting Data into Excel Tables.

Insert Excel Data Entry Form

After you have created the table, you are ready to generate the form.

  • Select a cell somewhere in the middle of your table
  • Click the Form button you added previusly in the Quick Access Toolbar and you will see the Data Entry Form popped up.

Whenever you need to open the Data Table, you should follow the above two steps.

Video: Insert Excel Data Entry Form

How to use Excel Data Entry Form

As explained above, you can easily generate a data entry form without knowledge of VBA. The excel automatically generates text box form controls for each column in your table.

Data Entry form in Excel
Excel Data Entry Form

This form provides 7 buttons, namely “New”, “Delete”, “Restore”, “Find Prev”, “Find Next”, “Criteria”, and “Close” to interact with the data table and records.

The functions of the buttons are briefly explained below.

You can use the Tab key to navigate between form controls. This reduces the need to switch from keyboard to mouse or touchpad back and forth unnecessarily. So, use the Tab key to save some time.

Add a new record

Case 01: If you started adding data to a brand new data table and your form fields are empty, just type the data and hit the Enter key to add a new record. This will, add the data to the table and clear the form fields allowing you to add the next record.

Case 02: If you are in the middle of viewing data or have some data in the form field, click the “New” button to clear the form without making any changes to existing data. Then you can start adding new records, as explained above.

Video: Add a new record using Excel data entry form

View records

In Excel forms, you can navigate through existing data in the table using the “Find Next,” “Find Prev“, and “Criteria” buttons. You can view one record at a time.

Using “Find Next” and “Find Prev” buttons

  • Click anywhere in the table and open the form using the form command you added to the Quick Access Toolbar. Then, the form will open populated with the first record.
  • Then you can use the “Find Next” button to move to the next record. Then the form will populated with the next record in the table.
  • Similarly you use the “Find Prev” button to populate the form with the record just above the current record.

Using the Critera button

Using this button, you can filter the data based on some criteria. Then the form will be populated with only the records that match the provided criteria.

To do this,

  • Click the Criteria button.
  • Then type the criteria in the relevent text box as explained below and press Enter key.
    • For example, if you want to select only the sales managers you “Sales Manager” in the designation field.
    • And also, if you want to retrive only the “Sales Managers” from IT field, you should type “Sales Manager” in the designation text box and “IT” in the Category text box.
  • Then, use the “Find Next” and “Find Prev” button to navigate through the search results. The form will contains only the records matching the provided criteria.
  • Once you click the Criteria button, the button will switch to Form button. When you click the Form button, it will clear the criteria and button will switch to the Criteria button.

Video: View records using Excel data entry form

Update a record

You can also use the Excel Data Entry form to update existing records. To update a record,

  • Populate the form with the record to be updated. You can use the record viving methods explanied above to find the correct record.
  • Edit the record and hit Enter key to apply the changes to data table.

Important !: You can also apply the changes by pressing the New, Criteria, and Close buttons too. If you press these buttons with unwanted changes, those changes will apply to the data table.

So, if you made any changes to existing records and do not want to apply those changes to the table, press the Restore button before pressing the “New”, “Criteria”, or “Close” buttons.

Video: Update records using Excel data entry form

Delete a record

To delete a record,

  • Populate the form with the record to be deleted. You can use the record viving methods explanied above to find the correct record.
  • Once the form loaded with the correct data, klick the Delete button.
  • A dialog box will pop up to get your confirmation for the action, click OK.

Video: Delete records using Excel data entry form

Data validation

By creating a data entry form, we expect to reduce the errors in data entry. Data validation plays a crucial role in reducing data entry errors.

The Excel data entry form we created above does not have data validations. However, you can still add data validation rules to the data table. If the user accidentally added invalid data, the Excel throws an error message instead of inserting the invalid data. That way, you can prevent adding invalid data through the form.

To add data validation to the data Excel table,

  • Select table column you need to add the validation. (Hover over the heading of the correct column until you see a black down arrow. Click it once the down arrow is appeared. It will select entire column excep the column heading.)
  • Then, go to Data > Data Validation in the “Data Tools” group
  • In the Data Validation dialog box,
    • In Settings tab, you can set the validation rules
    • In the Error Alert tab, add the error message and title of the erro.
  • Click OK.

Wrapping Up

The Data Entry Form is a very old feature in Microsoft Excel. However, Microsoft has removed it from the popular commands. So, most of the users are unaware of this feature.

However, the Excel Data Form can still help you increase productivity in data entry works. And also provides a compact view of the records, making it easy to view and navigate through the records. In this post, we have discussed when, why and, how you should use the Excel data entry form. The small video demonstrations may help you to understand each step easily.

You may also read,

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.