Are you trying to gain unique values from a large or small list of repeating records? Elimination of duplicates is common question on training, so I put here how to do it in Excel 2007 and newer. Earlier this was possible only by using advanced filter, which allowed the filtering records without duplicates. In Excel 2007 version it easier for us by the Data tab where we find the button that will do it much faster.

Firstly, let us make a sample with which you can encounter: We have a list of all our clients and they are obviously from different countries. We are interested in the list of countries from which we have clients, but we want only unique values. Each country we want to write in the list only once, and not a hundred times for a hundred clients.

Odebrání duplicit excel 2007

Remove duplicates

To remove duplicates in Excel use the Data tab and click Remove duplicates. The exact procedure is as follows:

  1. Copy the list of repeating values including a header (column name) into an empty area
  2. Select the copied range and activate the Remove duplicates button on the Data tab
  3. Make sure that Excel selects the correct column, and whether it is checked that the data includes header
  4. Removing duplicates is done 🙂

If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

3 Comments

  1. Martina Švejdová Štaudová

    Dobrý den chtěla bych se zeptat když odstranim ze seznamu duplicitni karty jak najdu které jsou ty duplicitní nebo jak to mam udelat aby se mi nekam ulozili, dekuji Martina

  2. Marcel Mojžíš

    Dobrý den,
    z vlastní zkušenosti doporučuji zkopírovat data na jiný list sešitu a tam teprve provést odstranění duplicit.
    Opakovaně se stalo (a nejen mě) že když je původní a kopírovaná oblast, kde se odebírají duplicity, na stejném listě, funkce udělá chybu a neodebere všechny. Mluvím o případu, kdy je hodnot několik set, nebo i několik tisíc. Toho si hned nevšimnete a to je pak divení, proč to pořád na konci nevychází.

Leave a Reply

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