|   Excel's conditional formatting is generally used to identify values in particular ranges, but we can hack it to identify duplicated data within a list or table . People frequently have to identify duplicated data within a list or table. Doing this manually can be very time-consuming and error-prone . To make this job much easier, you can hack one of Excel's standard features, conditional formatting. Take, for example, a table of data with a range of $A$1:$H$100. Select the top-left cell, A1, and drag it over and down to H100. It is important that A1 be the active cell in your selection, so dragging from H100 to A1 isn't quite the same. Select Format  Conditional Formatting... and, in the Conditional Formatting dialog box, select Formula Is from the top-left pop-up menu. In the field to its right, enter the following code: =COUNTIF($A:$H0,A1)>1 Click the Format tab (that's the Format button under Mac OS X), followed by the Patterns tab, and select a color you want applied to visually identify duplicate data. Click OK to return to the Conditional Formatting dialog box and click OK again to apply the formatting. All those cells containing duplicate data should be lit up like a Christmas tree in the color you chose, making it much easier to eyeball duplicate data and delete, move, or alter it as appropriate. It is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute, as is your table of data, $A$1:$H$100. By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion. By this we mean that the conditional formatting formula in cell A1 will read as follows : =COUNTIF($A:$H0,A1)>1 while in cell A2, it will read: =COUNTIF($A:$H0,A2)>1 in cell A3, it will read: =COUNTIF($A:$H0,A3)>1 and so forth. If you need to identify data that appears two or more times, you can use conditional formatting with three different conditions and color-code each condition for visual identification. To do this, select cell A1 (the cell in the top lefthand corner of table) and drag it down to H100. Again, it is important that A1 is the active cell in your selection. Now select Format Conditional Formatting... and, from the box containing the text "Cell Value Is", select Formula Is. In the box to the right of Formula Is, enter the following code: =COUNTIF($A:$H0,A1)>3 Click the Format tab and then the Patterns page tab, and select a color you want to apply to identify data that appears more than three times. Click OK, then click Add, and from the Condition 2 box, select Formula Is and enter the following formula: =COUNTIF($A:$H0,A1)=3
Click the Format tab and then the Patterns page tab and select a color you want to apply to identify data that appears three times. Click OK, and then click Add. From the Condition 3 box, select Formula Is and enter the following formula: =COUNTIF($A:$H0,A1)=2 Finally, click the Format tab and then the Patterns page tab. Select a color you want to apply to data that appears twice. Click OK. You will have different cell colors depending on the number of times your data appears within your table of data. Again, it is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute, as is your table of data, $A$1:$H$100. By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion. |
-C to copy, click the Formula box for Condition 2, press Ctrl/ 