Use COUNTIF function to make Excel count words or numbers in any range of cells. Alternatively you can click Sort Filter Filter on the Home tab in the Editing group. When you use a formula to apply conditional formatting the formula is evaluated relative to the active cell in the selection at the time the rule is created.
For example in a workbook with Sheet1 Sheet2 and Sheet3 running left to right the following formula will return 2.
If you want to see all duplicates at a glance use one of the formulas to find duplicates in Excel that better suits your needs. To create a formula with references highlight the cells you want to use then select an empty cell and enter the formula. Formula to Find Function in Excel VBA In regular excel worksheet we simply type shortcut key Ctrl F to find the contents. Because Mary is in row 4 VLOOKUP returns the value from row 4 in column C 22.