Highlight Duplicates Except First Number in Range in Microsoft Excel 2010

In Excel, we can make values appear in a specific range by using Conditional Formatting or Formulas. Conditional formatting is a built in feature of Excel while formulas are flexible enough to fulfil our requirement.

We can use the "COUNTIF"function to return the output.

Countif: Counts the number of cells within a range that meets the condition.

Syntax: =COUNTIF(range,criteria)

range: It refers to the range of selected cells from which the function will check the number of items that have found.

criteria:  The condition which defines / specifies which cells to count.


Let us take an example:

We have some random numbers in column A & few of them are duplicate.

  • We need to generate a formula and use conditional formatting so that we can apply colors to values that are duplicate provided that for the first time the number appears in the range the cell color should remain same.
  • The formula in cell B2 would be =COUNTIF($A$2:A2,A2)>1


  • Copying the formula in rest of the cells in column B, we will get the result as below.


  • In the above shown screenshot, you can see that the first value (cell A2) in the selected range is returning FALSE.The next time it’s repeated, the formula returns TRUE.
  • If we apply conditional formatting to the values in range A, we can visually figure out the difference.
  • Select the range A2:A7.
  • Click on Home
  • In the Styles group, click on Conditional Formatting
  • Click on New Rule


  • The following dialog box will appear -


  • Select “Use a formula to determine which cells to format”. 
  • Enter the formula as =COUNTIF($A$2:A2,A2)>1 in Format values where this formula is true box as shown below:


  • Click on Format, select Fill tab & select color


  • In Preview, you can see the color that will be applied to the selected range of cells.
  • Click on OK.


  • Thus, to conclude, you can see that with the use of conditional formatting, it is easier to determine which cells are duplicate.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube