💼 Practical Excel for Office & MIS Work
Attendance Sheets, Salary Sheets, Beneficiary Databases and Professional Reporting
```
1. 📅 Attendance Sheet in Excel
Excel can be used to create a simple and effective attendance system for employees, staff, trainees, students or field teams.
Recommended Attendance Columns
| Employee ID | Name | Department | Date | Status |
|---|---|---|---|---|
| EMP001 | Employee 1 | MIS | 01-Sep-2026 | Present |
| EMP002 | Employee 2 | Admin | 01-Sep-2026 | Absent |
| EMP003 | Employee 3 | Finance | 01-Sep-2026 | Leave |
Count Present Days
```
=COUNTIF(E2:E31,"Present")
```
Count Absent Days
```
=COUNTIF(E2:E31,"Absent")
```
Attendance Percentage
```
=COUNTIF(E2:E31,"Present")/COUNTA(E2:E31)
```
Format the result as a percentage to display the attendance rate.
Tip:
Use a dropdown list for Status with options such as
Present, Absent, Leave, Holiday. This reduces
spelling errors and makes reporting easier.
```
```
2. 💰 Salary Sheet in Excel
Excel can be used to prepare monthly salary calculations, allowances, deductions and net salary records.
Recommended Salary Sheet
| Employee ID | Name | Basic Salary | Allowance | Deduction | Net Salary |
|---|---|---|---|---|---|
| EMP001 | Employee 1 | 50000 | 5000 | 2000 | 53000 |
| EMP002 | Employee 2 | 60000 | 7000 | 3000 | 64000 |
Calculate Gross Salary
```
=C2+D2
```
Calculate Net Salary
```
=C2+D2-E2
```
Calculate Total Salary
```
=SUM(F2:F100)
```
Salary Status with IF
```
=IF(F2>0,"Payable","Check")
```
Tip:
Keep salary records protected because payroll information may
contain confidential employee information. Use appropriate
access controls and avoid sharing sensitive payroll files
unnecessarily.
```
```
3. 👥 Beneficiary Database in Excel
A structured Excel database can help organize beneficiary information for registration, verification, monitoring and reporting.
Recommended Database Structure
| Beneficiary ID | Name | District | UC | Status | Amount |
|---|---|---|---|---|---|
| BEN001 | Beneficiary 1 | District A | UC-01 | Verified | 6000 |
| BEN002 | Beneficiary 2 | District B | UC-02 | Pending | 6000 |
| BEN003 | Beneficiary 3 | District A | UC-03 | Verified | 6000 |
Count Verified Beneficiaries
```
=COUNTIF(E2:E1000,"Verified")
```
Count Pending Beneficiaries
```
=COUNTIF(E2:E1000,"Pending")
```
Total Amount for Verified Beneficiaries
```
=SUMIF(E2:E1000,"Verified",F2:F1000)
```
Find Beneficiary Name Using XLOOKUP
```
=XLOOKUP(A2,A:A,B:B,"Not Found")
```
Find Beneficiary Name Using VLOOKUP
```
=VLOOKUP(A2,A:F,2,FALSE)
```
Database Tip:
Give every beneficiary a unique ID. Avoid using names as the
primary identifier because different people can have the same
name.
Data Protection:
Beneficiary databases can contain personal information. Store
and share such data only according to the applicable
organizational privacy and data-protection requirements.
```
```
```
4. 📈 Excel Reporting & MIS
Excel is widely used for preparing monitoring reports, management summaries, project reports and MIS dashboards.
Example Monthly MIS Summary
10,000
Total Records
8,500
Verified
1,500
Pending
85%
Verification Rate
Useful Reporting Formulas
Total Records:
```
=COUNTA(A2:A10001)
```
Total Verified:
```
=COUNTIF(E2:E10001,"Verified")
```
Total Pending:
```
=COUNTIF(E2:E10001,"Pending")
```
Verification Rate:
```
=COUNTIF(E2:E10001,"Verified")/COUNTA(A2:A10001)
```
Total Financial Amount:
```
=SUM(F2:F10001)
```
Useful Reporting Tools
| Excel Tool | Reporting Use |
|---|---|
| IF | Create status and decision indicators |
| COUNTIF | Count records by status |
| SUMIF | Calculate conditional totals |
| XLOOKUP | Retrieve related information |
| VLOOKUP | Lookup information in existing workbooks |
| Pivot Table | Summarize large datasets |
| Charts | Visualize trends and comparisons |
| Power Query | Clean and combine data |
| Dashboard | Present KPIs and management information |
```
5. 🔄 Professional MIS Excel Workflow
Step 1 — Collect:
Gather source data from forms, field teams, systems or other
Excel/CSV files.
Step 2 — Validate:
Check IDs, dates, names, duplicate records and missing values.
Step 3 — Clean:
Use Power Query or Excel tools to clean and standardize the data.
Step 4 — Analyze:
Use formulas such as IF, COUNTIF, SUMIF, XLOOKUP and VLOOKUP.
Step 5 — Summarize:
Use Pivot Tables to summarize the dataset.
Step 6 — Visualize:
Create charts, KPIs and dashboards.
Step 7 — Report:
Prepare a clear monthly, weekly or project-level report.
Best Practice:
Keep Raw Data → Clean Data → Calculations → Pivot Tables
→ Dashboard/Report logically separated. This makes
your workbook easier to update and audit.
```
```
✅ Excel Reporting Checklist
- ✔ Check duplicate records.
- ✔ Check missing IDs.
- ✔ Check spelling and categories.
- ✔ Check dates and number formats.
- ✔ Check formulas.
- ✔ Compare totals with the source data.
- ✔ Refresh Pivot Tables and Power Query results.
- ✔ Check filters before finalizing the report.
- ✔ Review hidden rows and columns.
- ✔ Save a backup copy of important workbooks.
- ✔ Protect confidential information appropriately.
Social Media Point Excel Learning:
These practical examples provide a foundation for using Excel
in attendance management, payroll preparation, beneficiary
databases, MIS, monitoring, data analysis and management
reporting.
```
