Formatowanie warunkowe w Excelu: jak z niego korzystać (z przykładami formuł)

Aby użyć formatowania warunkowego w Excelu, zaznacz komórki, przejdź do Narzędzia główne → Formatowanie warunkowe i wybierz regułę, np. Reguły wyróżniania komórek → Większe niż, Paski danych lub Skale kolorów. Do wszystkiego, czego menu nie obejmuje, np. wyróżnienia całego wiersza lub porównania dwóch kolumn, wybierz Nowa reguła → Użyj formuły do określenia komórek, które należy sformatować i wpisz formułę zwracającą PRAWDA dla komórek, które mają zostać sformatowane.

Większość problemów wynika z jednego szczegółu: w regule opartej na formule znaki $ decydują, na które komórki patrzy formuła. Jeśli ustawisz je dobrze (zwykle $C2, z zablokowaną kolumną i swobodnym wierszem), reguła działa w każdym wierszu. Jeśli źle, Excel wyróżni złe komórki albo żadnej.

Nazwy poleceń podajemy według polskiej wersji Excela. W polskim Excelu argumenty funkcji oddziela się średnikami, a nazwy funkcji są spolszczone (przy pierwszym użyciu podajemy też angielską nazwę).

Wbudowane reguły formatowania warunkowego

Te kroki są takie same w Excelu z Microsoft 365, 2024, 2021 i 2019 w Windows.

1. Zaznacz komórki do sformatowania, na przykład kolumnę Kwota (D2:D10), bez nagłówka.
2. Przejdź do Narzędzia główne → Formatowanie warunkowe. W Windows możesz też nacisnąć kolejno Alt, H, L (ta sekwencja dotyczy angielskiej wersji Excela).
3. Wskaż Reguły wyróżniania komórek i wybierz regułę:
   • Większe niż, Mniejsze niż, Pomiędzy lub Równe dla liczb
   • Tekst zawierający dla słów takich jak „Zaległe”
   • Data występująca dla dat, takich jak Wczoraj, Ostatnie 7 dni, Przyszły tydzień lub Ten miesiąc
   • Duplikaty wartości, aby oznaczyć powtórzenia (lub przełącz listę na Unikatowe)
4. Wpisz wartość, wybierz format z listy z (np. Jasnoczerwone wypełnienie i ciemnoczerwony tekst) albo Format niestandardowy.
5. Kliknij OK.

Reszta menu działa tak samo:

• Reguły pierwszych/ostatnich wyróżniają 10 pierwszych elementów, 10% pierwszych, 10 ostatnich elementów, 10% ostatnich albo wartości Powyżej średniej i Poniżej średniej. Liczbę 10 możesz zmienić na dowolną.
• Paski danych rysują pasek w każdej komórce, dłuższy dla większych wartości (Wypełnienie gradientowe lub Wypełnienie jednolite).
• Skale kolorów cieniują komórki od jednego koloru do drugiego, np. czerwony dla niskich i zielony dla wysokich wartości.
• Zestawy ikon dodają strzałki, sygnalizatory lub oceny zależnie od tego, gdzie dana wartość mieści się w zakresie.

Aby znaleźć lub usunąć duplikaty, zobacz poradnik o usuwaniu duplikatów w Excelu.

Wyróżnianie całego wiersza na podstawie wartości komórki

Wbudowane reguły formatują tylko komórki, które spełniają warunek. Aby pokolorować cały wiersz, gdy w kolumnie Status jest „Zaległe”, potrzebna jest reguła z formułą. Załóżmy, że dane są w A1:D100: Klient w A, Termin w B, Status w C, Kwota w D.

1. Zaznacz cały zakres danych bez wiersza nagłówka, zaczynając od lewej górnej komórki: kliknij A2 i przeciągnij do D100.
2. Przejdź do Narzędzia główne → Formatowanie warunkowe → Nowa reguła.
3. W sekcji Wybierz typ reguły wybierz Użyj formuły do określenia komórek, które należy sformatować.
4. W polu Formatuj wartości, dla których ta formuła jest prawdziwa wpisz: =$C2="Zaległe"
5. Kliknij Formatuj, otwórz kartę Wypełnienie, wybierz jasnoczerwony i kliknij OK.
6. Sprawdź, czy Podgląd pokazuje Twój format, i kliknij OK.

Każdy wiersz, w którym kolumna C zawiera „Zaległe”, zmienia teraz kolor na czerwony od A do D, a kolor podąża za zmianą statusu. Porównanie nie rozróżnia wielkości liter. Jeśli status wpisuje się ręcznie, lista rozwijana zapewni jednolitą pisownię, żeby reguła nie pominęła żadnego wiersza.

Dlaczego znak $ ma znaczenie (błąd numer 1)

Excel zapisuje formułę dla aktywnej komórki, czyli pierwszej komórki zaznaczenia (A2 powyżej), a potem dostosowuje ją do każdej innej komórki, tak jak przy kopiowaniu formuły w dół i w bok.

