Jak znaleźć i zaznaczyć duplikaty w Excelu

Lista klientów, w której ten sam adres wpisano dwa razy. Zestawienie faktur, gdzie jeden numer pojawia się w dwóch wierszach. Arkusz zapisów, do którego trzy osoby dopisywały dane i nikt nie sprawdzał, czy ktoś już tam jest. Powtórzona pozycja nie rzuca się w oczy, a potrafi podwoić kwotę w podsumowaniu.

W tym poradniku przechodzimy szukanie duplikatów po kolei: od gotowej reguły, która podświetla powtórki jednym kliknięciem, przez własną formułę zaznaczającą cały wiersz i tylko drugie wystąpienie, aż po blokadę, która w ogóle nie pozwoli wpisać powtórki. Osobno duplikaty w dwóch kolumnach, gdzie powtórką jest dopiero para wartości, i porównanie dwóch list. Na końcu usuwanie duplikatów. Większość kroków na jednej tabeli z datami i miejscowościami, ze zrzutem z Excela.

Z jednej rzeczy bierze się tu wszystko: Excel nie ma pojęcia „duplikat”. Ma funkcję, która liczy, ile razy wartość występuje w zakresie. Jeśli wynik jest większy od jedynki, to jest duplikat. Cała reszta to decyzja, co z tą jedynką zrobić.

W dalszej części poradnika:

1. Czym są duplikaty w Excelu
2. Jak znaleźć duplikaty gotową regułą formatowania warunkowego
3. Jak podświetlić cały wiersz z duplikatem
4. Jak zaznaczyć tylko drugie i kolejne wystąpienie
5. Jak zablokować wprowadzanie duplikatów
6. Jak ustawić powiadomienie o duplikacie
7. Duplikaty w dwóch kolumnach
8. Jak usunąć duplikaty w Excelu
9. Podsumowanie

Czym są duplikaty w Excelu

Duplikat to komórka, która zawiera identyczny tekst, liczbę lub datę, co inna komórka. Jako duplikat uznajemy powtarzające się wartości w obrębie jednej tabeli lub zakresu danych. Wystarczy, że dwie komórki mają identyczną zawartość, ale możliwe jest również istnienie większej ich liczby - wszystkie w takim przypadku są uznawane za duplikaty.

Jak znaleźć duplikaty gotową regułą formatowania warunkowego

Formatowanie warunkowe pozwala podkreślić wzorce i trendy w danych, sprawiając, że stają się one bardziej zauważalne. To narzędzie można zastosować do określonego zakresu komórek, zarówno poprzez zaznaczenie, jak i poprzez odwołanie się do nazwanego zakresu. Formatowanie warunkowe można również wykorzystać do znalezienia duplikatów w Excelu.

Aby znaleźć duplikaty w Excelu za pomocą formatowania warunkowego:

1. w pierwszej kolejności zaznacz zakres (komórki), które mają zostać sprawdzone pod kątem duplikatów. Zaznaczam zakres w kolumnie C.

2. Następnie z karty narzędzia główne wybierz Formatowanie warunkowe > Reguły wyróżniania komórek > Duplikujące się wartości

3. Zduplikowane wartości w zaznaczonym zakresie zostaną automatycznie podświetlone w innym kolorze. Po prawej stronie pojawi się natomiast okno, które pozwala na wybór koloru (stylu formatowania), za pomocą którego będą oznaczane duplikaty. Po wybraniu stylu formatowania zduplikowanych komórek wystarczy nacisnąć przycisk OK

Reguła działa na bieżąco. Oznacza to, że jeśli zmienimy wartość na taką, jaka występuje już w naszym zakresie, to wartości zduplikowane zostaną automatycznie zaznaczone we wskazanym kolorze.

Dla przykładu zmieniłem wartość komórki C6 na taką samą co C4 i te dwie komórki zostały podświetlone na czerwono.

Jak podświetlić cały wiersz z duplikatem

Oprócz podświetlenia pojedynczych komórek ze zduplikowanymi wartościami możemy zaznaczyć cały wiersz dla tych duplikatów znajdujących się w zaznaczonym zakresie danych.

Z poniższych danych, gdzie duplikaty oznaczyłem na czerwono:

Osiągamy taki efekt:

Co zrobić, aby zaznaczyć cały wiersz gdy szukamy duplikujących się komórek?

1. W komórkach obok zakresu danych - wprowadzamy pomocniczo funkcję LICZ.JEŻELI

