Formatowanie warunkowe w Excelu to bardzo przydatne narzędzie, które pomoże Ci np. wtedy, gdy chcesz wyróżnić komórki z zakresu danych czy tabeli. Stosując tą funkcjonalność, możesz tworzyć własne reguły, dzięki czemu jesteś w stanie znaleźć wartości, które spełniają określone kryteria. Sprawdź, jak stosować formatowanie warunkowe w Excelu.
Kiedy warto stosować formatowanie warunkowe?
Formatowanie warunkowe jest niezwykle przydatne w codziennej pracy z Excelem. Przyda Ci się m.in. wtedy, gdy chcesz automatycznie wyróżnić komórki, które spełniają określone reguły. Przykładowo, masz plik, w którym znajdują się informacje o klientach oraz zamówieniach, które złożyli w Twoim sklepie. Jeśli chcesz je monitorować, możesz użyć dodatkowej kolumny, w której znajdzie się tekst opisujący status realizacji zamówienia. Niech będzie to “oczekujące”, “przyjęte do realizacji”, “zrealizowane”.
Jest to dobry pomysł, choć przy dużej ilości zamówień tekst może być nieczytelny – wtopi się w tłum innych wartości. Używając formatowania warunkowego możesz sprawić, że komórki z danym tekstem będą automatycznie wypełniane danym kolorem. Przykładowo statusowi oczekującemu możesz nadać kolor czerwony, przyjętemu do realizacji pomarańczowy, a zrealizowanemu zielony.
Co istotne, narzędzie formatowania warunkowego posiada o wiele więcej możliwości. Jeśli nie pasują Ci wbudowane formuły, zawsze możesz stworzyć własne reguły, a nawet użyć formuł do określenia komórek, które należy sformatować. Ale po kolei. Zacznijmy od podstaw.
Podstawowe formatowanie warunkowe
Najprostszy sposób na użycie formatowania warunkowego, to skorzystanie z gotowych reguł, które są wbudowane w Excela. Aby znaleźć je, musisz przenieść się do wstążki i wybrać kartę “Narzędzia główne”, która jest domyślne aktywowana, gdy otwierasz skoroszyt. Znajdź na niej formatowanie warunkowe i rozwiń listę.
Jak widzisz, znajdują się tu opcje, dzięki którym możesz formatować określone komórki. Te podstawowe, którymi zajmiemy się teraz, to reguły wyróżniania komórek oraz reguły pierwszych/ostatnich. Dla lepszego zrozumienia temat posłużmy się przykładem. Mamy zakres komórek taki, jak na poniższym obrazku.
Znajdują się tu imiona, nazwiska, płeć oraz wiek osób. Korzystając z formatowania nadajmy czerwone tło komórkom, w których wiek jest większy od 30 lat. Jeśli chcesz osiągnąć taki efekt, musisz zaznaczyć komórki, w których znajduje się wiek osób (lub całą kolumnę, gdy np. planujesz dodawać kolejne osoby i chcesz korzystać z tych samych reguł), a następnie wybrać opcję reguł wyróżniania komórek i przejść do “Większe niż…”.
Otworzy się okno, w którym musisz wybrać regułę. W naszym przypadku jest to liczba 30, ponieważ chcemy, aby pokolorowane były komórki osób, które mają więcej, niż 30 lat. Następnie po prawej stronie wybierz, co ma się stać, gdy reguła jest prawdziwa. W tym przypadku jest to wypełnienie komórki jasnoczerwonym tłem z ciemnoczerwonym tekstem. Po zatwierdzeniu tego formatu efekt jest taki, jak poniżej.
Zwróć uwagę na to, że tworząc regułę formatowania, skorzystałem z wbudowanego szablonu kolorostycznego. Jeśli nie pasują Ci te opcje, możesz zdecydować się na własną skalę kolorów, ale też utworzyć obramowanie oraz wybrać własną czcionkę. Aby to zrobić, w oknu dialogowym formatowania, zamiast wskazanego jasnoczerwonego wypełnienia wybierz “Format niestandardowy…”
Formatowanie duplikatów
Kolejną możliwością jest zaznaczenie wartości, które występują kilka razy w danym zakresie komórek (duplikaty). Jeżeli chcesz to zrobić, po raz kolejny musisz kliknąć formatowanie warunkowe. Z listy rozwijanej wybierz duplikujące się wartości. Następnie, jeżeli chcesz formatować duplikaty, zdefiniuj, jak Microsoft Excel ma sformatować komórki. Dla przykładu wybiorę niebieskie tło. Efekt możesz zauważyć poniżej.
Niestandardowe reguły formatowania warunkowego
Do tej pory przedstawiłem podstawowe możliwości, jakie daje Ci formatowanie warunkowe. To jednak nie wszystko. Poza tymi opcjami Excel umożliwia też korzystanie z niestandardowych reguł, w których możesz umieszczać nawet formuły.
Aby przystąpić do działania, po raz kolejny musisz przenieść się do formatowania warunkowego, jednak tym razem z listy wybierz “Nowa reguła…”. Po kliknięciu tej opcji zostanie otworzone nowe okno dialogowe.
Znajdują się tu różne możliwości, jednak skupmy się na ostatniej opcji, która daje Ci możliwość użycia formuły do określenia komórek, które należy sformatować. Jak z niej skorzystać? Przede wszystkim możesz wykorzystać tu standardowe formuły stosowane w Excelu, takie jak np. formuła JEŻELI.
W celu lepszego zobrazowania działania załóżmy, że chcemy, aby Excel pokolorował komórki z imionami, które zaczynają się na literę A. W tym celu należy skorzystać z formuły. Niech będzie to formuła LEWY, dzięki której wyciągnięmy pierwszą literę imienia. Jeżeli chcesz rozpocząć tworzenie formuły, musisz najpierw zaznaczyć odpowiedni zakres. Będą to komórki z imionami tak, jak poniżej.
Teraz z powodzeniem można przystąpić do tworzenia nowej reguły. Wiesz już, że zaznaczone mają być komórki, w których imię zaczyna się od litery A. Formuła dla takiego rozwiązania i pierwszej komórki (A3) jest następująca:
=LEWY(A3;1)
Po wklejeniu jej w dowolnej komórce otrzymasz literę A. No dobrze, ale jak sprawić, aby formuła działała i odpowiednio formatowała komórki? Bardzo prosto. Wystarczy, że wkleisz ją do paska formuł, który znajduje się w formatowaniu warunkowym i… dokonasz niewielkich modyfikacji. Przyjrzyj się poniższemu obrazkowi.
Jak widzisz, formuła została nieco zmodyfikowana. Chcemy, aby Excel sprawdził, czy pierwsza litera to litera A. Sama formuła LEWY wyciąga nam tylko tą literę, ale nic nie sprawdza. To właśnie dlatego po nawiasie należy dodać dalszą część, czyli:
LEWY($A3;1) = “A”
Dodatkowo możesz zauważyć, że przy literze kolumny pojawił się znak dolara ($A3). Takie działanie jest konieczne, ponieważ wybraliśmy określony zakres komórek z danej kolumny. W związku z tym chcemy, aby Excel przesuwał się po wierszach, a kolumna pozostała niezmienna – to właśnie dlatego musimy ją zablokować. Zwróć uwagę na to, że w tym przypadku nie blokujemy wierszy, ponieważ formatowanie warunkowe ma “płynnie” przechodzić po naszym zakresie.
Na koniec pozostaje wybranie formatowania, ale to już jest najprostszy krok całej procedury.
Jak znaleźć, edytować i wyczyścić reguły?
Stworzyliśmy już kilka reguł. Działają prawidłowo, jednak możesz zastanawiać się, co w przypadku, gdy chcesz je zmienić, podglądnąć lub usunąć, gdy nie są Ci już więcej potrzebne? Jest to bardzo proste.
Jeżeli chcesz podglądnąć lub edytować stworzone reguły, możesz skorzystać z opcji “Zarządzaj regułami”.
Zwróć uwagę, że po otworzeniu okna dialogowego nie zawsze zauważysz reguły dla całego arkusza. Może tak się stać, gdy wyświetlasz reguły formatowania dla zaznaczenia. Aby zobaczyć wszystkie reguły, które znajdują się w danym arkuszu, wybierz opcję “Ten arkusz”.
Z kolei, jeżeli chcesz wyczyścić reguły, skorzystaj z opcji “Wyczyść reguły”, a następnie wskaż, z jakiego zakresu chcesz usunąć reguły. Możesz to zrobić dla zaznaczonych komórek, całego arkusza, ale też tabeli lub tabeli przestawnej. Pamiętaj, że dwa ostatnie warianty uaktywnią się tylko wtedy, gdy w arkuszu masz stworzoną tabelę. W naszym przypadku dane mogą wyglądać jak tabela, jednak de facto jest to tylko określony zakres komórek.
Tabelę, którą Excel interpretuje faktycznie jako tabelę, tworzy się przy pomocy skrótu klawiszowego Ctrl + T lub wybierając opcję Wstawianie -> Tabela.
Tworzenie pasków danych
Kolejna dość ciekawa możliwość, to stworzenie pasków danych, które sprawdzą się np. jako tak zwane paski postępu realizacji celu. Kiedy możesz je zastosować?
Dobrym przykładem jest realizacja celu miesięcznych oszczędności. I właśnie takie zastosowanie użyję, aby zobrazować Ci działanie tej funkcjonalności. Do wcześniejszych danych dodajmy trzy kolumny tak, jak poniżej.
W pierwszej z nich znajduje się kwota, którą dana osoba chce zaoszczędzić. W drugiej zauważysz, że znajdują się faktycznie zaoszczędzone pieniądze. Na podstawie tych danych stwórzmy pasek postępu oszczędności w kolumnie G. Aby to zrobić, najpierw należy obliczyć procentowo te oszczędności w kolumnie.
Teraz należy zaznaczyć te komórki z kolumny G i przejść do formatowania warunkowego, a następnie do pasków danych i wybrać więcej reguł. Opcja, która nas interesuje, to formatowanie komórek na podstawie ich wartości.
Jak widzisz, aby pasek postępu działał prawidłowo, należy dokonać kilku modyfikacji. W stylu formatowania wybierz pasek danych. Jeżeli chcesz, aby widoczny był sam pasek (bez wartości procentowych), zaznacz dodatkowo opcję “Pokaż tylko pasek”.
Co więcej, poniżej należy zmienić typ danych na liczbę. Co do zasady chcemy, aby wartości procentowe zawierały się w przedziale od 0 do 100 procent, dlatego też nasza minimalna wartość to 0, a maksymalna to 1. Wartości ponad 100 procent potraktujmy jako osiągnięty cel, który również wypełni nasz pasek postępu w całości.
Na koniec warto wybrać odpowiednie formatowanie. Niech będzie to niebieskie wypełnienie tła. Teraz wystarczy tylko zatwierdzić. Efekt możesz zobaczyć poniżej.
Stworzyliśmy paski postępów, które wypełniają komórkę w zależności od osiągniętego celu.
Podsumowanie
Formatowanie warunkowe to bardzo przydatne narzędzie, które możesz wykorzystać, gdy chcesz wyróżnić komórki w zależności od spełnienia warunku. Korzystając z tej funkcjonalności, możesz skorzystać z gotowych, wbudowanych rozwiązań, a także tworzyć własne, niestandardowe reguły. Dodatkowo dzięki opcjom formatowania jesteś w stanie uatrakcyjnić swój arkusz, np. tworząc schludne paski danych.
