Assignment: Clean and Analyze HR Data
What This Assignment Is About
You will use AI tools in Microsoft Excel to clean a messy employee dataset and extract meaningful insights from it. This assignment takes you through the complete process of preparing raw HR data for analysis, spotting and fixing common data quality issues, and generating reports that support business decisions.
Scenario
You work as an HR assistant at a growing retail company with 150 employees across five locations. Your manager has asked you to prepare a report on employee turnover, department-wise headcount, and salary distribution. However, the employee data you received from payroll is messy: there are duplicate entries, inconsistent job titles, missing values, and formatting issues. You need to clean this data and analyze it to answer your manager's questions before the end-of-week meeting.
Tasks
Task 1: Identify Data Quality Issues Using AI
Open Microsoft Excel and create a sample employee dataset with at least 20 rows. Include columns for Employee ID, Name, Department, Job Title, Hire Date, Status (Active/Inactive), Location, and Salary. Intentionally introduce at least four types of errors: duplicate employee records, inconsistent department names (e.g., "HR", "Human Resources", "H.R."), missing values in some cells, and inconsistent date formats.
Once your dataset is ready, use the AI assistant in Excel to identify all the data quality problems present in your sheet. Observe what the AI detects and make a note of which issues it flags.
Task 2: Remove Duplicates and Standardize Text Fields
Using the AI assistant in Excel, remove all duplicate employee records from your dataset based on Employee ID. After that, ask the AI to help you standardize the department names so that all variations of the same department are written identically.
Check your dataset after each action to confirm the changes have been applied correctly.
Task 3: Fill Missing Values and Fix Date Formatting
Some of your records have missing values in the Location and Status columns, and the Hire Date column contains dates in multiple formats (e.g., "15-Jan-2020", "01/15/2020", "2020-01-15").
Use the AI assistant to fill in missing Location values based on patterns from other employees in the same department. Then ask the AI to convert all hire dates into a single consistent format of your choice. Compare the dataset before and after these changes and note how many cells were affected.
Task 4: Generate Summary Statistics and Insights
Now that your data is clean, use the AI assistant in Excel to calculate and display the following information in a separate section of your worksheet:
- Total number of active and inactive employees
- Headcount by department
- Average salary by department
- Number of employees hired in each year
Observe how the AI structures this summary. Does it create a table, use formulas, or generate a pivot table? Take note of the method it uses.
Task 5: Create a Turnover Analysis Report
ools available in Excel, create a complete analysis that shows which departments have the highest turnover, what percentage of total employees have left the company, and whether there are any patterns related to hire date or location.Present your findings in a format that would be easy for your manager to understand during the meeting. You decide what calculations, charts, or tables to include.
Task 6: Reflect on Your Process
Write 4 to 5 sentences reflecting on how your approach to working with the data changed from Task 1 to Task 5. Consider questions like: How much direction did you need from the AI at the start versus the end? What did you learn about asking the AI for help with data tasks? How confident do you feel now about cleaning and analyzing real HR data on your own?
What You Practiced
- You used the AI assistant in Excel to detect common data quality issues like duplicates, inconsistent formatting, and missing values in a real-world dataset
- You applied AI-powered tools to clean and standardize messy data, making it ready for analysis
- You generated summary statistics and insights from cleaned HR data using AI assistance
- You combined multiple cleaning and analysis steps to create a complete turnover report without step-by-step instructions
- You reflected on how your confidence and independence grew as you moved from guided tasks to open-ended problem-solving
FAQs on Assignment: Clean and Analyze HR Data
| 1. What is the significance of cleaning HR data? |  |
| 2. What are common methods used for data cleaning in HR analytics? | |
| 3. How does data analysis benefit human resource management? | |
| 4. What tools are typically used for HR data analysis? | |
| 5. What challenges are commonly faced during the HR data cleaning process? | |
