🔹 1.
Data Entry & Structure
📘 Scenario: You're a data entry officer maintaining an employee database.
✅ Steps:
1. Open Excel and type the following:
Employee ID Name Department Join Date Salary
E101 Alice Roy HR 01/04/2022 40000
E102 John Paul Sales 15/03/2022 50000
2. Save the file as employee_data.xlsx.
3. Apply column labels (Employee ID, Name...) clearly in Row 1.
4. Make sure Employee ID is unique for each employee (acts as a Primary Key).
🔹 2. Data Cleaning & Transformation
📘 Scenario: You received messy data from another department.
✅ Steps:
1. Sort employees by Salary (Home → Sort & Filter → Sort Largest to Smallest).
2. Use Remove Duplicates (Data → Remove Duplicates → Select relevant columns).
3. Apply a Filter (Data → Filter) and hide rows with salary < ₹40,000.
4. Use Highlight Cell Rules → Duplicate Values to detect duplicate entries in the Name
column.
🔹 3. Data Import & Handling
📘 Scenario: You get sales data from a .csv file monthly.
✅ Steps:
1. Open Excel → Go to Data → Get External Data → From Text/CSV.
2. Choose sales_march.csv file.
3. In the Text Import Wizard:
o Select Delimited (comma-separated)
o Choose delimiter as , or Tab
o Preview and click Finish
4. File opens in Excel → Save as .xlsx.
🔹 4. Formatting & Presentation
📘 Scenario: You’re preparing the employee sheet for presentation.
✅ Steps:
1. Select header row → Make it Bold, use Fill Color (light gray), and change font.
2. Select entire Salary column → Apply Currency format (₹).
3. Use Format Painter (Home → Format Painter) to copy formatting from one row to
others.
4. Apply Conditional Formatting (Home → Conditional Formatting):
o Data Bars for salary
o Highlight cells greater than ₹45,000 with green fill.
🔹 5. Number & Date Formatting
📘 Scenario: You need to generate a report with clean number formats.
✅ Steps:
1. Date Column → Format as DD-MMM-YYYY (Right-click → Format Cells → Date).
2. Salary Column → Format as Currency (₹, two decimal places).
3. Insert a column for Performance Rating → Format as Fractions.
4. Add a Time column for daily login → Format as HH:MM AM/PM.
🔹 6. Formulas & Auto Functions
📘 Scenario: You want to calculate totals and averages.
✅ Steps:
1. At the bottom of the Salary column, click a cell and use AutoSum → Sum(Salary).
2. Below that, use =AVERAGE(D2:D6) to find average salary.
3. Try formula =IF(Salary>45000, "High", "Normal") to categorize employees.
4. Drag formulas using fill handle to apply to all rows.
🧩 Bonus Challenge
Add a Data Validation drop-down in a new column for Department (HR, Sales, IT).
Create a bar chart showing Salary per Department.
Protect the worksheet allowing edits only in Name and Department columns.
