Formatowanie warunkowe w Excelu – jak automatycznie wyróżniać dane
Formatowanie warunkowe jest przydatnym narzędziem w Excelu, które pozwala automatycznie zmieniać wygląd komórek i formatować jej zawartości na podstawie określonych przez nas reguł. Dzięki formatowaniu warunkowemu można wyróżnić najważniejsze informacje, ułatwić analizę danych, określić wzorce i trendy oraz poprawić czytelność danych. W dalszej części artykułu dowiesz się jak zrobić różne typy formatowania warunkowego w Excelu, jak edytować i usuwać formatowanie z arkusza kalkulacyjnego.
W dalszej części poradnika:
- Jak zrobić formatowanie warunkowe w Excelu.
- Formatowanie warunkowe za pomocą pasków danych.
- Formatowanie warunkowe za pomocą skali kolorów.
- Formatowanie warunkowe za pomocą zestawu ikon.
- Formatowanie warunkowe – reguły wyróżniania.
- Formatowanie warunkowe – reguły pierwszych/ostatnich.
- Jak usunąć formatowanie warunkowe z komórek?
- Jak edytować i kopiować formatowanie warunkowe w Excelu.
- Formatowanie warunkowe na podstawie formuły
- Podświetlenie całego wiersza na podstawie wartości komórek.
- Formatowanie warunkowe podsumowanie.
Jak zrobić formatowanie warunkowe w Excelu
Aby wykonać formatowanie warunkowe należy zaznaczyć zakres komórek, dla których chcemy nadać formatowanie. Następnie wybieramy grupę poleceń opisaną jako Formatowanie warunkowe znajdujące się na karcie narzędzia główne. Po rozwinięciu tej grupy będziemy mogli skorzystać z wielu opcji formatowania warunkowego. Każdą z tych opcji opiszę w kolejnych działach
Formatowanie warunkowe za pomocą pasków danych
Za pomocą pasków danych możemy wizualnie porównać do siebie wartości.
Przechodząc do przykładu pokazanego poniżej po zaznaczeniu komórek w kolumnie D zastosowaliśmy formatowanie warunkowe za pomocą pasków danych Formatowanie warunkowe > Paski danych >Gradient niebieski. Widzimy, że w tych komórkach pojawiły się niebieskie paski, których wielkość jest zależna od wartości liczbowej w komórce.
Jeśli będziemy chcieli zastosować inne niestandardowe reguły lub chcieli zastosować inny niż domyślny kolor pasków danych należy przejść do polecenia Więcej reguł, które znajduje się pod ikonami domyślnych pasków danych
Po wybraniu opcji Więcej reguł pojawi się nowe okno z którego będziemy mogli dla pasków danych:
- Zmieniać kolor wypełnienia i obramowania pasków danych
- Określić skalę minimum i maksimum
- Pokazać tylko pasek bez wartości
- Zmieniać kierunek paska od prawej do lewej
- Ustalić parametry pasków dla wartości ujemnych
Formatowanie warunkowe za pomocą skali kolorów
W tej opcji formatowania poziom wartości reprezentowany jest przez podświetlenie w kolorze całej komórki. Kolory możemy wybrać z kilku domyślnych. W przykładzie przedstawiono kolorystykę tak aby wartości najwyższe były podświetlone na kolor zielony a najniższe na żółty. Kolorystykę możemy wybrać jako jedną z domyślnych lub nadać własną po przejściu do dodatkowych opcji tego formatowania warunkowego – należy kliknąć Więcej reguł.
Po wybraniu więcej reguł skali kolorów otrzymamy możliwość:
- Zmiany skali na 2-kolorową, 3-kolorową lub zmianę typu formatowania np. zestawem ikon
- Ustawić skalę na podstawie wartości minimum i maksimum
- Dostosować kolorystykę
Formatowanie warunkowe za pomocą zestawu ikon
Kolejny sposób prezentacji danych opiera się o ikonę. Mamy do wyboru szereg ikon, które przedstawiłem na poniższej grafice. W przykładzie zastosowano trzy kolorowe koła gdzie najniższe wartości zaznaczone są kolorem czerwonym a najwyższe zielonym.
Podobnie jak poprzednio aby zmienić opcję dla formatowania zestawem ikon musimy przejść do opcji klikając w polecenie Więcej reguł.
Po wybraniu dodatkowych opcji dla Zestawu ikon będziemy mogli:
- Zmienić styl ikon
- Pokazać tylko ikonę bez wartości w komórkach
- Ustawić reguły pojawienia się rodzaju ikon w zależności od wartości komórek. Ikony możemy uzależnić od wartości procentowej, liczby, formuły lub percentylu dla wszystkich wartości w komórkach.
Formatowanie warunkowe – reguły wyróżniania
Jeśli będziemy chcieli wyróżnić komórki na podstawie określonej przez nas stałej wartości liczbowej, daty lub tekstu bądź też podświetlić duplikujące się wartości (a także unikatowe) mamy do dyspozycji kilka opcji wyróżniania za pomocą reguł wyróżniania komórek.
Przykładowo będę chciał pokazać Wynik liczbowy pomiędzy 100 a 150, wybieram opcję Między…
Pojawi mi się okno w którym wpisuję lub wskazuję adresy komórek z najmniejszą wartością 100 i największą 150 a także wybieram w jakim kolorze ma się podświetlić komórka spełniająca regułę.
W tym przypadku mamy 3 wartości spełniające naszą regułę.
Formatowanie warunkowe – reguły pierwszych/ostatnich
Kolejną grupą formatowania warunkowego komórek jest zaznaczenie pierwszych lub ostatnich wartości dla całego zakresu danych, które weryfikujemy.
Dla przykładu aby podświetlić 3 najwyższe wartości liczbowe wybieram opcję 10 pierwszych elementów…
I w oknie, które się pojawi zmieniam domyślną ilość z 10 na 3 oraz wybieram aby te pozycje zostały podświetlone na kolor żółty. Jeśli żaden domyślny kolor formatowania mi nie odpowiada mogę wybrać format niestandardowy
Ponad 180 ćwiczeń z Excela
- Zeszyt ćwiczeń dostosowany do Ciebie. – Mnóstwo zadań na poziomie podstawowym i średniozaawansowany,
- Śledź swoje postępy – w arkuszu tytułowym przedstawione są Twoje postępy w realizacji ćwiczeń.
- Uproszczona nawigacja po całym pliku – poruszaj się po zadaniach za pomocą przystępnej nawigacji
- Bezterminowy dostęp do ćwiczeń
Jak usunąć formatowanie warunkowe z komórek?
Aby usunąć formatowanie warunkowe możemy usunąć wszystkie formatowania komórek w arkuszu lub wybrać z których komórek.
W tym celu wybieramy jedną z dostępnych opcji znajdujących się w grupie Wyczyść reguły
Jak edytować i kopiować formatowanie warunkowe w Excelu
Aby edytować formatowanie lub zmienić jego pole działania należy przejść do polecenia Zarządzaj regułami…
Następnie wybieramy regułę z dostępnych w arkuszu lub dostępnych w zaznaczonym obszarze.
Tutaj mamy do dyspozycji:
- Nowa reguła (utworzenie nowego formatowania warunkowego)
- Edycję reguły
- Usunięcie reguły
- Duplikowanie reguły (kopiowanie formatowania warunkowego dla innego zakresu danych)
- Zmianę zakresu komórek, których dotyczy formatowanie warunkowe
- Zatrzymanie innych reguł po spełnieniu wskazanego warunku
Formatowanie warunkowe na podstawie formuły
Najtrudniejszym sposobem formatowania warunkowego jest napisanie własnej formuły na podstawie, której podświetlone zostaną komórki.
Załóżmy że chcemy podświetlić na zielono wszystkie pozycje ze statusem „Zakończone” w kolumnie F. W tym celu:
- Zaznaczamy zakres komórek
- Wybieramy polecenie Formatowanie warunkowe > Nowa reguła
- W oknie zmieniamy typ reguły na Użyj reguły do określania komórek, które należy sformatować
- W miejscu na formułę wprowadzamy = F2=„zakończone”, która sprawdzi czy w komórce F2 jest wpisana wartość „zakończone”. Zwróć uwagę aby usunąć wartości bezwzględne (znak $), którym domyślnie oznaczane są zaznaczane komórki. Jeśli pozostawisz wartość bezwzględną, to wszystkie zaznaczone komórki będą odnosiły się tylko do tej jednej komórki F2. Ogólnie mówiąc wartość względna komórki powoduje, że dla zaznaczonego zakresu danych sprawdzana będzie reguła dla każdej komórki.
- Następnie musimy określić sposób na wizualne przedstawienie komórek spełniających regułę. W tym celu wybieramy Formatuj
- W nowym oknie wybieramy zakładkę Wypełnienie i zaznaczamy kolor zielony.
- Potwierdzamy wybór we wszystkich oknach ok i zastosuj, po czym powinniśmy otrzymać taki efekt jak poniżej.
Gdy status dla dowolnej pozycji zmieni się na „zakończony” to komórka automatycznie zostanie podświetlona na zielono.
Podświetlenie całego wiersza na podstawie wartości komórek
Jeśli chcielibyśmy aby cały wiersz z danymi podświetlił się na kolor zielony gdy pozycja jest zakończona musimy zaznaczyć cały zakres w którym zablokujemy przesunięcie komórki w kolumnie za pomocą znacznika $
Poniżej znajduje się przykład zastosowania formatowania warunkowego
Formatowanie warunkowe podsumowanie
Formatowanie warunkowe jest jedną z najważniejszych funkcjonalności używanych w Excelu. Jak widzimy firma Microsoft włożył dużo pracy w to aby ten moduł Excela był bardzo rozbudowany i pozwalał na wiele opcji automatycznego formatowania komórek.
W zależności od potrzeb wybieramy rodzaj formatowania. Ja osobiście uważam, że jeśli dane znajdują się w jednej kolumnie to najbardziej czytelne są paski danych natomiast gdy dane są ułożone w kilku kolumnach to lepszym rozwiązaniem jest skala kolorów. Zestaw ikon dobrze spisuje się gdy sprawdzamy trendy i wzorce.
Pobierz plik do tego poradnika
Od ponad 10 lat uczę Excela w sposób który ma sens — bez zbędnej teorii, tylko praktyczne przykłady z codziennej pracy biurowej. Ukończyło moje kursy ponad 6 000 kursantów. Jeśli chcesz nauczyć się Excela sprawnie i bez stresu — jesteś w dobrym miejscu.
Zobacz wszystkie kursy Excel →Te poradniki mogą Cię też zainteresować
