What is the purpose of using a form for data entry?
Watch the Video on Using Data Entry Forms in Excel Show
Below is a detailed written tutorial about Excel Data Entry form in case you prefer reading over watching a video. Excel has many useful features when it comes to data entry. And one such feature is the Data Entry Form. In this tutorial, I will show you what are data entry forms and how to create and use them in Excel.
Why Do You Need to Know About Data Entry Forms?Maybe you don’t! But if data entry is a part of your daily work, I recommend you check out this feature and see how it can help you save time (and make you more efficient). There are two common issues that I have faced (and seen people face) when it comes to data entry in Excel:
A data entry form can help by making the process faster and less error-prone. Before I show you how to create a data entry form in Excel, let me quickly show you what it does. Below is a data set that is typically maintained by the hiring team in an organization. Every time a user has to add a new record, he/she will have to select the cell in the next empty row and then go cell by cell to make the entry for each column. While this is a perfectly fine way of doing it, a more efficient way would be to use a Data Entry Form in Excel. Below is a data entry form that you can use to make entries to this data set. The highlighted fields are where you would enter the data. Once done, hit the Enter key to make the data a part of the table and move on to the next entry. Below is a demo of how it works: As you can see, this is easier than regular data entry as it has everything in a single dialog box. Data Entry Form in ExcelUsing a data entry form in Excel needs a little pre-work. You would notice that there is no option to use a data entry form in Excel (not in any tab in the ribbon). To use it, you will have to first add it to the Quick Access Toolbar (or the ribbon). Adding Data Entry Form Option To Quick Access ToolbarBelow are the steps to add the data entry form option to the Quick Access Toolbar:
The above steps would add the Form icon to the Quick Access Toolbar (as shown below). Once you have it in QAT, you can click any cell in your dataset (in which you want to make the entry) and click on the Form icon. Note: For Data Entry Form to work, your data should be in an Excel Table. If it isn’t already, you’ll have to convert it into an Excel Table (keyboard shortcut – Control + T). Parts of the Data Entry FormA Data Entry Form in Excel has many different buttons (as you can see below). Here is a brief description of what each button is about:
Now let’s go through all the things you can do with a Data Entry form in Excel. Note that you need to convert your data into an Excel Table and select any cell in the table to be able to open the Data Entry form dialog box. If you haven’t selected a cell in the Excel Table, it will show a prompt as shown below: Creating a New EntryBelow are the steps to create a new entry using the Data Entry Form in Excel:
Navigating Through Existing RecordsOne of the benefits of using Data Entry Form is that you can easily navigate and edit the records without ever leaving the dialog box. This can be especially useful if you have a dataset with many columns. This can save you a lot of scrolling and the process of going back and forth. Below are the steps to navigate and edit the records using a data entry form:
You can also use the scroll bar to navigate through entries one-by-one. The above snapshot shows basic navigation where you are going through all the records one after the other. But you can also quickly navigate through all the records based on criteria. For example, if you want to go through all the entries where the status is ‘In-progress’, you can do that using the below steps: Criteria is a very useful feature when you have a huge dataset, and you want to quickly go through those records that meet a given set of criteria. Note that you can use multiple criteria fields to navigate through the data. For example, if you want to go through all the ‘In-progress’ records after 07-08-2018, you can use ‘>07-08-2018’ in the criteria for ‘Date’ field and ‘In-progress’ as the value in the status field. Now when you navigate using Find Prev/Find Next buttons, it will only show records after 07-08-2018 where the status is In-progress. You can also use wildcard characters in criteria. For example, if you have been inconsistent in entering the data and have used variations of a word (such as In progress, in-progress, in progress, and inprogress), then you need to use wildcard characters to get these records. Below are the steps to do this:
This works as an asterisk (*) is a wildcard character that can represent any number of characters in Excel. So if the status contains the ‘progress’, it will be picked up by Find Prev/Find Next buttons no matter what is before it). Deleting a RecordYou can delete records from the Data Entry form itself. This can be useful when you want to find a specific type of records and delete these. Below are the steps to delete a record using Data Entry Form:
While you may feel that this all looks like a lot of work just to enter and navigate through records, it saves a lot of time if you’re working with lots of data and have to do data entry quite often. Restricting Data Entry Based on RulesYou can use data validation in cells to make sure the data entered conforms to a few rules. For example, if you want to make sure that the date column only accepts a date during data entry, you can create a data validation rule to only allow dates. If a user enters a data that is not a date, it will not be allowed and the user will be shown an error. Here is how to create these rules when doing data entry:
Now, if you use the data entry form to enter data in the Date column, and if it isn’t a date, then it will not be allowed. You will see a message as shown below: Similarly, you can use data validation with data entry forms to make sure users don’t end up entering the wrong data. Some examples where you can use this is numbers, text length, dates, etc. Here are a few important things to know about Excel Data Entry Form:
You May Also Like the Following Excel Tutorials:
What is the purpose of using a form?Forms are used to collect the required information in a logical, meaningful fashion for communication and pass to another entity. When you picture what a form is, you can conjure many different types of documents. A purchase order, a survey, a service request, or a tax return might come to mind.
What is the purpose of forms and reports?Forms are used to collect data for the system and reports to deliver information to users. With forms, data can be entered into the database. With dataentered in the database, it is possible to use a query language so as to generate reports about the data.
What is the importance of using forms in viewing records?Forms ensure you're entering the right data in the right location and format. This can help keep your database accurate and consistent.
|