Formatowanie warunkowe w Excelu – jak działa i jak je stosować?

Czym jest formatowanie warunkowe i dlaczego działa automatycznie?

Formatowanie warunkowe to reguła, która nadaje komórce format tylko wtedy, gdy jej zawartość spełnia warunek. Formatem jest kolor tła, kolor czcionki, obramowanie, pasek danych lub ikona. Warunkiem jest zdanie logiczne, na przykład wartość większa niż 1000 albo tekst równy słowu „Zaległe”.

Najważniejsza jest jego dynamika. Excel sprawdza warunek na bieżąco, przy każdej zmianie danych. Gdy status zmieni się z „oczekujący” na „zrealizowany”, kolor zmieni się sam. Nie musisz nic klikać ani odświeżać. Ta cecha odróżnia formatowanie warunkowe od zwykłego kolorowania, które zostaje na stałe.

Warto zapamiętać jedną zasadę, która oszczędza późniejszego zamieszania. Zgodnie z dokumentacją Microsoft, przy konflikcie reguła warunkowa ma pierwszeństwo przed formatowaniem ręcznym. Jeśli reguła i ręczny kolor trafią na tę samą komórkę, wygra reguła. Dlatego formatowania warunkowego używaj tam, gdzie dane się zmieniają. Gdy wartość jest stała, zwykłe wypełnienie bywa prostsze.

Jak zastosować formatowanie warunkowe krok po kroku?

Formatowanie warunkowe stosujesz w trzech krokach: zaznacz zakres, wybierz regułę, ustaw format. Wszystko znajdziesz na karcie Narzędzia główne, w grupie Style, pod przyciskiem Formatowanie warunkowe.

1. Zaznacz zakres komórek, którego ma dotyczyć reguła. To najważniejszy krok, bo poza zaznaczeniem format się nie pojawi.
2. Wejdź w Narzędzia główne, a następnie Formatowanie warunkowe. Zobaczysz kilka kategorii gotowych reguł.
3. Wybierz gotową kategorię albo Nową regułę, gdy potrzebujesz własnego warunku.
4. Kliknij Formatuj, ustaw kolor tła, czcionki lub ikonę i zatwierdź przyciskiem OK.

Zakres decyduje o wszystkim. Jeśli dane rosną z każdym tygodniem, zamień zakres w tabelę skrótem Ctrl plus T. Reguła rozszerzy się wtedy sama na nowe wiersze i nie musisz jej poprawiać. To rozwiązuje typowy problem, gdy świeżo dodane dane zostają bez koloru.

Jak wyróżnić komórki spełniające warunek: większe, mniejsze, tekst, data?

Do wyróżniania komórek według wartości służy grupa Reguły wyróżniania komórek. Zaznacz dane, wejdź w Formatowanie warunkowe, potem Reguły wyróżniania komórek i wskaż warunek. Excel podświetli komórki, które go spełnią, i będzie to robił dynamicznie.

• Większe niż / Mniejsze niż. Podświetla liczby powyżej lub poniżej progu. Działa też na datach, bo Excel traktuje datę jak liczbę.
• Między. Wyróżnia wartości z przedziału, na przykład od 100 do 150. Przydaje się do kontroli norm i widełek.
• Równe. Koloruje komórki z konkretną liczbą lub słowem, na przykład „Tak”.
• Tekst zawierający. Szuka fragmentu w komórce, na przykład miasta „Katowice” w pełnym adresie.
• Data występująca. Wyróżnia daty względem dnia dzisiejszego, na przykład z najbliższych 7 dni.

Jest tu trik, który konkurencja rzadko pokazuje. Zamiast wpisywać próg na sztywno w regule, wpisz go do osobnej komórki i wskaż ją w oknie warunku. Wtedy zmieniasz próg w arkuszu, a formatowanie przelicza się samo. Reguła oparta na dacie działa podobnie, bo korzysta z zegara systemowego i aktualizuje kolory każdego dnia. Dlatego świetnie pilnuje terminów płatności.

Jak podświetlić najwyższe, najniższe i zduplikowane wartości?

