Excel'de Koşullu Biçimlendirme: Örneklerle En İyi Kılavuz

Koşullu Biçimlendirme, Excel Elektronik Tablolarındaki en basit ama güçlü özelliklerden biridir.

Adından da anlaşılacağı gibi, belirli bir koşulu karşılayan hücreleri vurgulamak istediğinizde Excel'de koşullu biçimlendirmeyi kullanabilirsiniz.

Veri kümeniz üzerine hızlı bir şekilde görsel analiz katmanı ekleme olanağı sağlar. Excel'de koşullu biçimlendirmeyi kullanarak ısı haritaları oluşturabilir, artan/azalan simgeleri, Harvey balonlarını ve daha fazlasını gösterebilirsiniz.

Excel'de Koşullu Biçimlendirmeyi Kullanma (Örnekler)

Bu eğitimde, size Excel'de koşullu biçimlendirmeyi kullanmanın yedi harika örneğini göstereceğim:

• Excel'de Koşullu Biçimlendirmeyi Kullanarak Yinelenenleri Hızla Tanımlayın.
• Bir Veri Kümesinde Sayıdan Büyük/Küçük Değeri Olan Hücreleri Vurgulayın.
• Bir Veri Kümesinde Üst/Alt 10 (veya %10) değeri vurgulama.
• Excel'de Koşullu Biçimlendirmeyi Kullanarak Hataları/Boşlukları Vurgulama.
• Excel'de Koşullu Biçimlendirmeyi Kullanarak Isı Haritaları Oluşturma.
• Koşullu Biçimlendirmeyi kullanarak Her N. Satırı/Sütunu Vurgulayın.
• Excel'de Koşullu Biçimlendirmeyi kullanarak Ara ve Vurgula.

1. Kopyaları Hızla Tanımlayın

Excel'deki koşullu biçimlendirme, bir veri kümesindeki kopyaları belirlemek için kullanılabilir.

Bunu şu şekilde yapabilirsiniz:

• Yinelenenleri vurgulamak istediğiniz veri kümesini seçin.
• Ana Sayfa -> Koşullu Biçimlendirme -> Hücre Kurallarını Vurgulama -> Yinelenen Değerler'e gidin.
• Yinelenen Değerler iletişim kutusunda, soldaki açılır menüde Çoğalt'ın seçili olduğundan emin olun. Sağdaki açılır menüyü kullanarak uygulanacak formatı belirleyebilirsiniz. Kullanabileceğiniz veya Özel Biçim seçeneğini kullanarak kendi biçiminizi belirleyebileceğiniz bazı mevcut biçimler vardır.
• Tamam'ı tıklayın.

Bu, seçilen veri kümesinde bir kopyası olan tüm hücreleri anında vurgulayacaktır. Veri kümeniz tek bir sütunda, birden çok sütunda veya bitişik olmayan bir hücre aralığında olabilir.

Ayrıca bakınız: Excel'de Yinelenenleri Bulmak ve Kaldırmak için En İyi Kılavuz.

2. Sayıdan Büyük/Küçük Değeri Olan Hücreleri Vurgulayın

Belirtilen bir değerden büyük/küçük değerler içeren hücreleri hızla vurgulamak için Excel'de koşullu biçimlendirmeyi kullanabilirsiniz. Örneğin, satış değeri 100 milyondan az olan tüm hücreleri vurgulama veya geçme eşiğinden daha düşük puanları olan hücreleri vurgulama.

İşte bunu yapmak için adımlar:

• Tüm veri kümesini seçin.
• Ana Sayfaya Git -> Koşullu Biçimlendirme -> Hücre Kurallarını Vurgulama -> Büyüktür… / Küçüktür…
• Hangi seçeneği seçtiğinize bağlı olarak (büyük veya küçük), bir diyalog kutusu açılır. Diyelim ki, 'Büyüktür' seçeneğini seçtiniz. Diyalog kutusunda, soldaki alana numarayı girin. Amaç, bu belirtilen sayıdan daha büyük bir sayıya sahip hücreleri vurgulamaktır.

• Sağdaki açılır menüyü kullanarak koşulu karşılayan hücrelere uygulanacak biçimi belirtin. Kullanabileceğiniz veya Özel Biçim seçeneğini kullanarak kendi biçiminizi belirleyebileceğiniz bazı mevcut biçimler vardır.
• Tamam'ı tıklayın.

Bu, bir veri kümesinde 5'ten büyük değerlere sahip tüm hücreleri anında vurgulayacaktır.Not: 5'ten büyük değerleri vurgulamak istiyorsanız, "Eşit" kriteri ile tekrar koşullu biçimlendirme uygulamalısınız.

Belirtilen değerlerden daha düşük bir değere sahip hücreleri vurgulamak için aynı işlem izlenebilir.

3. Vurgulama Üst/Alt 10 (veya %10)

Excel'deki koşullu biçimlendirme, bir veri kümesindeki ilk 10 öğeyi veya ilk %10'u hızla tanımlayabilir. Bu, satış verilerindeki puanlara veya en yüksek anlaşma değerlerine göre en iyi adayları hızlı bir şekilde görmek istediğiniz durumlarda yardımcı olabilir.

Benzer şekilde, bir veri kümesindeki en alttaki 10 öğeyi veya en alttaki %10'u da hızlı bir şekilde tanımlayabilirsiniz.

İşte bunu yapmak için adımlar:

• Tüm veri kümesini seçin.
• Ana Sayfa -> Koşullu Biçimlendirme -> Üst/Alt Kuralları -> İlk 10 Öğe (veya %) / En Alt 10 Öğe (veya %) seçeneğine gidin.
• Seçtiğiniz şeye bağlı olarak, diyalog kutusunu açacaktır. Diyelim ki En İyi 10 öğeyi seçtiniz, ardından aşağıda gösterildiği gibi bir diyalog kutusu açılacaktır:
• Sağdaki açılır menüyü kullanarak koşulu karşılayan hücrelere uygulanacak biçimi belirtin. Kullanabileceğiniz veya Özel Biçim seçeneğini kullanarak kendi biçiminizi belirleyebileceğiniz bazı mevcut biçimler vardır.
• Tamam'ı tıklayın.

Bu, seçilen veri kümesindeki ilk 10 öğeyi anında vurgulayacaktır. Bunun yalnızca içinde sayısal bir değere sahip hücreler için çalıştığını unutmayın.

Ayrıca, veri kümesinde 10'dan az hücreniz varsa ve İlk 10 öğe/En Alt 10 Öğe'yi vurgulama seçeneklerini seçerseniz, tüm hücreler vurgulanır.

Koşullu biçimlendirmenin nasıl çalışacağına dair bazı örnekler:

4. Hataları/Boşlukları Vurgulama

Excel'de çok sayıda sayısal veri ve hesaplamayla çalışıyorsanız, hatalı veya boş hücreleri tanımlamanın ve işlemenin önemini bilirsiniz. Bu hücreler daha sonraki hesaplamalarda kullanılırsa hatalı sonuçlara yol açabilir.

Excel'de Koşullu Biçimlendirme, hatalı veya boş olan hücreleri hızlı bir şekilde belirlemenize ve vurgulamanıza yardımcı olabilir.

Aşağıda gösterildiği gibi bir veri setimiz olduğunu varsayalım:

Bu veri kümesinde boş bir hücre (A4) ve hatalar (A5 ve A6) vardır.

Boş veya içinde hata bulunan hücreleri vurgulama adımları şunlardır:

• Boş hücreleri ve hatalı hücreleri vurgulamak istediğiniz veri kümesini seçin.
• Ana Sayfa -> Koşullu Biçimlendirme -> Yeni Kural'a gidin.
• Yeni Biçimlendirme Kuralı iletişim kutusunda, hangi hücrelerin biçimlendirileceğini belirlemek için Bir formül kullan'ı seçin.
• 'Kural Açıklamasını Düzenle' bölümündeki alana aşağıdaki formülü girin:
  =VEYA(BOŞ(A1),ISERROR(A1))
  • Yukarıdaki formül, tüm hücreleri iki koşul için kontrol eder - boş olup olmadığını ve bir hata olup olmadığını. Koşullardan herhangi biri TRUE ise, TRUE değerini döndürür.
• Boş veya hatalı hücrelere uygulamak istediğiniz biçimi ayarlayın. Bunu yapmak için Biçim düğmesine tıklayın. Biçimi belirleyebileceğiniz 'Hücreleri Biçimlendir' iletişim kutusunu açacaktır.
• Tamam'ı tıklayın.

Bu, boş olan veya içinde hata bulunan tüm hücreleri anında vurgulayacaktır.

