In this article we’ll learn how to create forms in excel for data entry. We’ll also look at what else can you do with excel data entry forms like creating new entry, deleting a record from excel data entry form and much more.

What are Data Entry Forms in excel & their use?

If data entry is a part of your job, the data entry option in excel makes your life so much easier by saving your time & effort.

Generally people feel two main challenges when it comes to data entry in excel:

1. Human error: As you’re entering data every day, there is a chance that you might make a mistake and make a wrong data entry.
2. Time Consuming: If you have multiple columns then you would have to scroll right to different columns to enter data. It’s a very tedious & time consuming process.

Here a proper data entry form can help solve both these challenges. Before we create an Excel data entry form let’s see how one looks like.

We have a following data set where we maintain records of every new employee when they join.

Once we implement a data entry form, every time you want to add another entry it’ll show you an Excel data entry dialog box like this.

The highlighted fields are the ones where you would have to enter the data, once done hit Enter and excel will add the data entry in the sheet.

Adding Data Entry Form Option in Excel

First we need to add the option to the Quick Access Toolbar or Ribbon so that we can use it.

1. Right click on ribbon or Quick Access Toolbar. Select Customize Quick Access Toolbar.
   
2. In the Excel Options dialog box, select All Commands from drop-down.
   
3. Scroll down and find Form option and select it. Then click Add
   
4. Click OK. You can now see the Form option on Quick Access Toolbar.

Do Note: To create a data entry form in excel the data should be in form of an Excel Table. If not, you’ll have to convert it first. You can just select the data and press CTRL + T to create a table.

So now that we have learned how to create a form in excel, let’s look at what other things can we do with it.

Creating a new Excel Data Entry

1. Select the new cell where you want to add a new entry. Select the Form icon on Quick Access Toolbar.
   
2. The data entry dialog box will appear. Chances are you would see any already existing entry in the dialog box, click New first and then Add the data in the Excel form and hit Enter or click New button.
   
3. This is how you would create an excel form for data entry.
   

Do remember first convert the data into an Excel table otherwise you’ll not be able to create fillable form in Excel.

Different options in Excel Form

An Excel Form for data entry has many options in the dialog box as you just saw. Let’s see what each one does:

1. New: This creates a new data entry.
2. Delete: This is used to delete an existing data entry. First navigate using Find Prev and Find Next to select a data entry then press Delete.
3. Restore: This is used to restore previous data in the form if you didn’t click New or pressed Enter.
4. Find Prev: This is used to navigate to previous entry.
5. Find Next: This is used to navigate to the next data entry.
6. Criteria: This helps us to find specific records. If I want to find the entries of all sales from North region then I’ll click Criteria, enter North in status field and the use the find buttons to navigate. We’ll cover this is more depth in the article.
7. Close: This closes the Excel form.
8. Scroll Bar: You can use this to scroll through different entries or records.

Navigating Through Records in Excel Data Entry Form

You can use the navigate buttons in Excel Form dialog box to quickly go through different entries. This would save a lot of time to scroll if your dataset has a lot of columns.

1. Select any cell in the table. Click Form icon on the Ribbon.
2. This will open the dialog box. Now click on Find Next to go to next entry and use Find Prev to go back to previous entry.
   
3. If you found the entry you’re looking for. You can edit any details if you wish and hit Enter. It’ll update the entry. Click Restore if after updating you want to go back to the previous version of the updated entry.

You can also use the scroll bar to navigate to different entries.

Using Criteria in Excel Data Entry Form

As mentioned earlier, let’s deep dive into the Criteria option. Let’s say I want to get employee details of employees whose location is New York.

1. Select any cell in the table. Click Form icon on the Ribbon. This will open the dialog box.
2. Click Criteria, in Location data filed type ‘New York’ and hit Enter. The fields are not case-sensitive so even if you enter ‘new york’ it’ll work.
   
3. Now you’ll see only those entries where region is New York. Use Find Next/Find Prev to navigate between those entries.
   
4. You can also use multiple criteria. Let’s say we want to employees who work in sales and are based out of Chicago. Then click Criteria enter Sales in Designation data field and type Chicago in Location data field. Hit Enter. Remember the designation should start with the word ‘Sales’ if the first word of the designation is not ‘Sales’ then it’ll ignore those entries.
   

Deleting Record in Data Entry Form Excel

You can either delete an entry by just deleting the row of the table or you can also delete it via the Excel form dialog box.

1. Select any cell in the table. Click Form icon on the Ribbon. This will open the dialog box.
2. Navigate to find the entry you want to delete and click Delete button.
   

Data Validation Rules on Data Entry Form Excel

You can use data validation to make sure the data entered in the form meets a certain requirement. If it doesn’t it’ll not be accepted.

For example we can make a simple data validation rule to only allow numbers in the Employee ID column. If a user enters a data which is not a number in the Employee ID column then it’ll show an error.

1. Select the column where we want to add a data validation rule.
2. Go to Data tab. Select Data Validation option.
   
3. In the dialog box, within Settings select Whole Number from the Allow drop-down option.
   
4. Now you can give a minimum & maximum value as well as go to Error Alert to create a custom error message. Hit Ok.
   

Now if you go and make a new entry and type a wrong month or word which doesn’t match any month, excel will through this error.

You can use this method to create multiple data validation rules for different data fields/columns.

Important pointers to create a form in Excel

1. You can use wildcard characters via Criteria option.
2. To create a form in excel make sure your data is converted into an Excel Table.
3. The Field width within Excel form dialog box depends upon your column width. If you change the width of the column it’ll get reflected in the Field width.
4. You can insert bullet points in the data entry form. Use shortcut ALT + 7 or ALT + 9 from your numeric keypad to add bullet points.

Conclusion

That was all about how to create a form in excel.

I hope this article was helpful to you and explained you what is a data entry in excel and how to create fillable form in excel.

If you have any questions or tips, I’d love to hear them in the comment section below.