Funkcja LICZ.JEŻELI liczy, ile komórek w zakresie ma określoną wartość. Pierwszym argumentem jest zakres komórek, drugim szukana wartość.

Funkcję LICZ.JEŻELI wpisujemy w komórce F3 i jest ona odpowiednikiem komórki C3

• Pierwszy argument C3:C10 - jest zakresem wszystkich danych, które będziemy przeszukiwać
• Drugi argument C3 - wskazuje jakiej wartości szukamy w tym przypadku będzie to data 02.02.2022 znajdująca się w tej komórce.

Funkcja zwróci nam wartość 2, ponieważ w podanym zakresie danych występują dwie takie same daty 02.02.2022r.

Funkcję będziemy chcieli zastosować też do innych komórek, wobec tego zablokujmy zakres przed kopiowaniem odwołaniem bezwzględnym, czyli znakiem dolara.

Znak dolara przed literą kolumny i numerem wiersza mówi Excelowi, żeby przy kopiowaniu nie przesuwał tego adresu. Najszybciej wstawisz go klawiszem F4, stojąc kursorem na adresie w formule.

Po przekopiowaniu funkcji dla pozostałych komórek otrzymujemy taki wynik:

Jak widzisz, wszystkie duplikaty otrzymały wartość 2, a pozycje unikalne wartość 1.

Formatowanie warunkowe powinno zwracać informację w postaci PRAWDA lub FAŁSZ. Aby uzyskać taki efekt zmodyfikuję funkcję dodając porównanie >1

1 zwracająca wartość logiczną zamiast liczby”>

Dzięki temu otrzymaliśmy PRAWDA gdy wartość dla komórki jest zduplikowana w zakresie i FAŁSZ gdy wartość jest unikalna.

Funkcję będę chciał przekopiować w prawą stronę - dlatego muszę zablokować przesunięcie komórki C3 w kolumnie ($C3)

1″>

Po przekopiowaniu funkcji w dół i w prawo otrzymałem zakres danych PRAWDA/FAŁSZ, który będzie odpowiadał podświetleniu komórek dla zakresu C3:D10

Stworzona formuła, która będzie podświetlała komórki dla których wartość logiczna jest prawdziwa PRAWDA

Teraz pozostaje wykonać formatowanie warunkowe na podstawie utworzonej formuły:

1. Skopiuj utworzoną formułę

=LICZ.JEŻELI($C$3:$C$10;$C3)>1

1. Zaznacz zakres danych do sformatowania

2. Następnie wybierz z karty Narzędzia główne > Formatowanie warunkowe > Nowa reguła.

3. Pojawi się okno, w którym zaznacz typ Użyj formuły do określenia komórek, które należy sformatować. W polu Formatuj wartości, dla których następująca formuła jest prawdziwa, wklej skopiowaną poprzednio formułę.

4. Na tym etapie możesz również zmienić sposób podświetlenia komórki, klikając w przycisk Formatuj… i następnie wybierając zakładkę Wypełnienie ustawiamy pożądany kolor. Po czym zatwierdzamy całość przyciskiem OK.

Po wykonaniu tych czynności podświetlone zostaną całe wiersze gdy dane będą zduplikowane.

Reguła działa na bieżąco, natomiast jeśli chcesz dopisywać następne wartości poniżej, to zamień zakres na tabelę Excela. W tym celu zaznacz obszar zakresu obejmujący w przykładzie zarówno Datę jak i Miejscowości, a następnie użyj skrótu klawiszowego CTRL+T.  Pojawi się okno Utwórz tabelę, w którym kliknij OK.

Dzięki temu zastosowane formatowanie będzie automatycznie działało także dla kolejnych wpisywanych pozycji.

TIP: tabela Excela rozszerza regułę formatowania na każdy dopisany wiersz. Przy zwykłym zakresie reguła kończy się tam, gdzie ją ustawiłeś, a nowe wiersze pozostają niesprawdzone.

Jak zaznaczyć tylko drugie i kolejne wystąpienie

Skoro już wiesz, jak znaleźć duplikujące się komórki w Excelu warto, abyś wiedział, jak podświetlić duplikat następny.

Zaznaczanie kolorem duplikatu następnego pozwala na wyróżnienie danych, które występują po raz drugi oraz kolejny. Dzięki tej opcji analizowanie danych pod kątem występujących błędów jest o wiele łatwiejsze, a sama ich prezentacja czytelniejsza.

