Conditional Formatting Lab Exercises.docx

Conditional Formatting Lab-1 1) Open the ‘CF-1 from the Excel Labs folder on the desktop. 2) Calculate the entries of Percentage Column using the formula: Percentage = Achieved / Target 3) Format the Percentage column entries to Percentage Format. 4) Apply Conditional Formatting, so that: 

anyone with 100% or above gets a Green Light



anyone between 80% and 100% Success Rate gets an Amber Light



anyone below this gets a Red Light.

Note: Your answer should resemble the diagram shown below:

5) When you have finished, save your file as Stay out of the Red Light District.

Conditional Formatting Lab-2 1) Open the ‘CF-2’ from the Excel Labs folder on the desktop. 2) Apply conditional formatting rules, so that: 

Anyone achieving more than 100% of their target is shown in green color.



Anyone achieving less than 100% of their target is shown in red color.



Anyone achieving exactly 100% of their target is shown in yellow color.

Note: Your answer should resemble the diagram shown below:

3) Save your file with the name ‘Formatted Salespeople’.

Conditional Formatting Lab-3 1) Open the ‘CF-3’ from the Excel Labs folder on the desktop. 2) Apply custom conditional formatting to the data, so that each row is formatted, dependent upon the "Design" Category. Each different category should have a specific color scheme. Note: Your answer should resemble the diagram shown below:

3) Save your file with the name ‘Colorful Rollercoasters’.

Conditional Formatting Lab-4 1) Open the ‘CF-4’ from the Excel Labs folder on the desktop. 2) Format the column entries of column C to Long Date format. 3) Use a function to calculate today's date in the highlighted cell. This will ensure that this spreadsheet will always reflect the current date. 4) Use whichever method you prefer to calculate the remaining dates, based upon today's. Note: After calculating the dates, it should look like the table shown below on the left:

5) Apply conditional formatting to highlight This Week / Last Week / Next Week in different colors. Note: Your answer should resemble the diagram shown above on the right.

6) Save your file with the name ‘Dates Formatted’.

Conditional Formatting Lab-5 1) Open the ‘CF-5’ from the Excel Labs folder on the desktop.