Formatowanie warunkowe w Excelu - jak automatycznie wyróżniać dane
Tabela z wynikami, terminami i statusami zadań. Chcesz od razu widzieć, kto przekroczył normę, które zadania są skończone i gdzie termin już minął, bez czytania wiersz po wierszu. Formatowanie warunkowe w Excelu koloruje komórki samo, według reguły ustawionej raz, i zmienia kolor, kiedy zmieni się wartość.
W tym poradniku przechodzimy wszystkie rodzaje formatowania na jednej tabeli: paski danych, skale kolorów, zestawy ikon, reguły wyróżniania i reguły pierwszych/ostatnich. Potem edycja, kopiowanie i usuwanie reguł, a na końcu własna formuła, która koloruje cały wiersz zamiast jednej komórki.
Warto doczytać do zestawów ikon. Na zrzucie wynik 150 dostał żółte kółko, choć jest trzeci od góry, i nie jest to błąd Excela. Wyjaśniam, skąd się to bierze i jak ustawić progi po swojemu.
💡 Formatowanie warunkowe od podstaw, z plikiem do każdej lekcji
W kursie Excel Podstawowy formatowanie warunkowe ma dwie lekcje: paski danych, skale kolorów, zestawy ikon i edycja reguł. Lekcje wideo: 40, ćwiczenia z rozwiązaniami: 52.
W tym poradniku:
- Jak zrobić formatowanie warunkowe w Excelu
- Paski danych
- Skale kolorów
- Zestawy ikon
- Reguły wyróżniania komórek
- Reguły pierwszych/ostatnich
- Jak usunąć formatowanie warunkowe
- Jak edytować i kopiować formatowanie warunkowe
- Formatowanie warunkowe z formułą
- Jak podświetlić cały wiersz
- Podsumowanie: który rodzaj wybrać
Jak zrobić formatowanie warunkowe w Excelu
Zaznacz komórki, które mają zmieniać wygląd, i na karcie Narzędzia główne kliknij Formatowanie warunkowe. Rozwinie się lista z pięcioma grupami gotowych reguł i trzema poleceniami do zarządzania nimi. Każdą grupę omawiam niżej.
Przykłady są na tabeli z wynikami pracowników: imię, dział, wynik procentowy, wynik liczbowy, termin realizacji i status zadania.
Paski danych
Paski danych rysują w komórce poziomy pasek, którego długość zależy od wartości. Najlepiej sprawdzają się przy porównaniu liczb w jednej kolumnie: od razu widać, która wartość odstaje.
W przykładzie zaznaczam kolumnę D z wynikami liczbowymi i wybieram Formatowanie warunkowe > Paski danych, a z wypełnień gradientowych niebieski pasek.
Kolor i zachowanie pasków zmienisz w poleceniu Więcej reguł pod listą gotowych pasków. W oknie, które się otworzy, możesz:
- zmienić kolor wypełnienia i obramowania,
- ustawić, od jakiej wartości liczy się minimum i maksimum paska,
- pokazać sam pasek, bez liczby w komórce,
- odwrócić kierunek paska, od prawej do lewej,
- ustawić wygląd pasków dla wartości ujemnych.
Skale kolorów
Skala kolorów wypełnia całą komórkę kolorem zależnym od wartości. W przykładzie najwyższe wyniki są zielone, najniższe żółte, a pośrednie dostają odcień pomiędzy. Gotowych zestawów kolorów jest kilkanaście, własny ustawisz w Więcej reguł.
W ustawieniach skali możesz:
- przełączyć skalę między 2-kolorową a 3-kolorową albo zmienić styl na paski lub ikony,
- ustawić, jaka wartość wyznacza minimum, środek i maksimum skali,
- dobrać własne kolory.
Środek domyślnej skali 3-kolorowej to percentyl 50, czyli mediana, a nie średnia. Połowa komórek zawsze wypadnie poniżej środka, niezależnie od tego, jak blisko siebie leżą wyniki.
Zestawy ikon
Ikony dzielą wartości na grupy i każdej grupie dają symbol: strzałkę, kółko, flagę albo gwiazdkę. W przykładzie są trzy kółka, zielone dla najwyższych wyników, żółte dla środkowych i czerwone dla najniższych.
Spójrz na wynik 150. Jest trzeci od góry, a dostał żółte kółko. Domyślne progi to 67 i 33 procent, ale procent liczony jest od rozpiętości wartości, a nie od ich liczby: od najmniejszej (80) do największej (190). Zielone kółko dostaje górna jedna trzecia tej rozpiętości, czyli wyniki od około 154 w górę. Do progu brakuje 150 kilku punktów.
Progi zmienisz w Więcej reguł. Typ Procent przestaw na Liczba i wpisz konkretne wartości, na przykład zielone od 150, czerwone poniżej 100. Wtedy kolory nie przeskoczą, kiedy w tabeli pojawi się jeden rekordowo wysoki albo niski wynik. W tym samym oknie zmienisz styl ikon, odwrócisz ich kolejność albo zostawisz same ikony bez liczb.
Reguły wyróżniania komórek
Reguły wyróżniania kolorują komórki, które spełniają prosty warunek: są większe albo mniejsze od liczby, mieszczą się w przedziale, zawierają tekst, przypadają na datę albo powtarzają się w zakresie.
Chcę wyróżnić wyniki od 100 do 150. Wybieram Między, wpisuję 100 i 150 i wybieram kolor wypełnienia.
Wyróżnione są trzy komórki: 120, 150 i 100. Granice się wliczają, więc „między 100 a 150” obejmuje też samo 100 i 150.
Zamiast wpisywać liczby w okienku, możesz wskazać komórki arkusza, w których stoją progi. Zmiana progu nie wymaga wtedy edycji reguły, wystarczy wpisać nową wartość w komórce. Regułę Duplikujące się wartości opisuję osobno w artykule o szukaniu duplikatów.
Reguły pierwszych/ostatnich
Ta grupa wyróżnia wartości skrajne: najwyższe, najniższe, górny albo dolny procent zakresu, wyniki powyżej albo poniżej średniej.
Żeby wyróżnić trzy najwyższe wyniki, wybieram 10 pierwszych elementów i w okienku zmieniam 10 na 3. Kolor wybieram z listy, a gdy żaden nie pasuje, z pozycji Format niestandardowy.
Przy remisie Excel wyróżni więcej komórek, niż wynosi wpisana liczba. Jeśli dwa wyniki dzielą trzecie miejsce, kolor dostaną oba, czyli razem cztery komórki.
Formatowanie warunkowe jest w kursie Excel Podstawowy, razem z tym, co dookoła
✓Paski danych, skale kolorów, zestawy ikon i edycja reguł
✓Funkcje logiczne i JEŻELI, z których korzystają reguły z formułą
✓Tabele, sortowanie, filtrowanie i fragmentatory
✓Ćwiczenia z rozwiązaniami: 52
Jak usunąć formatowanie warunkowe
Formatowanie warunkowe usuwasz poleceniem Formatowanie warunkowe > Wyczyść reguły. Do wyboru masz: z zaznaczonych komórek, z całego arkusza, z tabeli albo z tabeli przestawnej. Dwie ostatnie opcje są aktywne tylko wtedy, gdy kursor stoi w tabeli.
Wyczyść reguły zdejmuje tylko formatowanie warunkowe. Kolory nadane ręcznie zostają na miejscu i usuwa się je osobno, przez Narzędzia główne > Wyczyść > Wyczyść formaty. Jedną regułę zamiast wszystkich usuniesz w menedżerze reguł, opisanym niżej.
Jak edytować i kopiować formatowanie warunkowe
Wszystkie reguły znajdziesz w oknie Formatowanie warunkowe > Zarządzaj regułami.
Najpierw sprawdź listę Pokaż reguły formatowania dla na górze okna. Domyślnie pokazuje reguły bieżącego zaznaczenia, więc jeśli kursor stoi poza zakresem z regułą, okno jest puste i wygląda, jakby reguł nie było. Przełącz ją na Ten arkusz.
W menedżerze możesz:
- dodać nową regułę,
- edytować istniejącą,
- usunąć pojedynczą regułę,
- zduplikować regułę, żeby użyć jej dla innego zakresu,
- zmienić zakres komórek w kolumnie Dotyczy,
- zaznaczyć Zatrzymaj, gdy warunek jest prawdziwy, żeby reguły niżej na liście nie działały na komórki, które spełniły tę regułę.
Kolejność na liście ma znaczenie. Kiedy dwie reguły chcą nadać tej samej komórce różne kolory, wygrywa ta wyżej. Kolejność zmieniasz strzałkami obok przycisku Duplikuj regułę.
Regułę skopiujesz na inne komórki także malarzem formatów: kliknij komórkę z regułą, potem Malarz formatów na karcie Narzędzia główne i przeciągnij po nowym zakresie. Malarz przenosi przy tym całe formatowanie komórki, nie tylko regułę.
Formatowanie warunkowe z formułą
Gotowe reguły nie wystarczą, kiedy kolor ma zależeć od innej komórki albo od kilku warunków naraz. Wtedy piszesz własną formułę. Zasada jest jedna: jeśli formuła zwraca PRAWDA, komórka dostaje format.
Przykład: w kolumnie Status chcę podświetlić na zielono wszystkie zadania ze statusem „Zakończone”. Zaznaczam komórki F2:F8 i wybieram Formatowanie warunkowe > Nowa reguła.
W oknie wybieram typ reguły Użyj formuły do określenia komórek, które należy sformatować i w polu formuły wpisuję:
=F2="zakończone"
Formułę piszesz dla pierwszej komórki zaznaczenia, tu F2. Excel sam przesuwa ją na kolejne wiersze: dla F3 sprawdza F3, dla F4 sprawdza F4 i tak dalej. Dlatego adres nie może mieć znaków dolara. Gdy zamiast wpisać adres klikniesz komórkę, Excel wstawi $F$2 i każda komórka sprawdzałaby tylko F2. Wtedy albo wszystkie są zielone, albo żadna.
Wielkość liter nie ma znaczenia, „zakończone” w formule pasuje do „Zakończone” w komórce. Znaczenie ma za to spacja na końcu wpisu, której w komórce nie widać.
Zostaje wygląd. Klikam Formatuj, na karcie Wypełnienie wybieram zielony i zatwierdzam oba okna.
Kolor da się też ustawić samym formatem liczby, bez reguły: kodami od [Kolor1] do [Kolor56]. Ściągawkę z podglądem wszystkich 56 kolorów i plikiem do pobrania znajdziesz w artykule o formatowaniu niestandardowym.
Gotowe. Kiedy status dowolnego zadania zmieni się na „Zakończone”, komórka zazieleni się sama.
Jak podświetlić cały wiersz
Żeby zazieleniał się cały wiersz zadania, a nie tylko komórka statusu, zmieniasz dwie rzeczy: zakres i jeden znak dolara. Zaznacz całą tabelę bez nagłówków, A2:F8, i wpisz formułę:
=$F2="zakończone"
Dolar przed F blokuje kolumnę, więc każda komórka w wierszu, czy to imię w kolumnie A, czy wynik w D, sprawdza status w kolumnie F. Numer wiersza zostaje bez dolara i reguła przesuwa się w dół razem z tabelą.
Ten sam zapis z dolarem przed kolumną zadziała w każdej regule, która ma kolorować wiersz. Na przykład zadania po terminie, które nie są jeszcze zakończone:
=ORAZ($E2<DZIŚ();$F2<>"zakończone")
Formuła sprawdza dwa warunki naraz: termin z kolumny E jest wcześniejszy niż dzisiejsza data, a status w kolumnie F jest inny niż „zakończone”. Wiersz zmieni kolor sam następnego dnia po terminie, bez otwierania reguły.
Podsumowanie: który rodzaj wybrać
Paski danych najlepiej czytają się w jednej kolumnie liczb. Skala kolorów sprawdza się, gdy porównujesz wartości w kilku kolumnach naraz. Zestaw ikon dobrze pokazuje ocenę albo kierunek zmiany. Reguły wyróżniania i formuły są od tego, żeby wskazać konkretne komórki, które spełniają warunek.
Pięć rzeczy, o które najczęściej potyka się formatowanie warunkowe:
- Ikony liczą procent od rozpiętości wartości. Jeśli progi mają coś znaczyć, ustaw je jako Liczba.
- Dolar w formule decyduje o wszystkim. $F$2 każe wszystkim komórkom patrzeć na jedną, $F2 koloruje całe wiersze, a F2 bez dolarów wystarczy, gdy reguła obejmuje tylko kolumnę F.
- Menedżer reguł pokazuje domyślnie tylko zaznaczenie. Przełącz go na Ten arkusz, zanim uznasz, że reguły nie ma.
- Kolejność reguł decyduje o kolorze. Przy dwóch pasujących regułach wygrywa ta wyżej.
- Wyczyść reguły nie usuwa ręcznych kolorów. Te zdejmujesz poleceniem Wyczyść formaty.
W pliku do pobrania jest zadanie na formatowanie warunkowe z formułą: tabela lat i miesięcy, w której podświetla się wiersz wybranego roku i komórki wybranych miesięcy. Arkusz „zadanie” jest do samodzielnej pracy, a „wykonane” zawiera gotowe rozwiązanie.
🚀 Następny poziom
Zobacz kurs
Własne formuły i kolejność reguł mają w kursie Excel Średniozaawansowany osobne lekcje
✓Tworzenie własnych formuł formatowania warunkowego
✓Zarządzanie kolejnością reguł
✓SUMA.WARUNKÓW, LICZ.WARUNKI i usuwanie duplikatów
✓Gwarancja zwrotu: 30 dni
🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy
Pobierz plik ćwiczeniowy
Plik jest Twój bez zapisywania się na cokolwiek. Jeśli chcesz przejść Excela po kolei, zacznij od darmowego kursu.
