conditional formatting using formulas - Microsoft Community

have range of numbers want highlight when range populated duplicate numbers. how do using formula in conditional formatting?

1, select range want apply cf
2. home tab > conditional formatting > new rule > use formula determine cells format , put following formula , choose appropriate fill colour

have prepared wrokbook (download - https://1drv.ms/x/s!akd5y6rujhvhixtfu6jpsxhlub8h)

have got 2 ranges: a1:g20 , i1:n11

1. sheet1 has cf applied on i1:n11 if enter value in i1:n11, cell in i1:n11 red. cf formula used here
=countif($a$1:$g$20,i1)

2. sheet2 has cf applied on a1:g20 if enter value in i1:n11, cell in a1:g20 red. cf formula used here
=countif($i$1:$n$11,a1)

(most important thing in cf reference cell should top left corner, hence i1 in case 1 , a1 in case 2 have been used)

Office / Excel / Windows 10 / Office 365 Home