Multiple Values

Copper Contributor

I have a vertical column with about 1000 addresses' is there any way to show if any address is mentioned multiple times and number of times it appears?  

2 Replies


1) Select the column.

On the Home tab of the ribbon, click Conditional Formatting > Highlight Cells Rules > Duplicate Values... and then click OK.

Duplicates will be highlighted in red.


2) To count the number of duplicates:

Let's say the addresses are in column A, starting in A2.

Enter the following formula in B2, then fill down:



=LET( addr, UNIQUE( FILTER($A$1:$A$10000, COUNTIF(A1:$A$10000,A1:A10000)>1)
), cou, BYROW( addr, LAMBDA(x, COUNTIF(A1:$A$10000, x) ) ), HSTACK( addr, cou ) )


With Office 365 or Excel 2021 or Excel for the web an alternative could be this formula. In my sample file the addresses are in column A.