Excel Koşullu Biçimlendirme Kullanım Kılavuzu [20 örnek ve şablon içerir]20 min read

Bu makalede excel koşullu biçimlendirme kullanımını ve örneklerini okuyacaksınız.

Koşullu biçimlendirme hücreleri belli koşula göre topluca biçimlendirme aracıdır.

Koşullu biçimlendirme, aynı zamanda excel’de bulunan bir veri görselleştirme aracıdır.

Bunu, renkleri veya diğer araçları (simgeler, çubuklar gibi) kullanarak bizimle ilgili hücreleri veya aralıkları vurgulamak ve farklılaştırmak için kullanırız.

Koşullu Biçimlendirme Ne işe yarar?

Koşullu biçimlendirme Excel kullanıcıları için çok çeşitli olanaklar sunar.

İlk ve en önemli işlevi, dikkati en önemli veri noktalarına yönlendirmektir.

Ya da bizim için en önemli olanları kurallarla belirleyebiliriz.

Bu veri noktaları son teslim tarihleri, mükemmel satış sonuçları ya da yüksek risk taşıyan görevler olabilir.

Koşullu Biçimlendirmenin Temelleri

Excel koşula bağlı biçimlendirmeyi iki çeşit yapabilirsiniz.

• Hazır şablonlar
• Kendi kurallarınız

Önceden Tanımlanmış Koşullar (yerleşik ön ayarlar): Bu seçenek yeni başlayanların ilgisini çekebilir. Excel’de birçok yerleşik kural ve koşul mevcuttur.

Koşullu biçimlendirmenizi hızlıca yapmanız için hazır şablonlar eklemiştir.

• İlk 10
• Büyüktür
• Küçüktür
• Eşittir
• ortalamanın üstünde
• ortalamanın altında vb.

Bu koşullar en çok kullanılan koşullardır. Eğer bu koşulların dışında bir ihtiyacınız var ise formül kullanarak kendi koşulunuzu oluşturabilir ve hücreleri biçimlendirebilirsiniz.

Formüller ise şöyle çalışır; Her bir koşullu biçimlendirme kuralı basit bir mantığa dayanır.

Eğer “X” kriteri doğruysa, “Y” kuralını uygulayın.

Basit bir örnek görelim:

• “X” kriterleri; “Satış fiyatı 50$’dan fazla olanlar.”
• “Y” kriteri, geçerli tüm hücrelerin kırmızı renkte olması.
• Formülü uyguladığınızda satış fiyatının 50 $’dan fazla olduğu her hücre kırmızı renkte olacaktır.

Koşullu biçimlendirme hazır şablonlar

1- Renk ölçekleri uygulama örneği

Hazır şablonların kullanımı oldukça basittir. Önce bir tane örnekle başlayalım.

Önceden ayarlanmış koşullu biçimlendirmeyi kullanmak için:

• Koşullu biçimlendirme kuralı için istediğiniz hücreleri seçin.
• Koşullu Biçimlendirme komutuna tıklayın. Bir açılır menü görünecektir.
• Fareyi istediğiniz ön ayarın üzerine getirin, ardından görüntülenen menüden bir ön ayar stili seçin.

Koşullu biçimlendirme seçilen hücrelere uygulanacaktır.

Yukarıda gördüğünüz en basit ısı haritasıdır.

Örnek excel dosyasını indir (Satış ekibi ısı haritası)

2- Hücre vurgulama kuralları;

Koşullu biçimlendirme verilerinize vurgulama kuralları uygulamanıza olanak tanır. Vurgulama kuralları, yerine getirildiği takdirde hücreyi belirli bir renkle vurgulayan koşullardır

Örneğin;

• belirli bir $ tutarının üzerindeyse yeşil, altındaysa kırmızı
• “___ içeren Metin”
• “Yinelenen Değerler”
• “…’den büyük” gibi çok sayıda koşul için vurgulama kuralları oluşturabilirsiniz.

3- İlk/son kuralları

Ardından, belirli koşullara bağlı olarak hücrelerin alt veya üst aralığını vurgulamanıza olanak tanıyan Üst/Alt kurallarımız vardır. Bu biçimlendirmeyi, üst %10, alt %10, ortalamanın üstü gibi ölçütlere dayalı olarak bir hücre aralığı için ayarlayabilirsiniz.

4- Veri çubukları

Ayrıca, veri çubukları ve renk ölçekleri, verilerinizi görsel bir çubuk veya renk ölçeği ile göstermenize olanak tanıyan diğer önceden ayarlanmış koşullu biçimlendirme özellikleridir. Her durumda, çubuğun uzunluğu veya çubuğun rengi, biçimlendirme aralığındaki diğer hücrelere kıyasla hücrenin değerini temsil eder.

5- Simge kümeleri

Son olarak, Koşullu Biçimlendirme bize simge setleri de sağlar. Verilerdeki değerlere dikkat çekmek için belirli simgeler veya göstergeler ayarlayabiliriz.

Excel koşullu biçimlendirme hazır şablonlar ile örnek uygulamalar

1. Kuralları kullanarak Hücreleri Vurgulama (miktar 200’den büyükse)

Kalıpları ve eğilimleri koşullu biçimlendirme ile vurgulamak için Hücreleri Vurgula Kuralları işlevlerine tıklayın. Ölçütlerinizi karşılayan hücreleri tanımlamak kolay olacaktır. Bu, hücreler ve aralıklar için temel bir renk biçimlendirme yöntemidir.