Not: Koşullu biçimlendirmede formülde A1:A7 aralığının tamamını kullanmanız gerekmez. Yukarıda belirtilen formül yalnızca A1'i kullanır. Bu formülü tüm aralığa uyguladığınızda, excel her seferinde bir hücreyi kontrol eder ve referansı ayarlar. Örneğin, A1'i kontrol ettiğinde =VEYA(ISBLANK(A1),ISERROR(A1)) formülünü kullanır. A2 hücresini kontrol ettiğinde, =VEYA(ISBLANK(A2),ISERROR(A2)) formülünü kullanır. Hangi hücrenin analiz edildiğine bağlı olarak referansı (bunlar göreli referanslar olduğu için) otomatik olarak ayarlar. Yani her hücre için ayrı bir formül yazmanıza gerek yok. Excel, hücre referansını tek başına değiştirecek kadar akıllıdır 🙂

Ayrıca bakınız: Excel'deki hataları işlemek için IFERROR ve ISERROR kullanma.

5. Isı Haritaları Oluşturma

Isı haritası, rengin bir hücredeki değeri temsil ettiği verilerin görsel bir temsilidir. Örneğin, en yüksek değere sahip bir hücrenin yeşil renklendirildiği ve değer azaldıkça kırmızı renge doğru kayma olduğu bir ısı haritası oluşturabilirsiniz.

Aşağıda gösterildiği gibi bir şey:

Yukarıdaki veri kümesi 1 ile 100 arasında değerlere sahiptir. Hücreler, içindeki değere göre vurgulanır. 100 yeşil rengi, 1 kırmızı rengi alır.

Excel'de koşullu biçimlendirmeyi kullanarak ısı haritaları oluşturma adımları.

• Veri kümesini seçin.
• Ana Sayfa -> Koşullu Biçimlendirme -> Renk Ölçekleri'ne gidin ve renk şemalarından birini seçin.

Isı haritası simgesine tıkladığınız anda, biçimlendirmeyi veri kümesine uygular. Aralarından seçim yapabileceğiniz birden fazla renk gradyanı vardır. Mevcut renk seçeneklerinden memnun değilseniz, daha fazla kural seçebilir ve istediğiniz rengi belirleyebilirsiniz.

Not: Benzer şekilde Data Bard ve Icon setlerini de uygulayabilirsiniz.

6. Her Satırı/Sütunu Vurgulayın

Verilerin okunabilirliğini artırmak için alternatif satırları vurgulamak isteyebilirsiniz.

Bunlara zebra çizgileri denir ve özellikle verileri yazdırıyorsanız yardımcı olabilir.

Şimdi bu zebra çizgilerini oluşturmanın iki yolu var. En hızlı yol, tablo halindeki verilerinizi bir Excel Tablosuna dönüştürmektir. Alternatif satırlara otomatik olarak bir renk uyguladı. Bununla ilgili daha fazla bilgiyi buradan okuyabilirsiniz.

Başka bir yol da koşullu biçimlendirme kullanmaktır.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:

Excel'de koşullu biçimlendirmeyi kullanarak alternatif satırları vurgulama adımları aşağıda verilmiştir.

• Veri kümesini seçin. Yukarıdaki örnekte, A2:C13'ü seçin (başlık hariç). Başlığı da dahil etmek istiyorsanız, tüm veri kümesini seçin.
• Koşullu Biçimlendirme iletişim kutusunu açın (Ana Sayfa-> Koşullu Biçimlendirme-> Yeni Kural) [Klavye Kısayolu - Alt + O + D].
• İletişim kutusunda, 'Hangi hücrelerin biçimlendirileceğini belirlemek için bir Formül Kullan' iletişim kutusunu seçin.
• 'Kural Açıklamasını Düzenle' bölümündeki alana aşağıdaki formülü girin:
  =ISODD(SATIR())

• Yukarıdaki formül tüm hücreleri kontrol eder ve bir hücrenin SATIR numarası tek ise DOĞRU sonucunu verir. Belirtilen koşullu biçim, DOĞRU döndüren tüm hücrelere uygulanacaktır.
• Boş veya hatalı hücrelere uygulamak istediğiniz biçimi ayarlayın. Bunu yapmak için Biçim düğmesine tıklayın. Biçimi belirleyebileceğiniz 'Hücreleri Biçimlendir' iletişim kutusunu açacaktır.
• Tamam'ı tıklayın.

Bu kadar! Veri kümesindeki alternatif satırlar vurgulanacaktır.

Aynı tekniği birçok durumda kullanabilirsiniz. Tek yapmanız gereken koşullu biçimlendirmede ilgili formülü kullanmak. İşte bazı örnekler:

