Conditional formatting in Excel

1. Format cells greater than 500

Ans: Home > Conditional Formatting > Highlight Cell Rules > Greater Than

2. format cells less than 500

Ans: Home > Conditional Formatting > Highlight Cell Rules > Less Than

3. Format cells above average

Ans: Home > Conditional Formatting > Top  / Bottom Rules >Above Average

4. Format top 10%

Ans: Home > Conditional Formatting > Top  / Bottom Rules >Top 10% 

5. Format first cell if name appear three times in the data

Ans: Assume that our data appears in range A1:A10, then 

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=COUNTIF($A$1:$A$10, $A1)>2

and apply your desired formatting

6. Format data row if the year column contains 2007

Let us assume that our years are listed in column A

Select the data range > 

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=$A1=2007

and apply your desired formatting

7. Format those dates which are older than today

Ans: Assume that our data appears in range A1:A10, then 

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=$A1 > TODAY()

and apply your desired formatting

8. Format cells if south or milk

Ans: Assume that our data appears in range A1:B10, 

Select the data range

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=OR(A1 ="south", A1 ="milk")

and apply your desired formatting

9. Format all odd values , use function isodd()

Ans: Assume that our data appears in range A1:B10, 

Select the data range

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=ISODD(A1)

and apply your desired formatting

10. Format all numbers , use isnumber()

Ans: Assume that our data appears in range A1:B10, 

Select the data range

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=ISNUMBER(A1)

and apply your desired formatting

11. format if south and milk

Ans: Assume that our data appears in range A1:B10, 

Select the data range

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=AND(A1 ="south", B1 ="milk")

and apply your desired formatting

12. Format dates within the next 30 days

Ans: Assume that our data appears in range A1:A10, then 

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=AND((TODAY()-$A3)<=-1, (TODAY()-$A3)>=-30)

and apply your desired formatting

13. format if Column B is different from Column C, 

Ans: Assume that our data appears in Columns B and C then 

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=$B1<>$C1 

and apply your desired formatting

14. Format if store south and milk revenue is less than 1800

 Ans: Assume that store data is given in Column A, milk in Column B,  and revenues data given in C, 

Select the data range

Home > Conditional Formatting > New Rule > Use a Formula ... > 

=AND(A1 ="south", B1="milk", C1> 1800)

and apply your desired formatting

.