Kural kullanmak istediğiniz aralığı seçin ve vurgulama kurallarını uygulayın.

Örneğimizde, miktarı 200 birimden fazla olan tüm ürünleri vurgulayacağız. Örneğin, ‘D5: D17’ aralığını seçin.

Şeridin Giriş sekmesinde -> Koşullu Biçimlendirme > Hücre Kurallarını Vurgula -> Büyüktür seçin.

Şimdi, bir iletişim kutusu görünecektir. Soldaki kutuya 200 girin. Yani, 200 girerseniz, değer 200’den büyük olduğunda bir şey olacaktır. Ama ne olacak? Tetikleyiciyi sağ kutuda tanımlayacaksınız.

Örnek excel dosyasını indir (Ürün kodu)

2. Bir sayıdan büyük veya küçük olan hücreleri biçimlendirme

Bu örnekte, 75’ten büyük bir test puanı içeren tüm hücreleri biçimlendirmek istiyorum.

• Test puanlarını içeren hücre aralığını seçin
• Ana Sayfa sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Hücre Kurallarını Vurgula’ya tıklayın
• Büyüktür’e tıklayın
• 75 sayısını girin
• Bir biçimlendirme seçeneği belirleyin
• Tamam’a tıklayın Excel, 75 veya üzeri test puanı içeren tüm hücreleri biçimlendirecektir.

Hücrelerin hangi değere göre biçimlendirileceğini girin

Aynı yöntemi, Hücreleri Vurgula Kuralları açılır menüsünden Küçüktür menüsünü seçerek belirli bir sayıdan daha düşük test puanlarını bulmak için de kullanabilirsiniz.

Örnek excel dosyasını indir (Ders notları)

3. Metin içeren hücreleri biçimlendirme

Hücreler, içlerinde bulunan metne göre biçimlendirilebilir.

Örneğin, bu aralıkta, C sütununda ‘Beatles’ sözcüğünü içeren tüm hücreleri biçimlendirmek istiyorum.’

• B sütununu seçin
• Ana Sayfa sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Hücre Kurallarını Vurgula’ya tıklayın
• İçeren Metne Tıklayın
• ‘Beatles’ metnini girin
• Bir biçimlendirme seçeneği belirleyin
• Tamam’a tıklayın

Bir aralıktaki belirli tarihleri ve yinelenen değerleri bulmak için aynı yöntemi kullanabilirsiniz.

Örnek excel dosyasını indir (Sanatçı ve türler)

4. Metin içeren hücreleri vurgulama

C sütununda tüm “M tiplerini” arıyorsanız, ekranı saatlerce taramak zorunda değilsiniz. Bunun yerine, koşullu biçimlendirmenin tüm kirli işleri yapmasına ve ölçütleri kolayca ve otomatik olarak vurgulamasına izin verebilirsiniz.

Metin içeren aralığı seçin. Bu örnekte, C5:C17 aralığını, yani tüm ‘Ürün Adı’nı seçeceksiniz.

Koşullu Biçimlendirme’ye tıklayın, fareyi Hücreleri Vurgula Kuralları’nın üzerine getirin ve “İçerdiği Metin..” seçin.

Metin kutusuna “Tip M” girin. Ardından, açılır menüyü kullanarak koyu sarı metinle sarı dolgu rengini seçin ve ardından Tamam’a tıklayın.

5. Yalnızca en üst veya en alt sıradaki değerleri biçimlendirin

Belirlediğiniz ölçütlere göre bir hücre aralığındaki en yüksek ve en düşük değerleri bulabilirsiniz. Bu örnekte, hangi öğrencilerin tarih alanında ilk %10’da yer aldığını bulmak istiyorum.

• Tarih sütununu seçin
• Giriş sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Üst/Alt Kurallar’a tıklayın
• İlk %10’u Tıklayın
• Gerekirse yüzdeyi ayarlamak için kaydırma oklarını kullanın
• Tamam’a tıklayın

Hücreleri listede belirtilenlerden farklı renkler kullanarak biçimlendirmek için Özel Biçim’e tıklayın.

Hücrelerin biçimlendirilmesi gereken % Yüzde değerini girin. Tercih ettiğiniz biçimlendirme stiliyle devam edin.

Aynı yöntemi alttaki %10’u, üstteki/alttaki öğeleri ve ortalamanın üstünde veya altında olanları biçimlendirmek için de kullanabilirsiniz.

6. Veri çubuklarını kullanarak hücreleri biçimlendirme

Veri çubukları, hücrelerdeki değerleri diğer değerlere göre görselleştirmenize yardımcı olur. Veri çubuğunun uzunluğu hücredeki değeri temsil eder. Çubuk ne kadar uzunsa, değer o kadar yüksektir.

• Bir hücre aralığı seçin
• Ana Sayfa sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Veri Çubuklarına Tıklayın

Bu örnekte, Matematik ve Fizik sütunlarına Degrade Dolgu, İngilizce ve Tarih sütunlarına ise Düz Dolgu uyguladım.

Veri Çubukları bölümünün altında Degrade dolgu veya Düz Dolgu seçeneklerinden birini seçin.

*Bonus;

Veri çubuklarını sadece çubuk olarak görmek istiyorsanız ve  hücrede gözüken rakamları görmek istemiyorsanız koşullu biçimlendirme kuralları yönet bölümünden kaldırabilirsiniz.

