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
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
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()
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”,
B1 =”milk”)
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
)
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
)
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”, B
1 =”milk”)
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()  – \$A1) <= -1, (TODAY()\$A1) >= -30 )

formatting

Explanation:
When we deduct actual date from today’s date, we might get some positive and some negative values, where positive values show older dates and negative values show coming dates. We have to exclude older dates, and current date. Therefore, we use a condition that the difference in dates is in the range of -1 and -30, i.e tomorrow till 30 days ahead

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