Przy =C2="Zaległe" komórka A2 sprawdza C2, ale B2 sprawdza D2, więc podświetla się tylko część wiersza. Przy =$C$2="Zaległe" każda komórka sprawdza C2, więc cały zakres jest albo cały czerwony, albo wcale.

Uwaga: jeśli klikniesz komórkę podczas wpisywania formuły, Excel wstawi odwołanie bezwzględne, np. $C$2. Naciskaj F4 (na Macu Cmd+T lub Fn+F4), aby przełączać $C$2, C$2, $C2 i C2, aż uzyskasz właściwe.

Więcej przykładów formuł

Użyj tych samych kroków z poniższymi formułami. Wszystkie zakładają, że zaznaczenie zaczyna się w wierszu 2.

Sformatuj komórkę na podstawie innej komórki. W arkuszu sprzedaży z kolumną Wykonanie w D i Cel w E zaznacz D2:D100 i użyj: =$D2<$E2 Każde wykonanie poniżej celu zmieni kolor na czerwony. Zaznacz zamiast tego A2:E100, aby wyróżnić cały wiersz.

Porównaj z jedną komórką wejściową. Wpisz próg w H1, zaznacz D2:D100 i użyj =$D2>$H$1. $H$1 jest w pełni bezwzględne, więc każdy wiersz patrzy na tę samą komórkę.

Wyróżnij daty z przeszłości. Zaznacz A2:D100 i użyj: =$B2<DZIŚ() (DZIŚ() to angielskie TODAY().) Puste komórki liczą się jak zero (data w 1900 roku), więc puste terminy również się podświetlą. Aby pominąć puste komórki i opłacone faktury, użyj =ORAZ($B2<>"";$B2<DZIŚ();$C2<>"Opłacone") (ORAZ to angielskie AND).

Cieniuj co drugi wiersz. Zaznacz dane i użyj: =MOD(WIERSZ();2)=0 (WIERSZ() to angielskie ROW().) Cieniuje to wiersze parzyste (=1 dla nieparzystych). Narzędzia główne → Formatuj jako tabelę z opcją Wiersze naprzemienne daje ten sam efekt bez reguły.

Połącz warunki. Użyj ORAZ lub LUB (angielskie AND i OR), na przykład =ORAZ($C2="Oczekujące";$D2>5000) dla dużych oczekujących faktur. Nie potrzebujesz funkcji JEŻELI (IF): formuła musi tylko zwrócić PRAWDA lub FAŁSZ.

Zarządzanie regułami, kolejność reguł i Zatrzymaj, jeśli prawda

Przejdź do Narzędzia główne → Formatowanie warunkowe → Zarządzaj regułami, aby otworzyć Menedżera reguł formatowania warunkowego.

1. W polu Pokaż reguły formatowania dla wybierz Ten arkusz, aby zobaczyć wszystkie reguły w arkuszu.
2. Użyj Edytuj regułę lub Usuń regułę albo popraw zakres reguły w polu Dotyczy (np. =$A$2:$D$100) i kliknij Zastosuj.
3. Strzałkami W górę i W dół zmieniasz kolejność reguł. Reguły wyżej na liście mają pierwszeństwo.

Jeśli dwie reguły ustawiają to samo inaczej, np. dwa kolory wypełnienia, wygrywa wyższa reguła. Jeśli nie kolidują (jedna pogrubia czcionkę, druga wypełnia komórkę na czerwono), obie obowiązują. Formatowanie z reguł zastępuje też formatowanie nałożone ręcznie.

Zaznacz Zatrzymaj, jeśli prawda, aby Excel przestał sprawdzać reguły poniżej, gdy ta reguła jest spełniona. Przydaje się to przy wyjątkach typu „jeśli Status to Opłacone, nie pokazuj niczego więcej”. Opcja nie jest dostępna dla reguł pasków danych, skal kolorów i zestawów ikon.

Aby usunąć formatowanie, użyj Narzędzia główne → Formatowanie warunkowe → Wyczyść reguły → Wyczyść reguły z zaznaczonych komórek lub Wyczyść reguły z całego arkusza.

Kopiowanie formatowania warunkowego do innych komórek

• Malarz formatów: zaznacz sformatowaną komórkę, kliknij Narzędzia główne → Malarz formatów, a potem przeciągnij po nowych komórkach. Kliknij dwukrotnie, aby malować kilka zakresów; naciśnij Esc, aby zakończyć.
• Wklej specjalnie: skopiuj komórkę, zaznacz miejsce docelowe, a potem Narzędzia główne → Wklej → Wklej specjalnie → Formaty.
• Zamiast tego rozszerz regułę: edytuj zakres Dotyczy w Zarządzaj regułami. Dzięki temu masz jedną czystą regułę zamiast kilku kopii.

Excel dla Maca i Excel w przeglądarce

