The conditional formatting rule in column G highlights cells that contain the value 5, the highest evaluation rating. Padma wants to change the highlighting to use colors that are
Padma wants to quickly identify the contractors who have been working with Veritate the longest. Sort the data in the Contractors table first in ascending order by the Start Year field and then in ascending order by the Last field.
Padma knows the Contractors table contains a duplicate record. In the range A5:A53, create a conditional formatting Highlight Cells Rule that identifies duplicate cell values by formatting them with Light Red Fill with Dark Red Text. Delete the second instance of the duplicate record, the one with a Start Year of 2022.
The conditional formatting rule in column G highlights cells that contain the value 5, the highest evaluation rating. Padma wants to change the highlighting to use colors that are associated with positive values. Edit the conditional formatting Highlight Cells Rule for the range G5:G52 to highlight the cells containing values equal to 5 with a font color of Teal, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette) and a fill color of Teal, Accent 2, Lighter 60% (6th column, 3rd row in the Theme Colors palette).
Padma wants to list the number of years each contractor has been working for Veritate. Insert a column to the right of the Eval Rating column. Use Years as the column heading. In cell H5, insert a formula without a function that uses a structured reference to subtract the Start Year from 2022, the current year. If Excel does not automatically copy the formula to the other cells in column H, fill the range H6:H52 with the formula in cell H5. Clear the conditional formatting rule from the range H5:H52 if Excel applies it.
Click on Start Year dropdown and select Smallest to Largest. Also, do the same fro Last column.
Select A5 through A53. In the home tab, select Conditional Formatting from styles group. Select Highlight Cell Rules and then Duplicate Values. Choose Light Red Fill with Dark Red Text. Delete row 19.
Select G5 through G52. In the home tab, select Conditional Formatting from styles group. Select Highlight Cell Rules and then select Equal to and enter 5. Click on dropdown of 'with' field and select Custom Format. Under Font group, choose Teal, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette). After that go to Fill tab, choose 6th column, 3rd row in the Theme Colors palette. And then click OK.
Right click in the Eval Rating and select Insert > Table Column to the Right. Double click on the Column and enter Years. Click on cell H5, and type =2022-[Start Year] and hit enter. Select Yes to the popup. Select H5 through H52 and go to Conditional Formating ans select Clear Rules > Clear Rules from Selected Cells.
=2022-[Start Year]and hit enter. Select Yes to the popup. Select H5 through H52 and go to Conditional Formating ans select Clear Rules > Clear Rules from Selected Cells.Video Instruction: