Emp_Id DOJ Name Department Pay Rate
E0001 ### CB Naidu Acct 200/hr ₹ 50.00 Acct
E0002 ### Nitish Kumar Admin 220/hr ₹ 48.00 Admin
E0003 ### Kangana MKT 185/hr ₹ 42.00 MKT
E0002 ### Lalu Yadav Sales 230/hr ₹ 55.00 Sales
E0005 ### Manish Singh IT 180/hr ₹ 40.00 IT
1)Assign the suffix for pay columnas '/hr" so that on only entering of value the suffix will be assigned automa
2) Assign INR symbol in rate column with 2 decimal places.
3) Change DOJ format as "Long Date".
4) Enter Department name by using custom list.
5) Insert a command in cell E1 as "Enter pay Hr" and insert a picture in the comment background.
6) Highlight pay column in in Green fill if pay /hr is more than 200, yellow fill if pay /hr is between 175 and 20
7) Now go to E3 cell and change the value as 220, and check if fill colour will update automatically.
8) Go to G1 and type Total Pay, now calculate total pay using suitable function (=pay*rate)
9) In Total Pay column highlight top 3 items.
10) Now insert icon set in Total Pay column instead of highlighting top 3 items.
11) Go to A5 and cange value as E0002, now highlight the duplicate value in Emp_Id field.
12) Copy the name column in a new sheet in cell A1, and tull A1, now type first name and last name in cell B1. Now
13) Go to cell E1 and type 12,then create a linear series by jumping 3 upto 50.
Total Pay 12
₹ 10,000.00 15
₹ 10,560.00 18
₹ 7,770.00 21
₹ 12,650.00 24
₹ 7,200.00 27
30
uffix will be assigned automa 33
36
39
42
ment background. 45
ay /hr is between 175 and 20 48
date automatically.
name and last name in cell B1. Now segregate first name and last name from name field using flash fill.
Emp_Id DOJ Name Department Pay Rate
E0001 5/12/2016 CB Naidu Acct 200hr/ 50
E0002 6/20/2016 Nitish Kumar Admin 200hr/ 48
E0003 7/5/2016 Kangana MKT 200hr/ 42
E0004 8/5/2016 Lalu Yadav Sales 200hr/ 55
E0005 9/3/2016 Manish Singh IT 200hr/ 40
CB Naidu CB Naidu
Nitish Kumar Nitish Kumar
Kangana Kangana Kangana
Lalu Yadav Lalu Yadav
Manish Singh Manish Singh