W naszym przykładzie będziemy chcieli podświetlić drugie i każde kolejne wystąpienie daty, która już wystąpiła wcześniej. Dane przeglądamy od góry do dołu. Finalnie uzyskując taki efekt:

Aby uzyskać zaznaczanie duplikatu koloru następnego, ponownie skorzystamy z funkcji LICZ.JEŻELI, którą wprowadzimy w komórce F3:

=LICZ.JEŻELI($C$3:C3;$C3)>1

1. Funkcja LICZ.JEŻELI liczy, ile razy wartość z komórki C3 (data 02.02.2022) występuje w zakresie $C$3:C3, czyli na razie w jednej komórce. Sprawdzamy, czy ten wynik jest większy od 1.

 Dla tej komórki formuła zwróci wartość FAŁSZ, ponieważ wynik funkcji LICZ.JEŻELI nie jest większy od 1.

1 w komórce F3 i kolumna wyników PRAWDA oraz FAŁSZ”>

2. Następnie skopiuj formułę dla pozostałych pozycji.

★ Tu jest cała sztuczka: początek zakresu jest zablokowany, a koniec nie. W wierszu trzecim zakres obejmuje jedną komórkę, w czwartym dwie, w piątym trzy. Każdy wiersz patrzy więc tylko na to, co stoi nad nim, i dzięki temu pierwsze wystąpienie nigdy nie zostanie uznane za duplikat. Dzięki temu wartość logiczna PRAWDA występuje tylko dla drugiego i każdego następnego wystąpienia.

3. Teraz pozostaje nam tak jak w poprzednim przykładzie wprowadzić tę formułę do reguły formatowania warunkowego:

Po zatwierdzeniu reguły formatowania uzyskamy efekt jak poniżej:

TIP: budując regułę formatowania warunkowego na własnej formule, najpierw rozpisz ją obok danych w tabeli pomocniczej zwracającej PRAWDA lub FAŁSZ. Reguła nie pokazuje wyniku formuły, więc bez takiej tabeli nie da się sprawdzić, czy podświetla to, co powinna.

🚀 Następny poziom

Reguła formatowania warunkowego na własnej formule

✓Lekcje wideo: 40

✓Ćwiczenia z plikami: 17

✓Dostęp bez terminu

✓Gwarancja zwrotu: 30 dni

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

Jak zablokować wprowadzanie duplikatów w Excelu

W przypadku powtarzających się danych warto nie tylko wiedzieć, jak znaleźć duplikaty w Excelu, ale także jak zablokować możliwość ich wprowadzania.

Zablokowanie możliwości wprowadzania duplikatów w Excelu pozwala na uniknięcie wprowadzenia błędnych danych lub danych, które nie powinny zostać wprowadzone w określonej postaci.

Przejdźmy do przykładu (jak z poprzedniego zadania) i zablokujmy możliwość wprowadzania daty, która już występuje w naszej tabeli danych.

W tym celu:

1. Przekopiuj formułę, która sprawdza, czy data występuje kolejny raz w zakresie danych

=LICZ.JEŻELI($C$3:C3;$C3)>1

1 w pasku formuły, kopiowana skrótem Ctrl + C”>

2. Zaznacz zakres danych, na który ma zostać nałożona blokada poprawności. W naszym przykładzie zakres C3:D12

3. Przejdź do karty Dane > Narzędzia danych > Poprawność danych

4. Otworzy się okno Sprawdzanie poprawności danych. Z rozwijanej listy kryteriów poprawności jako Dozwolone wybierz Niestandardowe, a następnie w polu Formuła wprowadź skopiowaną wcześniej formułę i zmodyfikuj ją, aby tym razem dopuszczała wartości tj. zwracała PRAWDA gdy wartości będą mniejsze lub równe 1 (takie wartości będą dopuszczone do wprowadzenia).

=LICZ.JEŻELI($C$3:C3;$C3)<=1

5. W tym samym oknie przejdź do zakładki Alert o błędzie i w oknie Tytuł wpisz treść, jaka ma pojawiać się po wprowadzeniu zduplikowanych danych, np. Duplikat. W polu Komunikat o błędzie wpisz natomiast instrukcje dla użytkowników arkusza, którym wyświetli się alert o wprowadzeniu duplikatu. Np. „Popraw błąd”. Na koniec kliknij OK.

Zastosowane ustawienia sprawią, że wprowadzenie duplikatów do arkusza w danym zakresie nie będzie już możliwe. Przy próbie ich wprowadzenia pojawi się z kolei komunikat.

W poniższym przykładzie wprowadziłem w kolejnej komórce datę 03.02.2022, która już w tabeli jest. Excel nie pozwolił na zatwierdzenie tej daty w komórce.

TIP: Aby zakreślić duplikujące się wartości, wybierz opcję Excela Zakreśl nieprawidłowe dane

Dane, które są niezgodne z regułą poprawności danych, zostaną zakreślone czerwoną elipsą.

Jak ustawić powiadomienie o duplikacie

W poprzednim dziale pokazałem, jak zablokować wprowadzenie duplikującej się wartości. Natomiast możemy też pozwolić na duplikat, ale poinformować, że taki duplikat się pojawił.

Aby ustawić tę opcję dla poprzedniego przykładu:

1. przejdź ponownie do okna Sprawdzanie poprawności danych tj. karta Dane > Narzędzia danych > Poprawność danych. Zaznacz Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami.

2. Następnie przejdź do zakładki Alert o błędzie i z rozwijanej listy Styl: wybierz Informacje. W polu Komunikat o błędzie możesz ustawić treść informacji dla użytkownika np. Popraw duplikat. Wprowadzone zmiany zatwierdź przyciskiem OK.

Po zastosowaniu powyższych ustawień możliwość wprowadzania zduplikowanych wartości zostanie odblokowana. Po ich wpisaniu pojawi się natomiast wyłącznie komunikat.

Po wciśnięciu przycisku OK wprowadzanie danych w komórce nadal będzie możliwe. Jeśli wpisana wartość jest duplikatem, zostanie podświetlona kolorem.

Duplikaty w dwóch kolumnach

Do tej pory duplikatem była powtórzona wartość w jednej kolumnie. Często to za mało. W rejestrze rezerwacji samochodów ta sama data pojawia się wiele razy i nie ma w tym błędu. Błędem jest dopiero ten sam samochód zarezerwowany dwa razy na ten sam dzień, czyli powtórzona para wartości z dwóch kolumn.

W pliku do tej części (drugi plik pod artykułem) dane stoją w zakresie B3:D13: data, auto i miejscowość. Zamiast LICZ.JEŻELI używamy LICZ.WARUNKI (COUNTIFS). Ta funkcja przyjmuje kilka par „zakres; kryterium” i liczy tylko te wiersze, w których zgadzają się wszystkie naraz.

Wszystkie powtórzone pary

Zaznacz B3:D13, wybierz Narzędzia główne > Formatowanie warunkowe > Nowa reguła > Użyj formuły do określenia komórek, które należy sformatować i wpisz:

=LICZ.WARUNKI($B$3:$B$13;$B3;$C$3:$C$13;$C3)>1

Formuła liczy, ile wierszy ma tę samą datę i ten sam samochód co bieżący wiersz. Wynik większy od jedynki oznacza powtórkę. Litery kolumn są zablokowane znakiem $, a numer wiersza nie, więc kolorem wypełnia się cały wiersz rezerwacji, a nie pojedyncza komórka. Gdy powtórką ma być dopiero zgodność trzech kolumn, dopisz trzecią parę: $D$3:$D$13;$D3.

Tylko drugie wystąpienie pary

Tak samo jak przy jednej kolumnie, wystarczy zablokować tylko początek zakresów. Każdy wiersz patrzy wtedy wyłącznie na wiersze nad sobą, więc pierwsza rezerwacja zostaje bez koloru, a podświetla się dopiero ta, która ją dubluje:

=LICZ.WARUNKI($B$3:$B3;$B3;$C$3:$C3;$C3)>1

Ostrzeżenie przy wpisywaniu powtórzonej pary

Blokada działa tak, jak opisałem wyżej dla jednej kolumny: Dane > Poprawność danych, kryterium Niestandardowe, ale z formułą dla dwóch kolumn:

=LICZ.WARUNKI($B$3:$B3;$B3;$C$3:$C3;$C3)<=1

W pliku alert ma styl Informacje: Excel pokazuje komunikat „Duplikat”, ale pozwala zatwierdzić wpis. Przy rezerwacjach to rozsądne, bo czasem drugi wpis jest celowy, na przykład przy zmianie miejscowości w ciągu dnia. Jeśli powtórka ma być niemożliwa, ustaw styl Zatrzymaj.