Do skrajnych wartości użyj grupy Reguły pierwszych/ostatnich, a do powtórzeń opcji Duplikujące się wartości. Pierwsza grupa znajdzie na przykład 10 najlepszych wyników. Druga oznaczy każdą wartość, która pojawia się więcej niż raz.

• 10 pierwszych / 10 ostatnich elementów. Domyślną dziesiątkę zmienisz na dowolną liczbę, na przykład 3 albo 15.
• Pierwsze / Ostatnie 10%. Liczy procent od liczby zaznaczonych komórek, a nie od wartości.
• Powyżej / Poniżej średniej. Excel sam liczy średnią zakresu i wyróżnia odchylenia.
• Duplikujące się / Unikatowe. Wskazuje powtórzenia albo wartości występujące tylko raz.

Zgodnie z dokumentacją Microsoft w regule pierwszych/ostatnich podasz liczbę od 1 do 1000, a dla procentu od 1 do 100. Zapamiętaj też jedną rzecz, która myli początkujących. Formatowanie duplikatów tylko je pokazuje, ale nie usuwa. Do usunięcia powtórzeń służy osobne polecenie Dane, potem Usuń duplikaty. Kolor traktuj jako sygnał do sprawdzenia danych, nie jako czyszczenie.

Kiedy użyć pasków danych, skali kolorów, a kiedy zestawów ikon?

Wybór zależy od tego, co chcesz pokazać. Paski danych porównują wielkość liczb w jednej kolumnie. Skala kolorów pokazuje rozkład wartości w większej tabeli. Zestaw ikon dzieli dane na trzy do pięciu kategorii, na przykład dobry, średni, słaby.

Format	Co pokazuje	Kiedy wybrać
Paski danych	Długość paska odpowiada wielkości liczby	Jedna kolumna liczb, szybkie porównanie
Skala kolorów	Odcień oddaje poziom wartości	Duże tabele, efekt mapy ciepła
Zestaw ikon	Ikona przypisuje wartość do kategorii lub progu	Trzy do pięciu grup, trendy, statusy

Prosta zasada ułatwia wybór. Dane w jednej kolumnie czytają się najlepiej z paskami danych. Dane rozłożone w wielu kolumnach zyskują na skali kolorów. Trend lub ocenę najczytelniej pokazuje zestaw ikon. Gdy tabela idzie do druku, unikaj skali kolorów, bo półtony gubią się na wydruku i w kopii czarno-białej.

Te trzy formaty najczęściej trafiają do raportów i dashboardów budowanych w Excelu, bo przekazują sens jednym spojrzeniem. W każdym z nich możesz wejść w Więcej reguł i ustawić własne progi oraz kolory. Domyślne ustawienia są dobrym startem, ale rzadko idealnie pasują do konkretnych danych.

Jak stworzyć regułę na podstawie własnej formuły?

Gdy gotowe reguły nie wystarczą, użyj własnej formuły. Wejdź w Formatowanie warunkowe, Nowa reguła, a potem „Użyj formuły do określenia komórek, które należy sformatować”. Formuła musi zaczynać się od znaku równości i zwracać PRAWDA albo FAŁSZ. Format pojawi się tylko tam, gdzie wynik to PRAWDA.

Sedno tkwi w odwołaniach. Zgodnie z dokumentacją Microsoft, Excel liczy formułę osobno dla każdej komórki zakresu i przesuwa odwołania tak, jakby formuła była kopiowana. Dlatego znak dolara decyduje o tym, co się zablokuje.

• A1 to odwołanie względne, przesuwa się razem z komórką.
• $A$1 to odwołanie bezwzględne, kolumna i wiersz są zablokowane.
• $A1 blokuje kolumnę, wiersz pozostaje ruchomy.
• A$1 blokuje wiersz, kolumna pozostaje ruchoma.

Zanim wkleisz skomplikowaną formułę do reguły, przetestuj ją w zwykłej komórce. Jeśli zwróci PRAWDA lub FAŁSZ zgodnie z oczekiwaniem, przeniesienie jej do formatowania będzie proste. Reguły z formułą świetnie łączą się z logiką warunków opartą na funkcji JEŻELI, dlatego warto znać obie techniki naraz.