• Alternatif çift satırları vurgulayın: =ISEVEN(ROW())
• Alternatif ekleme satırlarını vurgulayın: =ISODD(ROW())
• Her 3. satırı vurgulayın: =MOD(ROW(),3)=0

7. Koşullu Biçimlendirmeyi Kullanarak Verileri Arayın ve Vurgulayın

Bu, koşullu biçimlendirmenin biraz gelişmiş kullanımıdır. Bir Excel rock yıldızı gibi görünmenizi sağlar.

Ürün Adı, Satış Temsilcisi ve Coğrafya ile aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım. Buradaki fikir, C2 hücresine bir dize yazmaktır ve herhangi bir hücredeki/hücrelerdeki verilerle eşleşirse, bu vurgulanmalıdır. Aşağıda gösterildiği gibi bir şey:

Bu Ara ve Vurgula işlevini oluşturma adımları şunlardır:

• Veri kümesini seçin.
• Ana Sayfaya Git -> Koşullu Biçimlendirme -> Yeni Kural (Klavye Kısayolu - Alt + O + D).
• Yeni Biçimlendirme Kuralı iletişim kutusunda, 'Hangi hücrelerin biçimlendirileceğini belirlemek için bir formül kullan' seçeneğini seçin.
• 'Kural Açıklamasını Düzenle' bölümündeki alana aşağıdaki formülü girin:
  =VE($C$2””,$C$2=B5)
• Boş veya hatalı hücrelere uygulamak istediğiniz biçimi ayarlayın. Bunu yapmak için Biçim düğmesine tıklayın. Biçimi belirleyebileceğiniz 'Hücreleri Biçimlendir' iletişim kutusunu açacaktır.
• Tamam'ı tıklayın.

Bu kadar! Şimdi C2 hücresine herhangi bir şey girip enter tuşuna bastığınızda, eşleşen tüm hücreleri vurgulayacaktır.

Bu nasıl çalışıyor?

Koşullu biçimlendirmede kullanılan formül, veri kümesindeki tüm hücreleri değerlendirir. Diyelim ki Japonya'ya C2 hücresine girdiniz. Artık Excel, her hücre için formülü değerlendirecektir.

Formül, iki koşul karşılandığında bir hücre için DOĞRU döndürür:

• C2 hücresi boş değil.
• C2 hücresinin içeriği, veri kümesindeki hücrenin içeriğiyle tam olarak eşleşir.

Bu nedenle, Japan metnini içeren tüm hücreler vurgulanır.

Örnek Dosyayı İndirin

Aşağıdakiler gibi varyasyonlar oluşturmak için aynı mantığı kullanabilirsiniz:

• Bir hücre yerine tüm satırı vurgulayın.
• Kısmi bir eşleşme olduğunda bile vurgulayın.
• Yazarken hücreleri/satırları vurgulayın (dinamik) [Bu numaraya bayılacaksınız :)].

Excel'de Koşullu Biçimlendirme Nasıl Kaldırılır

Koşullu biçimlendirme uygulandıktan sonra, siz manuel olarak kaldırmadığınız sürece yerinde kalır. En iyi uygulama olarak, koşullu biçimlendirmeyi yalnızca ihtiyacınız olan hücrelere uygulayın.

Uçucu olduğundan, yavaş bir Excel çalışma kitabına yol açabilir.

Koşullu biçimlendirmeyi kaldırmak için:

• Koşullu biçimlendirmeyi kaldırmak istediğiniz hücreleri seçin.
• Ana Sayfa -> Koşullu Biçimlendirme -> Kuralları Temizle -> Seçili Hücrelerdeki Kuralları Temizle seçeneğine gidin.
  • Tüm çalışma sayfasından koşullu biçimlendirmeyi kaldırmak istiyorsanız, Tüm Sayfadan Kuralları Temizle'yi seçin.

Excel'de Koşullu Biçimlendirme hakkında bilinmesi gereken önemli şeyler

• Uçucu durumda koşullu biçimlendirme. Yavaş bir çalışma kitabına yol açabilir. Sadece gerektiğinde kullanın.
• Koşullu biçimlendirme içeren yapıştırma hücrelerini kopyaladığınızda koşullu biçimlendirme de kopyalanır.
• Aynı hücre grubuna birden çok kural uygularsanız, tüm kurallar etkin kalır. Herhangi bir çakışma olması durumunda, en son uygulanan kural tercih edilir. Ancak, Kuralları Yönet iletişim kutusundan sırayı değiştirerek sırayı değiştirebilirsiniz.