Excel dla Maca ma to samo menu Narzędzia główne → Formatowanie warunkowe. Dla reguły z formułą wybierz Nowa reguła, zmień Styl na Klasyczny, wybierz Użyj formuły do określenia komórek, które należy sformatować w polu poniżej, wpisz formułę i ustaw Formatuj za pomocą na Format niestandardowy.

Excel w przeglądarce także używa Narzędzia główne → Formatowanie warunkowe, ale reguły otwierają się w panelu Formatowanie warunkowe zamiast w oknie dialogowym. Dla reguły z formułą wybierz Nowa reguła i ustaw typ reguły na Formuła. Reguły utworzone na komputerze działają w przeglądarce i odwrotnie. Etykiety w wersji przeglądarkowej mogą się nieco różnić od podanych.

Dlaczego formatowanie warunkowe nie działa

Wyróżnia złe wiersze (przesunięcie o jeden wiersz). Jeśli zaznaczenie zaczyna się w A2, formuła musi odwoływać się do wiersza 2. Zwykle dzieje się tak, gdy uwzględnisz wiersz nagłówka lub zaznaczysz od dołu do góry.

Wyróżniona jest tylko jedna kolumna albo wszystko. Sprawdź znaki dolara. Dla reguł całych wierszy użyj $C2, a nie C2 ani $C$2.

Liczby są zapisane jako tekst. Zaimportowane wartości mogą wyglądać jak liczby, a być tekstem (często z małym zielonym trójkątem w rogu), przez co reguły takie jak Większe niż działają błędnie. Zaznacz komórki, kliknij ikonę ostrzeżenia i wybierz Konwertuj na liczbę.

Tekst się nie zgadza. Spacja na końcu („Zaległe ”) lub inna pisownia nie będzie równa „Zaległe”. Wyczyść dane funkcją USUŃ.ZBĘDNE.ODSTĘPY (TRIM) albo użyj Tekst zawierający zamiast Równe.

Reguły nakładają się lub kolidują. Otwórz Zarządzaj regułami, pokaż Ten arkusz i sprawdź kolejność oraz zakresy Dotyczy. Kopiowanie, wklejanie i wstawianie wierszy często dzieli jedną regułę na wiele fragmentów.

Skoroszyt zwolnił. Setki rozdrobnionych reguł, reguły na całych kolumnach (=$A:$D) oraz funkcje ulotne, takie jak DZIŚ() lub ADR.POŚR (INDIRECT), zwiększają ilość przeliczeń. Usuń duplikaty i ogranicz Dotyczy do używanych wierszy.

Najczęściej zadawane pytania

Jak wyróżnić wiersz na podstawie wartości komórki w Excelu? Zaznacz dane bez nagłówków, wybierz Narzędzia główne → Formatowanie warunkowe → Nowa reguła → Użyj formuły do określenia komórek, które należy sformatować, wpisz =$C2="Zaległe" i wybierz wypełnienie. Znak $ przed literą kolumny sprawia, że zmienia kolor cały wiersz.

Jak zastosować formatowanie warunkowe na podstawie innej komórki? Użyj reguły z formułą wskazującą tę komórkę, np. =$D2<$E2, aby wyróżnić wartości poniżej celu, lub =$D2>$H$1, aby porównać każdy wiersz z jedną komórką wejściową.

Czy formatowanie warunkowe może odwoływać się do innego arkusza? Tak, na przykład =$D2>Ustawienia!$B$1. Odwołania do innych skoroszytów nie są dozwolone.

Czy mogę sortować lub filtrować według koloru z formatowania warunkowego? Tak. Włącz Dane → Filtruj, otwórz strzałkę filtra kolumny i wybierz Filtruj według koloru lub Sortuj według koloru. Działa to z kolorami nadanymi przez reguły.

Jak usunąć formatowanie warunkowe bez kasowania danych? Zaznacz komórki i wybierz Narzędzia główne → Formatowanie warunkowe → Wyczyść reguły → Wyczyść reguły z zaznaczonych komórek. Wartości pozostaną bez zmian.

Niech AI podpowie, co wyróżnić

Formatowanie warunkowe pokazuje to, czego już postanowiłeś(-aś) szukać. CoreGPT for Excel działa odwrotnie: zapytaj „co wyróżnia się w sprzedaży za III kwartał?”, a odczyta dane i wskaże najlepszych wykonawców, nietypowe spadki i trendy, dzięki czemu wiesz, które reguły warto ustawić. Możesz zacząć za darmo, a dodatek działa w Excelu dla Windows, Maca i przeglądarki.

Wypróbuj: CoreGPT for Excel

Powiązane artykuły:

• Usuwanie duplikatów w Excelu: 5 sposobów (z usuwaniem danych i bez)
• Jak zrobić listę rozwijaną w Excelu (i sprawić, by aktualizowała się sama)
• XLOOKUP vs VLOOKUP: najważniejsze różnice, przykłady i wybór funkcji