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”, B1 =”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() – $A1) <= -1, (TODAY()–$A1) >= -30 )
and apply your desired 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
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
.