Home > DeveloperSection > Articles > Excel conditional formatting row color

Excel conditional formatting row color


Excel Excel 
Ratings:
0 Comment(s)
 3192  View(s)
Rate this:

Excel conditional formatting row color

Excel provides a very useful feature for set row’s color according condition based. For example user maintains excel sheet of student list based on pass and fail. User wants to set passed student’s row color become green when he entered status as pass and on fail set row color as red. In another example, change row color when date will be expired, etc. Steps are given below.

Step 1: Entered two data into excel sheet as below image. Here I have changed row color based on pass and fail value.

Excel conditional formatting row color

Step 2: Select area where you want to see effect of row color. Below I have selected A1xB2 area.

Excel conditional formatting row color

Step 3: Now select “Conditional Formatting” tag from “Home” menu as following image and select “New Rule…” option.

Excel conditional formatting row color

Step 4: When you selected “New Rule…” option, one popup window will be appears, where you apply various formula as below.

Excel conditional formatting row color

Note:

      1.       Select “Use a formula to determine which cells to format” option for apply custom formula.

      2.       =$B1=”pass” is a formula that set the color of row. Here “B” indicate column index, which used for apply formula and “1” is show active cell index. Active cell index you can get when you select area.

      3.       Set row color and styles using “Format” button.

 

Step 5: When you press “OK” button in step 4, you see effect in your selected area as below image.

Excel conditional formatting row color

Step 6: Follow step 4 for “fail” condition as below image.

Excel conditional formatting row color

Step 7: Insert some new record and check your applied formula and see effect as below image.

Excel conditional formatting row color

 

Note: Below I have given some important formula

Number Based

Greater Than:

=$B1>75

Less Than:

=$B1<56

Date Based

To check if a date is in the last 7 days:

=TODAY()-A1<7

To check if a date is in the current week:

=AND(WEEKNUM(A1)=WEEKNUM(TODAY()), YEAR(A1)=YEAR(TODAY()))

To check if a date is in the current month:

=AND(MONTH(A1)=MONTH(TODAY()), YEAR(A1)=YEAR(TODAY()))

To check if a date is in the last 30 days:

=TODAY()-A1<30

You can easily search formula and apply here.


Don't want to miss updates? Please click the below button!

Follow MindStick