Excel SAM Module 6 Pt. 2

6. Go to the Proposed Projects worksheet, which lists projects that were proposed in 2021. Dean suspects the ProposedProjects table has a duplicate record. Identify the duplicate as follows:

a. Clear the filter from the ProposedProjects table to display all the records.

b. In the range A2:A14, create a conditional formatting Highlight Cells Rule that displays cells with duplicate values using Light Red Fill and Dark Red Text.

c. Delete the second instance of the duplicate record so that you can summarize the data accurately.

7. The city of Honu Point wants to fast-track mixed-use development projects that use loans for funding. Add a column to the ProposedProjects table, and determine which projects meet the criteria as follows:

a. In cell G1, type Fast Track as the column heading.

b. In cell G2, enter a formula using the AND function that includes structured references to display TRUE if a project has a [@[Project Type]] of "Mixed Use" and a [@[Funding Type]] of "Loan". Fill the range G3:G13 with the formula in cell G2 if Excel does not do so automatically.

8. Add a Total Row to the ProposedProjects table, which automatically counts the number of Fast Track values. Using the total row, display the sum of the funding amounts.

9. Dean asks you to identify the projects that require 120 days or more to complete, those that require 60 days or more to complete, and those that require less than 60 days to complete.

a. In the Number of Days column (range C2:C13), create a new Icon Set conditional formatting rule using the 3 Signs icons.

b. Reverse the icon order.

c. Display the red diamond icon in cells with a Number type value greater than or equal to 120.

d. Display the yellow triangle icon in cells with a Number type value greater than or equal to 60.

e. Display the green circle icon in cells with a Number type value less than 60.

10. Dean also wants to compare the funding amounts visually. In the Funding column (range F2:F13), create a new Data Bars conditional formatting rule using Orange Gradient Fill data bars.

11. Wrap the text in cell J1 to display the complete contents of the cell.