Önce çubuklu verileri seçin

Ardından koşullu biçimlendirme -> kuralları yönete tıklayın

Karşınıza bir pencere açılacak

Bu pencereden kuralı düzenleye tıklayın.

Karşınıza çıkan yeni kutucukta yalnızca çubuğu göstere tıklayın.

Tamama tıklayın.

Yukarıdaki gibi tüm rakamlar artık gösterilmeyecektir.

7. Renk ölçeklerini kullanarak hücreleri biçimlendirme

Renk ölçekleri, verilerin nasıl dağıldığını anlamanıza yardımcı olan görsel kılavuzlardır. Isı haritası gibi, renk ölçekleri de verilerdeki varyasyonları görselleştirmenize yardımcı olur. Bir veri aralığına iki renkli ölçekler veya üç renkli ölçekler uygulayabilirsiniz ve her hücredeki değer, seçtiğiniz renk ölçeğine bağlı olarak iki veya üç renkle temsil edilir.

• Bir hücre aralığı seçin
• Ana Sayfa sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Renk Ölçekleri’ne tıklayın

Bu örnekte iki renkli bir ölçek kullandım. Yüksek değerler sarı, düşük değerler ise yeşil renktedir.

Renk Ölçekleri bölümü altında görüntülemek istediğiniz renk ölçeği türünü seçin

8. Simge kümesi kullanarak hücreleri biçimlendirme

Simge kümeleri, verileri üç ila beş kategoride gruplamak veya sınıflandırmak için kullanılır. Her simgenin bir değer aralığını temsil ettiği bir hücre aralığındaki değerleri görselleştirmeyi kolaylaştırırlar.

Örneğin, bir hücre aralığına uygulamak için 3 simgeli bir set seçerseniz, Excel seçili hücrelerdeki değerlere bakar ve 67. ve 33. yüzdelik dilimleri hesaplar. Ardından, değerin hangi 3’lük dilime düştüğüne bağlı olarak uygun bir simge atar.

• Bir hücre aralığı seçin
• Ana Sayfa sekmesine tıklayın
• Koşullu Biçimlendirme’ye tıklayın
• Simge Setlerini Tıklayın

Bu örnekte, ‘3 Trafik Işığı’ simge setini seçtim. Excel

• kırmızı trafik ışığını en düşük üçte birlik dilime giren değerlere,
• turuncu trafik ışığını orta üçte birlik dilime giren değerlere ve
• yeşil trafik ışığını en yüksek üçte birlik dilime giren değerlere atadı.

Simge setleri bölümünde görüntülenecek simge seti türünü seçin ve koşulları ayarlamaya devam edin.

Koşullu Biçimlendirme ve Formüller

Bu bölümde koşullu biçimlendirmede özel formüller kullanmayı okuyacaksınız.

Formüller Excel koşullu biçimlendirme ile kullanılabilir. Biçimlendirme ölçütlerini belirtmek için mantıksal bir formül kullanabilir ve kendi koşullu biçimlendirme kuralınızı oluşturabilirsiniz.

Tüm örnekler birkaç basit adıma dayanmaktadır:

• Biçimi uygulamak istediğiniz aralığı seçin
• ‘Yeni Kural’ ekleyin
• Formülü girin
• Biçim stilini seçin
• ‘Tamam’a ve ardından ‘Uygula’ya tıklayın.

Hepsi bu kadar.

Aşağıda bir örnek üzerinden şunları öğreneceksiniz

• formülle koşullu biçimlendirme kuralı koymayı
• kuralı revize etmeyi
• iki kural üst üste koymayı
• kuralları silmeyi

Koşullu biçimlendirme formül kurma, düzenleme ve silme örneği;

A- Koşullu biçimlendirme formül kurma

Bu örnekte, seçilen aralıkta tek bir değer içeren tüm hücreleri bulmak ve bu hücreleri mor arka plan dolgusu ve beyaz yazı tipiyle biçimlendirmek için bir formül içeren bir kural oluşturdum. Parantez içindeki hücre başvurusu (C7) TEKMİ işlevi için tek bağımsız değişkendir. Kullanılan hücre referansı her zaman seçilen veri kümesindeki en üst sol hücre olmalıdır.

Biçimlendirmenin gerçekleşmesi gereken formül tabanını girin.

Veri kümesindeki tüm tek sayılar artık biçimlendirilmiştir.

Formül Tabanlı Koşullu Biçimlendirmenin Sonucu şöyledir;

B- Koşullu Biçimlendirme Kuralını Değiştirmek için iki kez oluşturmayın

Bir koşullu biçimlendirme kuralı oluşturduktan sonra, bunu değiştirmek isteyebilirsiniz.

Örneğin, örnekte, ilk 5 tutarı vurgulamak için bir Üst/Alt kuralı oluşturulmuştu. Şimdi, bu Üst/Alt kuralını değiştirmek ve yalnızca ilk 3 tutarı vurgulamak istiyoruz.

Mevcut bir Üst/Alt kuralını veya başka bir tür koşullu biçimlendirme kuralını değiştirmek istiyorsanız, açılır menüdeki Koşullu Biçimlendirme seçenekleri listesinden seçeneği tekrar seçmeyin.

• Bu seçeneği tekrar seçmek mevcut kuralı değiştirmek yerine YENİ bir kural oluşturacaktır.
• Sonuçta tek bir doğru kural yerine birbiriyle çelişen iki kural elde edersiniz

