Scenario: You have a column E named "Priority". You have to highlight the rows with different colors based on whether it is "Urgent", "High" or "Normal".
1) Select the entire range of rows for which you want to apply specific background colors. If you have 1000 rows and say 10 columns of data, then select this entire range of cells.
2) In the Home tab, click on Conditional Formatting item in the Styles panel
3) Select the last option Manage Rules
4) This brings up the Conditional Formatting Rules Manager.
5) Click on the New Rule button
6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".
7) In the textbox that says Format values where this formula is true, type this formula:
=EXACT($E2,"Urgent")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells
9) Move to the last tab Fill, select a color that you want to mark as the background color and then click on OK
10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "Urgent".
11) To specify a background color for rows that contains the text value "High", repeat steps 3 to 9, this time specifying a different background color.
12) Repeat this procedure for other criteria (text value "Normal") and finally you have entire rows conditionally formatted.
Courtesy: http://www26.brinkster.com/mvark/codegallery/conditionally_format_entire_row.aspx
1) Select the entire range of rows for which you want to apply specific background colors. If you have 1000 rows and say 10 columns of data, then select this entire range of cells.
2) In the Home tab, click on Conditional Formatting item in the Styles panel
3) Select the last option Manage Rules
4) This brings up the Conditional Formatting Rules Manager.
5) Click on the New Rule button
6) This opens up a dialog box titled New Formatting Rule. Select "Use a formula to determine which cells to format".
7) In the textbox that says Format values where this formula is true, type this formula:
=EXACT($E2,"Urgent")
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
8) Click on the Format button on the same dialog box. This opens a another dialog box Format Cells
9) Move to the last tab Fill, select a color that you want to mark as the background color and then click on OK
10) This takes us back to the original Conditional Formatting Rules Manager dialog box. When you click on the OK button, the background color is applied to 2 rows that contains the text value "Urgent".
11) To specify a background color for rows that contains the text value "High", repeat steps 3 to 9, this time specifying a different background color.
12) Repeat this procedure for other criteria (text value "Normal") and finally you have entire rows conditionally formatted.
Courtesy: http://www26.brinkster.com/mvark/codegallery/conditionally_format_entire_row.aspx
Conditional formatting is a useful feature provided by Excel. Thanks for sharing how to make most of it. The official guides of Office suite at www.Office.Com/Setup covers all this and comes handy. Amazing blog.
ReplyDelete