Jak sformatować cały wiersz na podstawie jednej kolumny?

Aby pokolorować cały wiersz, zaznacz całą tabelę i użyj reguły z formułą, w której zablokujesz kolumnę znakiem dolara. Przy statusie w kolumnie E formuła wygląda tak: =$E2=”Zaległe”. Dolar przed literą E sprawia, że każda komórka w wierszu sprawdza tę samą kolumnę.

1. Zaznacz zakres danych bez nagłówków, na przykład A2:F100.
2. Wejdź w Formatowanie warunkowe, Nowa reguła, „Użyj formuły”.
3. Wpisz =$E2=”Zaległe”, zakładając, że dane zaczynają się w wierszu 2.
4. Ustaw kolor tła i zatwierdź.

Najczęstszy błąd to pominięcie dolara przed kolumną. Bez niego formatowanie przesuwa się w prawo i koloruje przypadkowe komórki. Drugi błąd dotyczy pierwszego wiersza. Formuła odnosi się do pierwszej komórki danych, więc przy danych od wiersza 2 piszesz E2, nie E1. Jeśli zaznaczysz razem z nagłówkiem, odniesienie się przesunie i reguła zadziała o wiersz za wcześnie.

Jak uzależnić formatowanie od innej komórki lub listy rozwijanej?

Możesz sterować formatowaniem z jednej komórki, zamiast wchodzić w regułę za każdym razem. Wpisz próg do osobnej komórki, na przykład E1, i odwołaj się do niej bezwzględnie: =B2<$E$1. Zmiana liczby w E1 od razu przelicza kolory w całym zakresie.

Ta sama zasada łączy formatowanie z listą rozwijaną. Utwórz listę rozwijaną przez Dane, potem Sprawdzanie poprawności danych, i umieść ją w komórce sterującej, na przykład H1. Formuła =$E2=$H$1 podświetli wiersze o statusie wybranym z listy. Wybór z listy działa wtedy jak wizualny filtr, który nie zmienia kolejności danych.

Klucz to mieszanie odwołań. Komórkę sterującą blokujesz na sztywno dolarami, a dane zostawiasz względne, by reguła przechodziła wiersz po wierszu. W ten sam sposób porównasz dwie listy, na przykład formułą =LICZ.JEŻELI($A$2:$A$100;B2)=0, która wyróżni kody z kolumny B nieobecne w kolumnie A. Gdy często zestawiasz listy, pomocna bywa też funkcja X.WYSZUKAJ.

Sprawdź także: Jak działa formuła licz.jeżeli?

Jak zarządzać regułami i ustalać ich priorytet, gdy się nakładają?

Wszystkie reguły znajdziesz w Menedżerze reguł formatowania warunkowego: Formatowanie warunkowe, Zarządzaj regułami. Excel sprawdza je od góry do dołu. Gdy dwie reguły chcą pokolorować tę samą komórkę różnie, wygrywa ta wyżej na liście.

Nie każde nałożenie oznacza konflikt. Jeśli jedna reguła pogrubia czcionkę, a druga daje czerwone tło, Excel zastosuje obie. Konflikt pojawia się dopiero wtedy, gdy reguły ustawiają tę samą właściwość inaczej, na przykład dwa różne kolory tła. Kolejność zmieniasz strzałkami w górę i w dół.

Jest tu opcja, która myli wielu użytkowników. Zgodnie z dokumentacją Microsoft, pole „Zatrzymaj, gdy warunek jest prawdziwy” blokuje sprawdzanie kolejnych reguł dla komórki, która spełniła bieżącą. Warto też wiedzieć, że okno domyślnie pokazuje reguły tylko z zaznaczenia. Jeśli „nie widzisz swoich reguł”, przełącz widok z „Bieżące zaznaczenie” na „Ten arkusz”.

Jak kopiować, znajdować i usuwać formatowanie warunkowe?

Formatowanie warunkowe kopiujesz Malarzem formatów, usuwasz przez Wyczyść reguły, a znajdujesz przez Przejdź do specjalnie. To trzy różne narzędzia do trzech różnych zadań.