Uwaga: poprawność danych sprawdza tylko to, co ktoś wpisuje ręcznie. Dane wklejone ze schowka przechodzą bez ostrzeżenia. Dlatego regułę formatowania warunkowego warto zostawić obok blokady - ona pokaże powtórkę niezależnie od tego, jak trafiła do arkusza.

Porównanie dwóch kolumn: które wartości są w obu listach

Inna sytuacja: masz dwie osobne listy, na przykład klientów z tego i z zeszłego roku, i chcesz zobaczyć, kto jest na obu. Jeśli lista z tego roku stoi w kolumnie B, a z zeszłego w kolumnie D, zaznacz B3:B100 i użyj reguły z formułą:

=LICZ.JEŻELI($D$3:$D$100;$B3)>0

Podświetlą się nazwiska z kolumny B, które występują gdziekolwiek w kolumnie D. Zamiana na =0 pokaże odwrotnie: nowych klientów, których rok wcześniej nie było. Gdy listy są ułożone w tej samej kolejności i porównujesz je wiersz po wierszu, wystarczy prostsza formuła =$B3<>$D3, która zaznaczy wiersze, w których wartości się różnią.

Gotowa reguła Duplikujące się wartości też zadziała, jeśli zaznaczysz obie kolumny naraz (drugą z wciśniętym Ctrl). Ma jedną wadę: podświetla także powtórki wewnątrz jednej listy, więc nie odróżnisz klienta, który jest na obu listach, od klienta wpisanego dwa razy na jednej.

Jak usunąć duplikaty w Excelu

Znalezienie powtórek to jedno, pozbycie się ich to drugie. Excel ma na to gotowe narzędzie:

1. zaznacz zakres albo kliknij dowolną komórkę w tabeli,
2. przejdź do karty Dane i wybierz Usuń duplikaty,
3. w oknie zaznacz kolumny, które decydują o tym, że wiersz jest powtórką,
4. zatwierdź przyciskiem OK. Excel pokaże, ile wierszy usunął i ile zostało.

Trzy rzeczy, o których warto wiedzieć, zanim to klikniesz.

• Zaznaczone kolumny decydują o wszystkim. Przy zaznaczonej jednej kolumnie Excel usuwa wiersze, w których powtarza się tylko ta wartość, choćby reszta wiersza była zupełnie inna. Przy wszystkich kolumnach usunie wyłącznie wiersze identyczne na całej długości.
• Zostaje pierwsze wystąpienie, reszta znika. Nie ma wyboru, które z nich zatrzymać. Jeśli ma znaczenie, który wiersz zostanie, najpierw posortuj dane.
• Operacji nie da się cofnąć po zamknięciu pliku. Zanim usuniesz, zrób kopię arkusza. To jedyna rzecz w tym poradniku, która kasuje dane.

Jeśli zamiast usuwać chcesz porównać dwie kolumny i sprawdzić, które wartości są w obu, opisałem to wyżej, w części duplikaty w dwóch kolumnach.

Podsumowanie

Pięć sposobów z tego poradnika rozwiązuje pięć różnych sytuacji. Który wybrać:

• Chcesz tylko zobaczyć powtórki w jednej kolumnie - gotowa reguła Duplikujące się wartości, dwa kliknięcia, bez formuł.
• Chcesz widzieć cały wiersz - własna reguła z LICZ.JEŻELI i zakresem zablokowanym z obu stron.
• Chcesz zostawić pierwszy wpis w spokoju - ta sama formuła, ale z zakresem zablokowanym tylko na początku.
• Chcesz, żeby duplikat w ogóle nie wszedł do arkusza - poprawność danych z formułą niestandardową.
• Chcesz się ich pozbyć - Dane, Usuń duplikaty, po wcześniejszej kopii arkusza.

Jedna pułapka wspólna dla wszystkich tych sposobów: Excel porównuje zawartość komórki, a nie to, co widzisz. „Kraków” i „Kraków ” ze spacją na końcu to dla niego dwie różne wartości i nie zostaną uznane za duplikat. Tak samo data wpisana jako tekst i ta sama data wpisana jako data. Jeśli powtórki są oczywiste, a Excel ich nie widzi, winne są niemal zawsze spacje albo format.

🚀 Następny poziom

Porządki w danych to osobny dział Excela

✓Lekcje wideo: 40

✓Ćwiczenia z plikami: 17

✓Certyfikat po ukończeniu

✓Kursantów: ponad 9 000

Zobacz kurs Excel Średniozaawansowany

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy