1.
Format the Salary column so that values show in Indian currency
with commas (e.g., ₹40,063).
Soln:
Select the Salary column.
Right-click → Format Cells → Category: Custom
₹#,##0
Click ok
2. Format the Hire date to display as MMM-YYYY (e.g., Sep-2000).
Soln :
Select the Hire date column.
Right-click → Format Cells → Category: Custom.
mmm-yyyy
Click ok
3. Display Emp_id in the format EMP-03928 instead of E03928.
4. Highlight employees older than 60 by changing their Age font color
to red.
Soln :
Select the Age column.
Home → Conditional Formatting -> Highlight Cell rules ->
Greater Than 60
5. Show Salary with text "USD" (e.g., 40,063 USD).
Soln :
Select the Salary column.
Right-click → Format Cells → Category: Custom.
#,##0 "USD"
Click ok
6. Sort employees by Department alphabetically, and within each
department, sort by Salary (highest to lowest).
Soln :
Click any cell in the data table.
Go to Data → Sort.
In Sort dialog:
Sort by: Department → Sort On: Values → Order: A → Z.
Click Add Level.
Then by: Salary → Sort On: Values → Order: Largest to
Smallest.
Click OK
7. Filter only employees in the IT department earning more than
₹50,000.
Select header row → Data → Advanced Filter.
Department Salary
IT >50000
8. Display only Female employees from United States.
Soln: Data tab -> Advanced filter
Gender Country
Female United States
9. Show
the 5 most recent hires.
Soln
Click the Hire date filter → Date Filters → Top 10
Change Top to 5 and choose Items (Top 5 Items by date).
Click OK
10. Find employees with Designation containing the word
“Analyst”.
Soln :
Turn on filters.
Click the Designation filter → Text Filters → Contains...
Analyst
Click ok
11. Restrict Age column so only values between 18 and 65 are
allowed.
Select Age data cells
Data → Data Validation → Settings:
o Allow: Whole number
o Data: between
o Minimum: 18
o Maximum: 65
Click OK.
12. Allow only specific Departments (IT, HR, Marketing, Finance).
Create a short list somewhere with allowed departments: IT,
HR, Marketing, Finance.
Select Department cells.
Data → Data Validation:
Allow: List
Source: select the list range (or enter IT,HR,Marketing,Finance
directly).
Click OK.
13. Ensure Hire date cannot be a future date.
Select Hire date cells.
Data → Data Validation:
Allow: Date
Data: less than or equal to
End date: =TODAY()
Click OK
14. In Gender column, allow only "Male" or "Female".
Soln:
Select Gender cells.
Data → Data Validation:
Allow: List
Source: Male,Female
Click OK.
15. In Salary, restrict entry to be greater than ₹20,000.
Soln: Select Salary cells.
Data → Data Validation:
Allow: Decimal (or Whole number)
Data: greater than
Minimum: 20000
Click OK.
16. Remove duplicate employee records based on Emp_id.
Soln:
Select the entire table (or any cell inside it).
Data → Remove Duplicates.
In the dialog, check only the Emp_id column (uncheck others).
Click OK.
17. Group data by Department so you can collapse/expand
employees under each department.
Sort the data by department
Select each department row-wise
Click on Data -> Outline -> Group
18. Show subtotal of average Salary by Department.
Sort the department column from A-Z
Select the table and click on Data-> Outline->Subtotal
At each change in Department
Use Function Average
Add Subtotal Salary
Click ok
19. Show total number of employees in each Country.
Sort the table from A-Z
Select the table and click on Data-> Outline->Subtotal
At each change in Emp_id
Use Function Count
Add Subtotal Country
Click ok
20. Create a subtotal report of total Salary by Gender.
Soln:
Sort the Gender
Select the table and click on Data-> Outline->Subtotal
At each change in Gender
Use Function total
Add Subtotal Salary
Click ok