Project Assignment: Excel Proficiency

INSTRUCTIONS: Format Row 1
Freeze and Bold Row 1
2INSTRUCTIONS: Set up conditional formatting
Set up conditional formatting so that the “Status” column (column G) shows:
Yellow for “Confirmed”
Green for “Completed”

INSTRUCTIONS: Create a formula to fill in the missing data in cells D10 & D11
Using the COUNTIF formula in cells D10 & D11
count the number of times each status is used in column G
BonusINSTRUCTIONS: Conditional format the range a2:i7
Set up conditional formatting that differentiates “CONFIRMED” and “COMPLETED”
status and will highlight the entire row of the table
E.g. When G2 is “CONFIRMED,” A2:I2 is highlighted yellow.
Worksheet 2

INSTRUCTIONS: Enter data validation in cells C4:C9
Create data validation in cells C4:C9 (Table A)
so that only numbers between 4:00 and 8:00 display as valid
2INSTRUCTIONS: Set up an IFS formula in E4:E9
Setup an IFS formula so that E4:E9 (Table A) displays the “Cost to Rent”
for each robot based on the “Cost Per Hour” (Table B).
3INSTRUCTIONS: Create a formula to fill in the missing data in cells K11:K12
Create a formula with VLOOKUP to calculate the comission percentage
for Robot 1 and 2. Your formula should refer to Table C.
Worksheet 3
1INSTRUCTIONS: Build onto Pivot Table A
Display the “Hours Rented” row and show totals
Display the SUM of “Cost to Rent” value with a Grand Total
2INSTRUCTIONS: Edit the date format in G4:G14
Using the date format tool, change the dates in G4:G14 to display as mm/dd/yyyy
E.g. 01/01/2030