best training excel intermediate - Duplications

Hi John

there are a number of ways. you can use the countif formula to count the number of times each data appears in a named range.

=sumif(range,criteria)

so you want to put (in cell B1) =sumif(A:A,A1) to find the number of times the data in cell A1 appears in column A. Drag this formula down and then you can find the occasions where cell Bx is greater than 1

you could use conditional formatting to do this.

Format > conditional formatting

select 'cell value is greater than' and type 1 in the box next to it. Then edit the format how you wish, eg a red background for any occurences of where this is true.

Alternatively you could give auto filter a go once you've done your sumif. to do this, highlight all cells in the sheet by clicking the box to the left of the A column header and above the 1 column header, select Data > filter > auto filter then you can select anything that is not a 1 in the sumif column.

Hope this helps?

Paul