Excel Data Consolidation and Analysis
Budget: ₹600 – ₹1,500 INR
I have four Excel sheets containing employee attendance data for the entire year, with each sheet representing a quarter (three months). In each sheet, dates are listed in columns and employee names are listed in rows. Attendance statuses include Present, Planned Leave, Unplanned Leave, and other leave-related categories.
I need to consolidate the data from all four sheets into a single dataset and create a summary that shows the total leave utilization for each employee across the full year. The summary should clearly bifurcate leave days into:
Planned Leaves (e.g., annual leave, vacation, pre-approved leave)
Unplanned Leaves (e.g., sick leave, emergency leave, unscheduled absence)
The final output should provide, for each employee:
Employee Name/ID
Total Present Days
Total Planned Leave Days
Total Unplanned Leave Days
Total Leave Days (Planned + Unplanned)
Attendance Percentage (optional)
The solution should be scalable and refreshable so that new quarterly data can be easily incorporated without requiring significant manual effort. Ideally, the consolidated dataset should support the creation of an executive dashboard with filters and visualizations for management reporting, enabling analysis by employee, department, location, month, quarter, and leave type. This dashboard should provide a clear overview of workforce attendance, leave trends, and absenteeism patterns across the organization.
Key Requirements:
- Clean the data:
- Remove duplicates
- Correct data formats
- Handle missing values
- Consolidate the cleaned data
- Calculate annual leave totals for each employee
- Classify leaves into Planned and Unplanned based on leave type codes
- Provide a summary suitable for management reporting
- Assist in dashboard creation
Ideal Skills and Experience:
- Proficiency in Excel, especially with data cleaning and consolidation
- Experience in handling employee attendance data
- Strong analytical skills
- Ability to create management reports and dashboards
Looking for a detail-oriented freelancer who can ensure accuracy and provide a clear summary for management.
I need to consolidate the data from all four sheets into a single dataset and create a summary that shows the total leave utilization for each employee across the full year. The summary should clearly bifurcate leave days into:
Planned Leaves (e.g., annual leave, vacation, pre-approved leave)
Unplanned Leaves (e.g., sick leave, emergency leave, unscheduled absence)
The final output should provide, for each employee:
Employee Name/ID
Total Present Days
Total Planned Leave Days
Total Unplanned Leave Days
Total Leave Days (Planned + Unplanned)
Attendance Percentage (optional)
The solution should be scalable and refreshable so that new quarterly data can be easily incorporated without requiring significant manual effort. Ideally, the consolidated dataset should support the creation of an executive dashboard with filters and visualizations for management reporting, enabling analysis by employee, department, location, month, quarter, and leave type. This dashboard should provide a clear overview of workforce attendance, leave trends, and absenteeism patterns across the organization.
Key Requirements:
- Clean the data:
- Remove duplicates
- Correct data formats
- Handle missing values
- Consolidate the cleaned data
- Calculate annual leave totals for each employee
- Classify leaves into Planned and Unplanned based on leave type codes
- Provide a summary suitable for management reporting
- Assist in dashboard creation
Ideal Skills and Experience:
- Proficiency in Excel, especially with data cleaning and consolidation
- Experience in handling employee attendance data
- Strong analytical skills
- Ability to create management reports and dashboards
Looking for a detail-oriented freelancer who can ensure accuracy and provide a clear summary for management.