Bir koşullu biçimlendirme kuralını başarıyla değiştirmek için aşağıdaki adımları izleyin. Bu adımlar, Koşullu Biçimlendirme Kuralları Yöneticisi’nde mevcut kuralın nasıl bulunacağını ve orada nasıl değiştirileceğini gösterir.

C- Koşullu biçimlendirme kuralını düzenleme

Bu bölüm size koşullu biçimlendirme kurallarını nasıl yöneteceğinizi gösterecektir. Koşullu biçimlendirme kuralları yöneticisini kullanarak seçili kuralı değiştirebilirsiniz. Bu kurallardan bazılarını daha sonra düzenlemek veya silmek istiyorsanız bu işlevi kullanın.

Bu işleve Giriş Sekmesi ve Koşullu Biçimlendirme düğmesi kullanılarak erişilebilir. Ardından açılır listeden Kuralları Yönet’i seçin.

Varsayılan olarak, bir iletişim kutusu görürsünüz:

Varsayılan olarak, “Biçimlendirme kurallarını göster:” seçeneği “Geçerli Seçim” olarak ayarlanmıştır.

• Ardından, asıl Çalışma Sayfasına uyguladığınız koşullu biçimlendirme kurallarını görüntülemek için açılır listeden “Bu Çalışma Sayfası” seçeneğini belirleyin.
• Üst-alt kurallarını kullanın ve listeden değiştirmek istediğiniz kuralı seçin.
• Ardından, bir kuralı düzenlemek için değiştirmek istediğiniz kurala tıklayın.
• Toplam değer sütunundaki ilk 10 değeri vurgulamak istiyoruz. Şu anda, ilk beş vurgulanmıştır.
• İlk 5 satırına tıklayın! Excel seçilen satırları mavi ile vurgular.
• Ardından, Kuralı Düzenle’ye tıklayın. Kuralın verilen koşullarını değiştirebileceğiniz bir iletişim kutusu açılır; sayı alanına 10 yazın.
• Son olarak, Tamam’a tıklayın.

• Kuralları Yönet kutusuna geri döneceğiz. Değişiklikleri kaydetmek için Tamam’a tıklayın.

D- Koşullu biçimlendirme kuralını silme

Bazen excel dosyanız çok fazla koşullu biçimlendirme kuralına boğulmuş gibi görünür. Bunun nedeni Çok sayıda benzersiz kuralınız varsa, yavaş hesaplamaların nedeni bu olabilir. Bu durumda, sayfadaki bazı kuralları ortadan kaldırmalısınız.

İpucu: Excel, kuralları silmek için hızlı bir yol sağlar.

Giriş sekmesindeki Koşullu Biçimlendirme düğmesine tıklayın ve kuralları temizle’yi seçin.

Ancak bir çalışma sayfasındaki tüm kuralları silmek istemeyebilirsiniz!

Bu durumda, Sütun B’deki (Adları) kuralları silmek istiyoruz. ‘C4:F15’ aralığını seçin.

Bir kuralı düzgün bir şekilde silmek için, ‘Kuralları Yönet’ kutusunu seçin ve kaldırmak istediğiniz koşullu biçimlendirme kuralına tıklayın.

Kuralı silmek için Tamam’a tıklayın.

TÜM Kuralları Silme

Seçili hücrelerden veya sayfanın tamamından tüm koşullu biçimlendirme kurallarını kaldırmak istiyorsanız aşağıdaki adımları izleyin.

• TÜM kuralları silmek istediğiniz hücreleri seçin

• Veya etkin sayfadaki tüm kuralları silmek istiyorsanız, çalışma sayfasındaki herhangi bir hücreyi seçin
• Excel Şeridi’nde Giriş sekmesine tıklayın
• Stiller grubunda, Koşullu Biçimlendirme komutunu tıklatın
• Ardından, seçenekler listesinin alt kısmına yakın bir yerde Kuralları Temizle’ye tıklayın
• Seçeneklerden birine tıklayın:

• Seçili Hücrelerdeki Kuralları Temizle
• VEYA, Kuralları Tüm Sayfadan Temizle

UYARI: Kurallar hiçbir doğrulama mesajı olmadan derhal silinecektir.

İpucu: Kuralları temizleme konusunda fikrinizi değiştirirseniz, başka bir şey yapmadan önce “Geri Al” komutunu (Ctrl+Z) kullanabilirsiniz.

Değerleri karşılaştırmak için formüller (sayılar ve metin)

Bildiğiniz gibi Microsoft Excel, hücreleri belirttiğiniz değerden büyük, küçük veya eşit değerlerle biçimlendirmek için kullanıma hazır birkaç kural sağlar (Koşullu Biçimlendirme >Hücreleri Vurgula Kuralları).

Ancak, bir hücrenin başka bir sütundaki değerine bağlı olarak belirli sütunları veya tüm satırları koşullu olarak biçimlendirmek istiyorsanız bu kurallar çalışmaz. Bu durumda, benzer formüller kullanırsınız:

Koşullu biçimlendirme ile uygulama örnekleri

9. Tek rakamları tespit etmek

Dört kolay adımda formül tabanlı bir koşullu biçimlendirme kuralı oluşturabilirsiniz:

1. Biçimlendirmek istediğiniz hücreleri seçin.

2. Bir koşullu biçimlendirme kuralı oluşturun ve Formül seçeneğini belirleyin

