How To Create Data Entry Form In Excel – Ms Office

Data Entry Form in Excel 1

What is data?

Data are units of information, often numeric, that are collected through observation. In a more technical sense, data are a set of values of qualitative or quantitative variables about one or more persons or objects, while a datum is a single value of a single variable. 

Data is defined as facts or figures, or information that’s stored in or used by a computer. An example of data is information collected for a research paper. An example of data is an email.

Data is defined as facts or figures, or information that’s stored in or used by a computer.

An example of data is information collected for a research paper.

An example of data is an email.noun13327Facts that can be analyzed or used in an effort to gain knowledge or make decisions; information.pluralNoun5114Statistics or other information represented in a form suitable for processing by computer.pluralNoun5020Facts or figures to be processed; evidence, records, statistics, etc. from which conclusions can be inferred; information.pluralNoun4712Information in a form suitable for storing and processing by a computer.

Data Entry Form

A data entry form is often used to input data, which has previously been collected, into a database.

There are some common controls within a data entry form that include:

> Text Box : Allows a single text line to be entered
> Text Area: Allows multiple lines of text to be entered
> List Box: Allows you to pick one or more items from a list
> Combo Box: Allow you to pick a single item from a list
> Radio button: Always in a group of at least two, but often more. radio buttons allow you to make a single selection from a number of options.
> Check box: A small rectangular box that allows you to select an option.
> Submit Button: Allows you to send the data. Often some ‘validation’ checks are made before sending in order to make sure the data format is valid and within range.

What is the purpose of using a form for data entry?

data form provides a convenient way to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen.

HOW TO CREATE DATA ENTRY FORM IN MS EXCEL

Data Entry Form in Excel

Using 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 Toolbar

Below are the steps to add the data entry form option to the Quick Access Toolbar:

  1. Right-click on any of the existing icons in the Quick Access Toolbar.
  2. Click on ‘Customize Quick Access Toolbar’.Customize the Quick Access Toolbar option
  3. In the ‘Excel Options’ dialog box that opens, select the ‘All Commands’ option from the drop-down.Select All Commands from the drop down
  4.  Scroll down the list of commands and select ‘Form’.Select Form option in the Excel Options dialog box
  5. Click on the ‘Add’ button.Click on the Add button
  6. Click OK.

The above steps would add the Form icon to the Quick Access Toolbar  (as shown below).

Excel Data Entry Form icon in QAT

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 Form

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

Buttons of the Entry Form in Excel
  • New: This will clear any existing data in the form and allows you to create a new record.
  • Delete: This will allow you to delete an existing record. For example, if I hit the Delete key in the above example, it will delete the record for Mike Banes.
  • Restore: If you’re editing an existing entry, you can restore the previous data in the form (if you haven’t clicked New or hit Enter).
  • Find Prev: This will find the previous entry.
  • Find Next: This will find the next entry.
  • Criteria: This allows you to find specific records. For example, if I am looking for all the records, where the candidate was Hired, I need to click the Criteria button, enter ‘Hired’ in the Status field and then use the find buttons. Example of this is covered later in this tutorial.
  • Close: This will close the form.
  • Scroll Bar: You can use the scroll bar to go through the records.

Now let’s go through all the things you can do with a Data Entry form in Excel.https://a5153d00a507717095ce0ac72f93e366.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

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:

Table Not Selected Prompt

Creating a New Entry

Below are the steps to create a new entry using the Data Entry Form in Excel:

  1. Select any cell in the Excel Table.
  2. Click on the Form icon in the Quick Access Toolbar.Excel Data Entry Form icon in QAT
  3. Enter the data in the form fields.
  4. Hit the Enter key (or click the New button) to enter the record in the table and get a blank form for next record.
Data Entry Form in Excel - Demo

Navigating Through Existing Records

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

  1. Select any cell in the Excel Table.
  2. Click on the Form icon in the Quick Access Toolbar.
  3. To go to the next entry, click on the ‘Find Next’ button and to go to the previous entry, click the ‘Find Prev’ button.Navigating uing the Data Entry Form in Excel
  4. To edit an entry, simply make the change and hit enter. In case you want to revert to the original entry (if you haven’t hit the enter key), click the ‘Restore’ button.

You can also use the scroll bar to navigate through entries one-by-one.

Using Scroll Bar to Navigate

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:

  • Select any cell in the Excel table.
  • Click on the Form icon in the Quick Access Toolbar.
  • In the Data Entry Form dialog box, click the Criteria button.Criteria Button in Excel Data Entry Form
  • In the Status field, enter ‘In-progress’. Note that this value is not case sensitive. So even if you enter IN-PROGRESS, it would still work.Specify the criteria in the field
  • Use the Find Prev/Find Next buttons to navigate through the entries where the status is In-Progress.

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.

Two criteria in data entry form

You can also use wilcard characters 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:

  1. Select any cell in the Excel table.
  2. Click on the Form icon in the Quick Access Toolbar.
  3. Click the Criteria button.
  4. In the Status field, enter *progress
  5. Use the Find Prev/Find Next buttons to navigate through the entries where the status is In-Progress.

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 Record

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

  1. Select any cell in the Excel table.
  2. Click on the Form icon in the Quick Access Toolbar.
  3. Navigate to the record you want to delete
  4. Click the Delete button.Delete a record in Excel

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 Rules

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

  1. Select the cells (or even the entire column) where you want to create a data validation rule. In this example, I have selected column A.
  2. Click the Data tab.Data Tab in the Ribbon
  3. Click the Data Validation option.Data Validation Option in the Ribbon
  4. In the ‘Data Validation’ dialog box, within the ‘Settings’ tab, select ‘Date’ from the ‘Allow’ drop down.Date Option in Data Validation Drop Down
  5. Specify the start and the end date. Entries within this date range would be valid and rest all would be denied.Start and End Date in Data Validation
  6. Click OK.

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:

Data Validation Error when doing data entry with a form in Excel

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:

  1. You can use wildcard characters while navigating through the records (through criteria option).
  2. You need to have an Excel table to be able to use the Data Entry Form. Also, you need to have a cell selected in it to use the form. There is one exception to this though. If you have a named range with the name ‘Database’, then the Excel Form will also refer to this named range, even if you have an Excel table.
  3. The field width in the Data Entry form is dependent on the column width of the data. If your column width is too narrow, the same would be reflected in the form.
  4. You can also insert bullet points  in the data entry form. To do this, use the keyboard shortcut ALT + 7 or ALT + 9 from your numeric keypad.

Hits: 8

Related Post

Leave a Reply

Your email address will not be published.