Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Duplicates


resolvedResolved · Low Priority · Version 365

Geraint has attended:
Excel Advanced course


I have many duplicated rows of references but I just want to count a particular column from these. So in effect I have 30,000 rows but only 12,000 non-duplicated references and I want to find the Count of the values in a certain column. All values inside the specific column will be the same for each duplicated reference but I just cannot figure out how to quickly count them all to know how many non-duplicated references have X as the value or Y. Very annoying when I feel like it should be a very simple task, any advice would be superb!

RE: Duplicates

Hi Geraint,

Thank you for the forum question and for the nice feedback. I am happy that you found the course useful.

I hope, I fully understand what you want to do.

I have done some examples (please find attached Excel file). In the first table I have added conditional formatting to highlight duplicates and added a TOTAL ROW to the dynamic table (you can find this option under the TABLE DESIGN tab when you click inside the dynamic table) and changed the TOTAL row to COUNT. Then the table has been filtered BY COLOUR and the total row will show that there is 11 duplicates.

In the second below you can find Excel functions all highlighted in yellow.

I hope this can be helpful.

If you cannot find the attached file refresh your browser and if you still cannot see it below my reply, please wait 10 minutes and try again. Sometimes it takes some time after it is uploaded.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

count dupl.xlsx

Fri 22 Dec 2023: Automatically marked as resolved.


Excel tip:

Create a unique items table from a duplicating table

1. Ensure that your list has column headings
2. Select the entire list
3. From the menu bar, select DATA, FILTER, ADVANCED FILTER
4. Select "Filter the list, in place", and tick the "Unique Records Only" box
5. Click OK, filtered list appears.

View all Excel hints and tips

Server loaded in 0.07 secs.