3. DOĞRU veya YANLIŞ döndüren bir formül girin. Tekmi formülü girin

4. Biçimlendirme seçeneklerini ayarlayın ve kuralı kaydedin.

Tekmi işlevi yalnızca tek sayıları bulur ve işaretler:

Örnek excel dosyasını indir (Tek sayılar)

10. Bir aralıktaki değerleri vurgulama

Daha iyi analiz edebilmek için genellikle odağımızı küçük bir aralığa daraltmak isteriz. Mağaza girişleri örneğine bakalım. Girişlerin 145 ila 160 arasında olduğu tüm günleri vurgulamak istiyorsanız, aşağıdaki gibi yerleşik arasında kuralını kullanabilirsiniz:

• Girişler sütununu seçin
• Koşullu Biçimlendirme > Hücre kurallarını vurgula > Arasında… seçeneğine gidin.
• Aralığı yazın ya da değerleri içeren hücreleri işaret edin.
• Tamamlandı.

Veya bunu şu şekilde de yapabilirsiniz,

Koşullu biçimlendirme -> Yeni kural – > Yalnızca şunu içereni biçimlendir

Örnek excel dosyasını indir (Mağaza girişler)

11. Zaman içindeki değişiklikleri zarif simgelerle görselleştirin

Değişen verileri işaretlemek için tarihlere göre mağaza müşteri girişlerini inceliyoruz.

Mağaza girişleri örneğimize bakalım.  Eğilimi şu şekilde görmek istiyoruz:

12. İki listede eşleşen / eksik öğeleri vurgulayın

Her gün milyonlarca insan “Bu iki listede hangi öğeler ortak?” diye soruyor ve sonra çoğu listeleri karşılaştırmak için birkaç dakika (veya saat) harcıyor. Ancak bu soruyu sadece beş saniye içinde cevaplayabilirsiniz. Çok basit ve zarif.

• İlk listeyi seçin.
• CTRL tuşunu basılı tutun ve ikinci listeyi seçin. Bu, her iki listeyi de vurgular.
• Ana Sayfa > Koşullu Biçimlendirme > Hücre vurgulama kuralları > Yinelenen değerler e gidin
• İşte, her iki listede hangi değerlerin ortak olduğunu anında görebilirsiniz.

Örnek excel dosyasını indir (Basketbol takımı-futbol takımı)

13. Geçmiş son tarihleri vurgulamak için Koşullu Biçimlendirme nasıl kullanılır?

Örnekte “vadesi geçmiş tarihleri” belirlemek için bir formül uygulayacağız.

Formül, tarihler arasındaki farkın belirli bir gün sayısını aşıp aşmadığını kontrol edecektir. Renk kodlu bir tablo oluşturmak için, her aralık için üç koşullu biçimlendirme kuralı kullanın.

E4:E9 aralığındaki hücreleri seçin ve formülleri uygulayın.

1. =(E4-D4)<5 fark 5 günden az ise,
2. =(E4-D4)<15 fark 5 gün ile 15 gün arasındaysa
3. =(E4-D4)>=20 iki tarih arasındaki fark 20’den büyük veya eşitse

Önemli: Kural 1 ve kural 2 için “doğruysa durdur” onay kutusunu kullanmanız gerekir.

Örnek excel dosyasını indir (Tarih farkları)

14. İki koşula bağlı biçimlendirme

Bu listede en az 3 yatak odası olan ancak 3.000.000 TL’nin altında olan mülkleri bulmak için VE işlevine dayalı bir formül kullanabilirsiniz:

=VE($C5<3000000;$D5>=3)

Dolar işaretleri ($) C ve D sütunlarına yapılan referansı kilitler ve VE işlevi her iki koşulun da DOĞRU olduğundan emin olmak için kullanılır. VE fonksiyonunun DOĞRU değerini döndürdüğü satırlarda koşullu biçimlendirme

Örnek excel dosyasını indir (Üç odalı evler)

15. Hücre boşsa koşullu biçim nasıl kullanılır?

Aşağıdaki örnekte, bir veya daha fazla sütundaki değerler boş olduğunda bir sütundaki değerleri vurgulamak istiyoruz.

Boş veya boş hücreleri test etmek için YADA ve EBOŞSA işlevlerine dayalı temel bir formül kullanılır. Örneğin, B4:E12 aralığında karşılık gelen bir satırdaki herhangi bir hücre boşsa, YADA işlevi doğru değerini döndürür.

Böylece, formül çalışır ve F sütunundaki hücre açık mavi kullanılarak vurgulanır.

Koşullu biçimlendirme formülünü kullanın:

=YADA(EBOŞSA($B4);EBOŞSA($C4);EBOŞSA($D4);EBOŞSA($E4))

Örnek excel dosyasını indir (Hücre boşsa)

16. Önümüzdeki 30 gün içindeki tarihleri vurgulamak

Önümüzdeki 30 gün içinde gerçekleşecek tarihleri vurgulamak için,

(1) tarihlerin gelecekte olduğundan ve

(2) tarihlerin bugünden itibaren 30 gün veya daha az olduğundan emin olan bir formüle ihtiyacımız var.

Bunu yapmanın bir yolu, VE işlevini ŞİMDİ işleviyle birlikte aşağıdaki gibi kullanmaktır:

=VE(B4>ŞİMDİ();B4<=(ŞİMDİ()+30))

