2.2 Using Form for Data Entry

Overview

In lesson 2.2, we were able to add a data form icon to a Quick Access Toolbar (QAT) in an Excel document. We’re now set to use the form to create a new data file or change the contents of an existing data file.

Learning Outcomes

By the time you complete this lesson, you should be able to:

• Use a data form to add more records to an Excel table
• Save a new table created with a data form

 

How to Use Form for Data Entry

For a data entry form to work, your first task is to ensure that a few of your records are entered in a worksheet as in figure 1. The resultant worksheet consists of four rows. The first row contains basically the headings that describe each column of data. The other three rows are records of three students.

Our immediate task is retrieve an existing table or worksheet. Otherwise, we create a new worksheet with a few records and then convert it into a table by doing the following tasks:

1. Start Excel.

2. Retrieve mydata in module 1 or re-type the data in figure 1 in the default worksheet (sheet1).

 

 

Now, we convert mydata or the newly created worksheet (figure 1) into an Excel table before we can use the data entry form. To convert a worksheet into an Excel table, do the following tasks:

1. Select a range of cells (A1:G4) in your data file (figure 2).

 

 

2. Select Home tab.

3. Click Format as Table in the Style group (figure 3).

 

 

4. Choose a style for your table (figure 4).

 

 

5. Confirm or set your cell range in the Create Table dialog box (figure 5).

 

 

6. Click OK.

We now have a new Excel table formatted with a style (figure 6).

7. Select File tap

8. Click Save As to save the new table as mytable21.

 

 

We now have an Excel table that we can use data form to do data entry with in future. Next, we go through the following tasks to add more records to a table:

1. Retrieve an old table you want to add note dasta to (e.g., mytable21), see figure 6.

2. Click the Form icon in the Quick Access Toolbar (figure 7).

 

 

Figure 8 shows the data form dialog box with mytable21 in the background window.

 

 

The data form in figure 8 displays the first record of the table. The student with ID 10BBA002 is highlighted in the first textbox. To move from textbox to textbox use the keyboard Tab key or click with the mouse.

3. Click New to enter fields (data) for our new fourth record. A set of 8 empty textboxes (figure 9) appears ready to receive your entries.

 

 

4. Type the ID for the fourth record in the textbox beside Student ID.

4. Press keyboard Tab key or click the next textbox.

5. Type the gender for the fourth record.

6. Complete entry for the remaining fields (data).

7.  To add another record repeat step 3 through step 6.

When you’re done with entry of all your records, save your work by doing the following tasks:

8. Click the Close button or X in the data form.

9. Select File tap.

10. Click Save command to add the new record to the data file.

 

Updating an Excel Data Table

The act of changing fields, records of an Excel table to bring it up to date (usually by adding or deleting entries) is referred to as updating. Any changes you make to a table has to be saved first before we can claim or refer to it as an updated file.

We’ll use the following example to demonstrate how to  use a data entry form to add new data to an existing table.

 

Example

Use a data form to add the following new record to the given data table in figure 10.

• Id: 10BAR006
• Gender: Male
• Status: Regular
• Department: Religions
• Admission Date: 20 Feb 2019
• CA: 25
• Final Exam: 56

 

 

Solution

In this example you’ll add the given record to an an existing Excel data table (figure 10), by doing the following tasks:

1. Start Excel.

2. Retrieve mytable21.

3. Click the Form icon.

Figure 11 represents the data form with mytable21 in the background. Note the position of the cursor in the first textbox beside Student ID.

 

 

4. Type 10BAR006.

5. Press the Tab key or use the mouse to move your cursor to Gender text box.

6. Type Male.

7. Repeat Task 5 to complete the rest of the entries (figure 12).

 

 

9. Click New command or press Enter key to send the fourth record (of 7 fields) to mytable21.

Note that new empty cells appear ready for a new record to be entered. Remember to always close the data form.

11. Click Close command or the X button (figure 11).

 

 

11. Click Save command to add the new record to mytable21.

Figure 12 is sample of the updated file mytable21.

 

▣▣▣