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.
- 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?
| Organization | Purpose |
|---|---|
| Government Offices | Employee and beneficiary records |
| Schools | Student admission and attendance records |
| Universities | Student registration information |
| Hospitals | Patient registration records |
| Businesses | Customer and sales information |
| NGOs and Projects | Beneficiary 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 ID | Employee Name | Designation | Department |
|---|---|---|---|
| EMP001 | Ali Ahmed | Computer Operator | Administration |
| EMP002 | Sara Khan | Data Entry Operator | IT |
| EMP003 | Ahmed Raza | Account Assistant | Finance |
4. Format the Data Entry Worksheet
- Select the heading row.
- Make the headings bold.
- Apply a suitable background colour.
- Add borders to the data range.
- Adjust column widths.
- Use a clear font and appropriate font size.
- 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.
- Select the complete data range, including headings.
- Click the Insert tab.
- Select Table, or press Ctrl + T on Windows.
- Confirm that My table has headers is selected.
- 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.
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:
- Select the cells where users will select a department, such as F2:F100.
- Open the Data tab.
- Click Data Validation.
- Under Allow, select List.
- In the Source box, enter:
Administration,Finance,IT,Human Resources,Accounts
- Click OK.
- Click one of the selected cells to see the dropdown arrow.
- 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
- Select the Date of Joining cells.
- Open Data and choose Data Validation.
- Select Date under Allow.
- Set the required date range.
- Click OK.
Example 2: Restrict numerical entries
- Select the required number cells.
- Open Data Validation.
- Select Whole Number.
- Set a minimum and maximum value.
- 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.
| Purpose | Formula |
|---|---|
| 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:
- Enter information for at least 10 sample employees.
- Format the worksheet with headings and borders.
- Convert the data into an Excel table.
- Create a dropdown list for departments.
- Apply date validation to the Date of Joining column.
- Filter records by department.
- Count the total employee records using COUNTA.
- Save the workbook as Employee_Records.xlsx.
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.
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.
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