Makaleyi hazırladığım tarih 27 Mart 2024 olduğundan, koşullu biçimlendirme tarihleri aşağıdaki gibi vurgular:

ŞİMDİ işlevi geçerli tarih ve saati döndürür.

Örnek excel dosyasını indir (Sonraki 30 gün)

17. En yüksek değeri içeren satırı vurgulamak

Örneğin, belirtilen bir sütunda maksimum değeri içeren satırı vurgulamak için MAX Fonksiyonunu kullanabilirsiniz.

=$D4=MAK($D:$D)

Şimdi, yalnızca Sütun D’deki maksimum değere sahip satır vurgulanır.

Örnek excel dosyasını indir (Bölge ve renkler)

18. Tüm satırı biçimlendirmek (hücre toplamlarına göre)

Yukarıdaki tabloda gördüğünüz gibi 5 çalışan için aylık satış rakamlarını kaydettiğinizi ve toplam satış rakamları 100.000 $’dan fazla olan her satırı vurgulamak istediğinizi düşünün.

Bu örnekte, satırdaki değerlerin toplamına bağlı olarak her satırın tamamına Excel Koşullu Biçimlendirme uygulamak istiyoruz.

Başlangıçta biçimlendirilecek satırları seçmemiz gerekir. Yukarıdaki tabloda, 2 – 6. satırlardaki tüm hücreler seçili olmasına rağmen A2 hücresinin etkin hücre olduğuna dikkat edin.

A2 etkin hücre olduğu için, Koşullu Biçimlendirme menüsüne girilen formül A2 hücresine uygulanır.

Örnek excel tablo için, toplamı 100 veya daha fazla olan satırları vurgulamak için formül şöyledir:

=TOPLA($B2:$F2)>100

Yukarıdaki Excel Koşullu Biçimlendirme tanımının sonuçlarını sağdaki örnek excel tabloda görebilirsiniz.

Gerektiği gibi, toplamları 100’ü aşan satırlar yeşil renkte biçimlendirilmiştir.

Örnek excel dosyasını indir (Satışlar ve aylar)

19. Bir hücrenin değerine bağlı olarak tüm satırı vurgulama

Bir hücrenin değerine bağlı olarak tüm satırı vurgulayan bir Excel koşullu biçimlendirme kuralı oluşturabilirsiniz. Özellikle büyük bir veri kümeniz varsa, belirli bir hücre yerine tüm satır vurgulandığında önemli verileri görselleştirmek çok daha kolaydır.

1. B4:F26 aralığını seçin
2. Koşullu Biçimlendirme’ye tıklayın
3. Yeni Kural’a tıklayın
4. Formül kullan öğesini seçin
5. Şu formülü yazın : =$E4=”Emre Çelik”
6. Biçimlendir’e tıklayın
7. İstediğiniz biçimlendirme stilini seçin
8. Tamam’a tıklayın

Bu örnekte, “Emre Çelik” adını içeren tüm satırları vurgulayan bir kural oluşturdum.

Aradığım metin dizesini içeren sütundaki ilk hücre olduğu için $E4 hücre başvurusu kullanıldı. Bu formülü aşağıya kopyaladığınızda, satır numarası (4) kilitlenmez ancak sütun harfi (E) kilitlenir.

Oluşan sonuç şu şekilde;

Örnek excel dosyasını indir (Sıra ve isimler)

20-Tarihi geçenleri işaretleme

Excel koşullu biçimlendirmesini, süresi dolan poliçeleri vurgulamak için kullanabilirsiniz. Bu örnekte, son tarihler B2:B7 hücrelerine girilmiştir.

Geçmiş son kullanma tarihlerine yönelik koşullu biçimlendirmeyi ayarlamak için aşağıdaki adımları izleyin

1.B2:B7 hücrelerini seçin

2.Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın

3.Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın

4.Formül için, süresi dolan tarihleri bulmak üzere Bugün işlevini kullanın:

=B2<BUGÜN()

5.Biçimlendir düğmesine tıklayın.

6.Biçimlendirme seçeneklerini seçin (bu örnekte kırmızı dolgu rengi), Tamam’a tıklayın

7.Tamam’a tıklayın

Örnek excel dosya indir (tarihi geçenleri işaretleme)

21-Piyango rakamlarını eşleştir

Bir piyangoda çekilen bilet numaralarını veya 3 veya daha fazla kazanan numaraya sahip biletleri vurgulamak için Excel koşullu biçimlendirmesini kullanabilirsiniz.

Bu örnekte bilet numaraları C6:H9 hücrelerine, çekilen numaralar ise C3:H3 hücrelerine girilmiştir.

Bir bilet hücresinin değeri, çekilen numaraların bulunduğu hücrelerde bulunursa, bilet numarası hücresi mavi renkte vurgulanacaktır.

Kazanan numaraları vurgulamak için:

1. C6:H9 hücrelerini seçin
2. Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın
3. Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın
4. Formül için CountIf işlevini kullanın:

=EĞERSAY($C$3:$H$3;C6)>=1

5. Biçimlendir düğmesine tıklayın.
6. Biçimlendirme seçeneklerini seçin (bu örnekte mavi dolgu), Tamam’a tıklayın
7. Tamam’a tıklayın

Kazanan biletleri vurgulamak için:

1. B6:B9 hücrelerini seçin
2. Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın
3. Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın
4. Formül için Topla ve Eğersay işlevlerini kullanın:

