Making Data Entry Forms in MS Excel – Computer Diploma Course - Lesson 60

Lesson 60: Making Data Entry Forms in MS Excel

Data entry is the process of entering information into a computer system. Offices, schools, universities, hospitals and businesses use data entry forms to collect and organize information about employees, students, beneficiaries, customers and other records.

Microsoft Excel provides useful features for creating organized data entry worksheets. You can use headings, tables, dropdown lists and Data Validation to make data entry easier and reduce mistakes.

Learning Objectives
  • Understand the meaning of data entry.
  • Create a data entry worksheet in Excel.
  • Prepare employee and student information forms.
  • Use Excel tables to organize records.
  • Create dropdown lists using Data Validation.
  • Apply date and number restrictions.
  • Search, filter and manage entered records.

1. What Is a Data Entry Form?

A data entry form is a structured layout used to collect and enter information into a computer. It provides separate fields for different types of information.

For example, an employee information form may contain:

  • Employee ID
  • Employee Name
  • Father's Name
  • CNIC Number
  • Designation
  • Department
  • Mobile Number
  • Date of Joining
  • District

2. Where Are Data Entry Forms Used?

OrganizationPurpose
Government OfficesEmployee and beneficiary records
SchoolsStudent admission and attendance records
UniversitiesStudent registration information
HospitalsPatient registration records
BusinessesCustomer and sales information
NGOs and ProjectsBeneficiary and survey information

3. Create a Data Entry Worksheet

Open Microsoft Excel and create a blank workbook. Enter the following column headings in Row 1:

A1: Employee ID
B1: Employee Name
C1: Father's Name
D1: CNIC Number
E1: Designation
F1: Department
G1: Mobile Number
H1: Date of Joining
I1: District

Enter the employee information in the rows below the headings.

Employee IDEmployee NameDesignationDepartment
EMP001Ali AhmedComputer OperatorAdministration
EMP002Sara KhanData Entry OperatorIT
EMP003Ahmed RazaAccount AssistantFinance

4. Format the Data Entry Worksheet

  1. Select the heading row.
  2. Make the headings bold.
  3. Apply a suitable background colour.
  4. Add borders to the data range.
  5. Adjust column widths.
  6. Use a clear font and appropriate font size.
  7. Freeze the heading row if the worksheet contains many records.

5. Convert Data into an Excel Table

Excel tables make it easier to organize, filter and manage records.

  1. Select the complete data range, including headings.
  2. Click the Insert tab.
  3. Select Table, or press Ctrl + T on Windows.
  4. Confirm that My table has headers is selected.
  5. Click OK.

Your data is now formatted as an Excel table. You can use the filter arrows in the headings to search and filter records.

Important Tip

Keep one record in each row and one type of information in each column. Avoid merging cells inside the actual data table because merged cells can create problems when sorting and filtering.

6. Create Dropdown Lists in Excel

A dropdown list allows users to select an option instead of typing it manually. This helps maintain consistent information.

For example, you can create a dropdown list for employee departments:

Administration
Finance
IT
Human Resources
Accounts

Steps to create a dropdown list:

  1. Select the cells where users will select a department, such as F2:F100.
  2. Open the Data tab.
  3. Click Data Validation.
  4. Under Allow, select List.
  5. In the Source box, enter:
Administration,Finance,IT,Human Resources,Accounts
  1. Click OK.
  2. Click one of the selected cells to see the dropdown arrow.
  3. Select the required department.

7. Use Data Validation for Dates and Numbers

Data Validation can restrict the type of information entered into a cell.

Example 1: Restrict a date field

  1. Select the Date of Joining cells.
  2. Open Data and choose Data Validation.
  3. Select Date under Allow.
  4. Set the required date range.
  5. Click OK.

Example 2: Restrict numerical entries

  1. Select the required number cells.
  2. Open Data Validation.
  3. Select Whole Number.
  4. Set a minimum and maximum value.
  5. Click OK.

These restrictions help prevent invalid entries, although the data should still be checked for accuracy.

8. Search and Filter Records

When a worksheet contains hundreds or thousands of records, searching and filtering become important.

  • Use the filter arrow to display selected departments.
  • Filter records by district or designation.
  • Sort employee names alphabetically.
  • Use Find to search for a specific name or ID.
  • Use Remove Duplicates carefully when duplicate records need to be checked.

9. Important Excel Formulas for Data Records

These formulas can help manage and analyze data entry records.

PurposeFormula
Count all numeric employee IDs=COUNT(A2:A100)
Count entered employee names=COUNTA(B2:B100)
Count employees in IT=COUNTIF(F2:F100,"IT")
Find a specific employee ID=COUNTIF(A2:A100,"EMP001")

Note: If employee IDs contain letters, COUNT will not count them. Use COUNTA to count non-empty ID cells.

10. Save and Protect Your Data

  • Save your workbook with a meaningful name, such as Employee_Records.xlsx.
  • Keep a backup copy in a safe location.
  • Check spelling and numbers before saving.
  • Restrict access to confidential information.
  • Use worksheet protection when appropriate to prevent accidental changes.
  • Do not share personal information with unauthorized people.

11. Practical Exercise

Create an employee data entry worksheet with the following fields:

Employee ID
Employee Name
Father's Name
CNIC Number
Designation
Department
Mobile Number
Date of Joining
District

Complete these tasks:

  1. Enter information for at least 10 sample employees.
  2. Format the worksheet with headings and borders.
  3. Convert the data into an Excel table.
  4. Create a dropdown list for departments.
  5. Apply date validation to the Date of Joining column.
  6. Filter records by department.
  7. Count the total employee records using COUNTA.
  8. Save the workbook as Employee_Records.xlsx.
Practice Challenge

Create a student admission data entry sheet with Student ID, Student Name, Father's Name, Class, Gender, Mobile Number, Admission Date and District. Add dropdown lists for Class and Gender.


12. Test Your Knowledge – MCQs

Select one answer for each question. Submit the test to see your score, correct answers and explanations.

1. What is the main purpose of a data entry form?

2. Which shortcut creates an Excel table in Windows?

3. Which Excel feature is used to create dropdown lists?

4. What is the benefit of a dropdown list?

5. Which function counts non-empty cells?

6. Which function counts employees in a particular department?

7. What is one advantage of converting data into an Excel table?

8. Which Data Validation setting can restrict entries to dates?

9. Why should important Excel records be backed up?

10. How should confidential employee information be handled?

Submit the test before downloading your PDF report.

Conclusion

Data entry forms make office work more organized and efficient. By using Excel tables, dropdown lists, Data Validation and formulas, students can create useful records for employees, students, beneficiaries and customers.

Next Lesson: Lesson 61 – Preparing Reports in Word and Excel

Learn how to prepare professional office reports, organize information in Excel, present findings in Word and create clear printable reports.

Social Media Point – Free Computer Diploma Course

Previous Post Next Post