Computer Applications in

Finance

### Exercises

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", B
1 ="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

ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
FV.xlsx
(9k)
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
Attaullah Shah,
Mar 12, 2018, 10:29 PM
Ĉ
tvm.xlsx
(10k)
Attaullah Shah,
Mar 12, 2018, 10:29 PM