=Topla(Eğersay($C6:$H6;$C$3:$H$3))>=3

5. Biçimlendir düğmesine tıklayın.
6. Biçimlendirme seçeneklerini seçin (bu örnekte yeşil dolgu), Tamam’a tıklayın
7. Tamam’a tıklayın

Örnek excel dosya indir (piyango rakamlarını tespit et)

22-Formül içeren hücreleri renklendir

Formül içeren hücreleri vurgulamak için Excel koşullu biçimlendirmesini kullanın. Bu örnekte, A2:B8 hücrelerinde değerler ve C2:C8 ve A9:C9 hücrelerinde toplamlar vardır.

1. Biçimlendirmeyi istediğiniz tüm hücreleri seçin — A2:C9 aralığı

2. Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın
3. Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın
4. Formül için şunu girin: =EFORMÜLSE(A2)
5. Biçimlendir düğmesine tıklayın.
6. Vurgulama için bir yazı tipi rengi seçin.
7. Tamam’a tıklayın, Tamam’a tıklayın

Örnek excel dosya indir (formül içeren hücreler)

23-Arama kutusuna yazdıklarınızı vurgulayın

Geçerli iki basamaklı kodlar içeren bir ölçüt listesi gibi, çalışma sayfasındaki farklı bir listeden değerler içeren hücreleri vurgulamak için Excel koşullu biçimlendirmesini kullanın.

Bu ekran görüntüsünde, C sütunundaki bir liste 3 koda sahiptir: AA, BB ve CC. A sütununda, bir koşullu biçimlendirme kuralı sayesinde bu kodlara sahip hücreler yeşil renge dönüştürülür.

1. Vurgulamak istediğiniz öğelerin bir listesini oluşturun. Öğeler koşullu biçimlendirmeden farklı bir sayfadaysa, listeyi adlandırın.
2. A2:A7 aralığını seçin
3. Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın
4. Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın

o NOT: Excel 2003’te, Biçim->Koşullu Biçimlendirme’yi seçin

o Ardından, ilk açılır menüden Formül Şudur’u seçin

5. Formül için şunu girin

=Eğersay($C$2:$C$4;A2)

6. Biçimlendir düğmesine tıklayın.
7. Vurgulama için bir yazı tipi rengi seçin.
8. Tamam’a tıklayın, Tamam’a tıklayın

Örnek excel dosya indir (Arama kutusuna yazdıklarınızı vurgulama)

24-Tamamlanan işlerin üstünü çizin

Üzerinde çalışmanız gereken görevlerin bir listesi varsa, tamamlanan öğelerin üzerini çizmek için koşullu biçimlendirmeyi kullanın. Bu örnekte, tamamlanan görevler “Bitti” sütununda bir X ile işaretlenmiştir.

Listede, tamamlanan öğelerin üzerini çizmek ve yazı tipini açık griye dönüştürmek için koşullu bir biçimlendirme vardır. Bu, hala bitirilmesi gereken görevlere odaklanmayı kolaylaştırır.

Bu koşullu biçimlendirmeyi ayarlamak için aşağıdaki adımları izleyin:

1. A2:C4 hücrelerini seçin
2. Şeridin Giriş sekmesinde Koşullu Biçimlendirme’yi ve ardından Yeni Kural’ı tıklatın
3. Hangi Hücrelerin Biçimlendirileceğini Belirlemek için Formül Kullan’a tıklayın
4. Formül için =$C2<>”” girin.

o Her hücrede formül, o satırdaki C sütununun boş olup olmadığını kontrol eder

5. Hücreleri Biçimlendir iletişim kutusunu açmak için Biçimlendir düğmesine tıklayın.
6. Yazı Tipi sekmesinde Üstü Çizili’yi seçin ve renk olarak açık griyi seçin

7. Tamam’a tıklayın, Tamam’a tıklayın

Koşullu biçimlendirme kuralını ayarladıktan sonra, “Bitti” sütununa herhangi bir şey yazarsanız öğenin üzeri çizilecektir.

Örnek excel dosya indir (tamamlanan işlerin üstünü çizme)

Excel’de Koşullu Biçimlendirmede Uzmanlaşmak için İleri Düzey Kılavuz

Excel’de koşullu biçimlendirme yalnızca hücreleri vurgulamakla ilgili değildir; veri analizi ve sunumunun yeni bir boyutunun kilidini açmakla ilgilidir. Daha derin işlevlerini anlayarak ve diğer Excel özellikleriyle entegre ederek, kullanıcılar verilerine ilişkin benzersiz içgörüler elde edebilir.

Gelişmiş Koşullu Biçimlendirme Teknikleri

1. Veri Görselleştirme Geliştirmeleri: Temel renk kodlamasının ötesinde, koşullu biçimlendirme, verilerinizin görsel özetlerini sağlayan ve hızlı analiz ve karşılaştırmaya olanak tanıyan veri çubukları, renk ölçekleri ve simge setleri oluşturmak için kullanılabilir.
2. Formül Güdümlü Biçimlendirme: Koşullu biçimlendirme kuralları içinde Excel formüllerinden yararlanarak karmaşık kriterleri ve senaryoları ele alabilirsiniz. Bu, yinelenenleri vurgulamayı, veri eğilimlerini görselleştirmeyi ve hatta proje yönetimi için Gantt grafikleri oluşturmayı içerir.
3. Dinamik Aralıklar ve Koşullu Biçimlendirme: Koşullu biçimlendirme ile adlandırılmış aralıklar veya dinamik aralık formülleri kullanmak, biçimlendirme kurallarınızın değişen veri boyutlarına uyum sağlamasını sağlayarak elektronik tablolarınızın ölçeklenebilirliğini ve esnekliğini artırabilir.

