Excel data entry form

Contents
  1. Step-by-step instructions for data entry
  2. About adding a data entry form icon to the Quick Access Toolbar
  3. Find the data entry button
  4. Add Database Field Names
  5. Open the data entry form
  6. Open the data entry form
  7. Add data records with a form
  8. Add data records with a form
  9. Sample items
  10. Add data records using a form (continued)
  11. Using Form Data Tools
  12. Find records with a single field name
  13. Find records with a single field name
  14. Find records with multiple field names

Step-by-step instructions for data entry

Using Excel’s built-in data entry form is a quick and easy way to enter data into an Excel database.

Using the form you can:

  • start a new database table or add new records to an existing one

  • scroll through the data records one by one

  • search for records containing specific information

  • edit or delete individual items as needed

Applies to Excel 2016 and 2013 versions.

About adding a data entry form icon to the Quick Access Toolbar

The data entry form is one of the built-in tools in Excel. To use this, all you need to do is specify the column headings to use in your database, click the form icon, and Excel will do the rest.

However, to make the task more challenging, Microsoft has decided not to include the “Form” icon on the ribbon as of Excel 2007.

The first step to use the data entry form is to add the form icon to the Quick Access Toolbar so that we can use it.

This is a one-time operation. After adding the icon Form remains available on quick access panels

Find the data entry button

Quick Access Toolbar used to store shortcuts to frequently used functions in Excel. You can also add shortcuts to Excel functions here that are not available on the ribbon.

One of those features is the data entry form.

A data form is a quick and easy way to add data to an Excel database table.

However, for some reason, Microsoft has decided not to add the form to any of the ribbon tabs as of Excel 2007.

Following are the steps that show you how to add a form icon to quick access bar

Add a form to the Quick Access Toolbar

  1. Click down arrow in the end quick access panels to open the drop-down menu.

  2. Select Additional assignments in the list to open the dialog box Customize the Quick Access Toolbar

  3. Click down arrow at the end of the line Select teams to open the drop-down menu.

  4. Select All teams in the list to see all available commands in Excel in the left pane.

  5. Scroll through this alphabetical list to find the team Form

  6. Press the button To add between command bars to add a command Form on the Quick Access Toolbar

  7. Click Okay

Knob Form should now be added to Quick Access Toolbar

Add Database Field Names

As mentioned before, we only need to specify the column headings or field names to use in our database to use the data entry form in Excel.

The easiest way to add field names to a form is to type them into cells on a worksheet. You can include up to 32 field names in a form.

Enter the following headings in cells A1 through E1:

Student ID
Last name
Initial
Age
Program

Open the data entry form

Open the data entry form

  1. Click on cell A2 to make it active.

  2. Press shape icon that was previously added to the Quick Access Toolbar.

  3. Clicking the form icon opens an Excel message box that contains a number of options related to adding headings to the form.

  4. Since we’ve already entered the names of the fields we want to use as headers, all we need to do is click Okay in the message box.

  5. A form with all field names should appear on the screen.

Add data records with a form

Add data records with a form

After the data headers are added to the form, adding records to the database is simply entering the data in the correct order in the form fields.

Sample items

Add the following records to the database by entering the data in the form fields next to the appropriate titles. Press the button New after entering the first entry to clear the fields for the second entry.

Student ID : SA267-567
Last name : Jones
Homepage : b.
Age : 21
Program : languages
StudentID : SA267-211
Last name : Williams
Initial :J.
Age : 19
Program : The science

When entering data that is very similar, such as student ID numbers (only the numbers after the dash are different), use the copy and paste function to speed up and simplify data entry.

To add the remaining records to the sample database, use the form to enter the rest of the data shown in the image above in cells A4-E11.

Add data records using a form (continued)

To add the remaining records to the sample database, use the form to enter the rest of the data found in cells A4 – E11 here.

Using Form Data Tools

The biggest problem with the database is maintaining the integrity of the data as the file size increases. It needs:

  • Correcting data errors or updating individual records.

  • Remove outdated or duplicate entries.

The data entry form contains several tools on the right, making it easy to find and correct or delete records from the database.

These tools are:

  • Tests Search previous And Find next – allows you to move forward and backward through the database record by record.

  • Knob remove – used to delete records from the database.

  • Knob To recover – this button can be used to undo changes to the record being edited. Sometimes we make the wrong changes to a post, or even edit the wrong post altogether. If so, the restore button can be used to undo those changes.

  • Remark. knob To recover only works if there is an entry in the form. As soon as you open another record or close the form, the restore button becomes inactive.
  • Knob criteria allows you to search records in the database by specific criteria such as name, age or program. Button preview criteria included in the next step of the tutorial.

Find records with a single field name

Knob criteria allows you to search for records in the database using one or more field names, such as name, age, or program.

Find records with a single field name

  1. Press the button criteria in the form of.

  2. Click a button criteria clears all form fields, but does not delete data from the database.

  3. Click on the field Program and type “Arts” because we want to find all students of the Arts program in the college.

  4. Press the button Find next † The entry for H. Thompson must appear on the form as she is enrolled in the arts program.

  5. Press the button Find next for the second and third time, and the recordings of J. Graham and W. Henderson should appear one after the other, as they are also registered in the Art program.

The next step of this guide provides an example of finding records that match multiple criteria.

Find records with multiple field names

In this example we are looking for all students aged 18 And enrolled in art education at the university of applied sciences. The form should only display records that meet both criteria.

  1. Press the button criteria in the form of.

  2. Click on the field age and enter 18.

  3. Click in the field Program and type Art.

  4. Press the button Find next † The entry for H. Thompson should appear on the form as she is both 18 years old and enrolled in the arts program.

  5. Press the button Find next the second time, and J. Graham’s entry should appear, as he is also 18 years old and enrolled in the arts program.

  6. Press the button Find next a third time, and J. Graham’s entry should still be visible, as there are no other entries that meet both criteria.

The entry for W. Henderson should: not occur in this example because, although he is enrolled in the art school, he is under 18 and therefore does not meet both search criteria.

Leave a Reply

Your email address will not be published.