• Kopiowanie. Kliknij komórkę z regułą, użyj Malarza formatów, przeciągnij po nowych komórkach. Dwuklik na Malarzu pozwala wklejać wielokrotnie.
• Usuwanie. Wejdź w Formatowanie warunkowe, Wyczyść reguły, i wybierz zakres: z zaznaczonych komórek albo z całego arkusza.
• Znajdowanie. Użyj Narzędzia główne, Znajdź i zaznacz, Przejdź do specjalnie, potem Formaty warunkowe.

Uważaj na zwykłe kopiowanie komórek skrótem Ctrl plus C. Razem z danymi przenosi ono reguły i po kilku wklejeniach powstaje bałagan z dziesiątkami pociętych zakresów. Gdy nie chcesz przenosić formatowania, użyj Wklej specjalnie, a potem Wartości. Przy kopiowaniu reguł z formułą sprawdź też, czy odwołania nie wymagają poprawki dolarów.

Najczęstsze błędy w formatowaniu warunkowym i jak ich uniknąć?

Najwięcej problemów wynika z pięciu błędów. Każdy z nich albo psuje efekt, albo spowalnia plik. Dobra wiadomość jest taka, że wszystkie łatwo naprawić po ich rozpoznaniu.

• Złe odwołania z dolarem. Formatowanie przesuwa się w bok albo koloruje nie te komórki. Do całego wiersza używaj wzoru z zablokowaną kolumną, na przykład $E2.
• Wiele drobnych reguł zamiast jednej. Osobna reguła dla każdego fragmentu spowalnia arkusz. Twórz jedną regułę na cały zakres z dobrze napisaną formułą.
• Sprzeczne reguły. Ostatnia potrafi nadpisać poprzednią. Sprawdź kolejność w Menedżerze i w razie potrzeby włącz „Zatrzymaj, gdy warunek jest prawdziwy”.
• Formatowanie całych kolumn. Reguła na A:A obciąża plik bez potrzeby. Ogranicz zakres do realnie używanych wierszy.
• Reguła nie działa przy błędzie formuły. Gdy komórka zwraca błąd, format się nie zastosuje.

Ostatni przypadek warto rozwinąć, bo bywa mylący. Jeśli reguła korzysta z komórki, która zwraca błąd typu #N/D lub #DZIEL/0!, formatowanie zostanie pominięte. Rozwiązaniem jest opakowanie wartości funkcją, która zwróci coś innego niż błąd, na przykład funkcją JEŻELI.BŁĄD. Dzięki temu reguła znów widzi normalną wartość i działa jak trzeba.

Dlaczego formatowanie warunkowe spowalnia Excel i jak to naprawić?

Formatowanie warunkowe spowalnia Excel, gdy reguł jest dużo albo obejmują całe kolumny. Program przelicza każdą regułę przy niemal każdej zmianie arkusza. Im większy zakres i im więcej reguł z formułami, tym więcej obliczeń pracuje w tle.

• Ogranicz zakres do realnych danych, na przykład A2:A5000 zamiast A:A.
• Łącz podobne reguły w jedną zamiast trzymać kilkanaście małych.
• Usuwaj martwe reguły i pocięte zakresy, które powstają przy kopiowaniu.
• Ostrożnie używaj funkcji lotnych, takich jak DZIŚ czy TERAZ, na dużych zakresach, bo przeliczają się bez przerwy.

Zanim zaczniesz cokolwiek kasować, otwórz Menedżer reguł i przełącz widok na „Ten arkusz”. W wolno działających plikach często widać kilkadziesiąt niemal identycznych reguł z pociętymi zakresami, powstałych przez kopiowanie komórek. Scalenie ich w jedną regułę na cały zakres zwykle najszybciej przywraca płynność.

Formatowanie to jednak tylko jedna z przyczyn spowolnień. Jeśli plik zwalnia mimo uporządkowania reguł, źródło leży zwykle w formułach, łączach lub rozmiarze danych, co dobrze tłumaczy materiał o tym, dlaczego Excel działa wolno i jak go przyspieszyć. Gdy raport ma zostać szybki, a samodzielne przekopywanie reguł pochłania zbyt dużo czasu, pomaga profesjonalna optymalizacja arkuszy Excela.