Koşullu Biçimlendirmenin Stratejik Uygulamaları

1. Risk Yönetimi ve Hata Tanımlama: Verilerinizdeki aykırı değerleri, hataları veya anormallikleri vurgulamak için koşullu biçimlendirmeyi kullanın. Bu özellikle finansal analiz, kalite kontrol ve hassasiyet ve doğruluk gerektiren tüm alanlarda kullanışlıdır.
2. Proje Takibi ve Yönetimi: Koşullu biçimlendirme, proje zaman çizelgelerini, durumunu (örn. tamamlandı, devam ediyor, ertelendi) ve önceliklerini görsel olarak temsil edebilir, bu da onu proje yöneticileri ve ekipleri için paha biçilmez bir araç haline getirir.
3. Gösterge Tablosu ve Rapor Geliştirme: Dinamik, etkileşimli ve görsel olarak ilgi çekici veri sunumları oluşturmak için Excel panolarına ve raporlarına koşullu biçimlendirme ekleyin. Bu, önemli metrikleri ve eğilimleri vurgulayarak karar verme sürecini önemli ölçüde geliştirebilir.

Koşullu Biçimlendirmeyi Excel Özellikleriyle Bütünleştirme

1. PivotTable’lar: Önemli veri noktalarını vurgulamak, farklı kategorilerdeki performansı karşılaştırmak veya veri eğilimlerini doğrudan özetlenmiş verilerinizin içinde görselleştirmek için PivotTable’larda koşullu biçimlendirme uygulayın.
2. Excel Tabloları: Excel Tabloları ile birlikte koşullu biçimlendirmeden yararlanmak yalnızca görsel çekiciliği artırmakla kalmaz, aynı zamanda tablolar genişledikçe veya daraldıkça tutarlılığı da korur.
3. Excel Grafikleri: Grafikler için doğrudan geçerli olmasa da, grafik oluşturmak için kullanılan veriler, önemli noktaları, eğilimleri veya aykırı değerleri vurgulamak için koşullu olarak biçimlendirilebilir ve dolaylı olarak grafik görselleştirmesini geliştirir.

Koşullu Biçimlendirme için Optimizasyon İpuçları

1. Performansı Yönetme: Özellikle büyük veri kümeleri üzerindeki karmaşık formüllerde koşullu biçimlendirmenin aşırı kullanımı Excel’in performansını etkileyebilir. Kurallarınızı düzenli olarak gözden geçirip düzenleyin ve çok büyük veri kümeleri için alternatif yöntemleri değerlendirin.
2. Kural Yönetimi: Bir çalışma sayfasında uygulanan tüm kurallara genel bir bakış sağlamak için Koşullu Biçimlendirme Kuralları Yöneticisi’ni kullanın. Bu, kural önceliğini yönetmek, mevcut kuralları düzenlemek veya beklenmedik biçimlendirme sonuçlarını gidermek için çok önemlidir.
3. Benzersiz Senaryolar için Özel Formüller: Koşullu biçimlendirme için özel formüller oluşturmaktan çekinmeyin. İster tek bir hücrenin değerine bağlı olarak tüm satırları vurgulamak ister farklı sayfalarda birbirine bağlı koşullu biçimler oluşturmak olsun, özel formüller sınırsız olanaklar sunar.

Geleneksel Kullanımların Ötesini Keşfetmek

1. Etkileşimli Öğeler: Kullanıcı girdisine göre dinamik olarak güncellenen etkileşimli raporlar ve gösterge tabloları oluşturmak için koşullu biçimlendirmeyi Excel’in form denetimleriyle (örn. açılır listeler, kaydırıcılar) birleştirin.
2. Excel İşlevleri ile Entegrasyon: DOLAYLI, TOPLA.ÇARPIM ve DİZİ formülleri gibi gelişmiş Excel işlevleri, koşullu biçimlendirmenin yeteneklerini önemli ölçüde genişleterek son derece spesifik ve dinamik biçimlendirme kuralları sağlar.
3. Görsel Proje Çizelgeleri: Doğrudan Excel içinde Gantt grafiği işlevselliğini taklit ederek proje programlarının, zaman çizelgelerinin ve bağımlılıkların görsel temsillerini oluşturmak için koşullu biçimlendirmeyi kullanın.

Çeşitli örnekler için şu linki deneyebilirsiniz;

https://www.contextures.com/xlcondformat03.html 

Özet olarak

Excel kullanıcısı olarak siz, bu gelişmiş strateji ve teknikleri benimseyerek veri analizi ve sunumuna yaklaşımlarınızı dönüştürebilirsiniz.

Koşullu biçimlendirme, tam olarak kullanıldığında içgörüleri aydınlatabilen, üretkenliği artırabilen ve bilinçli karar verme sürecini yönlendirebilen güçlü bir araçtır.

İster iş analizi, ister proje yönetimi ya da sadece bilgi düzenlemek için veri yönetiyor olun, koşullu biçimlendirmede ustalaşmak Excel’in tüm potansiyelinden yararlanmak için bir adımdır.