Menambahkan pemformatan bersyarat di Excel memungkinkan Anda menerapkan berbagai opsi pemformatan ke sel atau rentang sel yang memenuhi ketentuan khusus yang Anda tetapkan. Opsi pemformatan hanya diterapkan ketika sel yang dipilih memenuhi ketentuan yang ditetapkan ini.
Opsi pemformatan yang dapat diterapkan termasuk perubahan warna font dan latar belakang, gaya font, batas sel, dan penambahan pemformatan angka ke data.
Excel telah memiliki sejumlah opsi bawaan untuk kondisi yang umum digunakan seperti mencari angka yang lebih besar dari atau kurang dari nilai tertentu atau menemukan angka yang di atas atau di bawah nilai rata-rata.
Selain opsi-opsi pra-set, juga dimungkinkan untuk membuat aturan pemformatan bersyarat kustom menggunakan rumus Excel untuk menguji kondisi yang ditentukan pengguna.
01 04Menerapkan Beberapa Aturan di Excel
Lebih dari satu aturan dapat diterapkan ke data yang sama untuk menguji berbagai kondisi. Misalnya, data anggaran mungkin memiliki ketentuan yang berlaku yang menerapkan perubahan format ketika level tertentu, seperti 50%, 75%, dan 100%, dari total anggaran dibelanjakan.
Dalam keadaan seperti itu, Excel pertama kali menentukan apakah berbagai aturan konflik, dan, jika demikian, program mengikuti urutan prioritas yang ditetapkan untuk menentukan aturan pemformatan bersyarat yang diterapkan ke data.
Menemukan Data yang Melebihi 25% dan 50% Meningkat
Dalam contoh berikut, dua aturan pemformatan bersyarat kustom akan diterapkan ke rentang sel B2 untuk B5.
- Aturan pertama memeriksa untuk melihat apakah data dalam sel A2 untuk A5 lebih besar dari nilai yang sesuai di B2 untuk B5 lebih dari 25%.
- Aturan kedua memeriksa untuk melihat apakah data yang sama di A2: A5 melebihi nilai yang sesuai di B2: B5 lebih dari 50%.
Seperti dapat dilihat pada gambar di atas, jika salah satu dari kondisi di atas benar, warna latar belakang sel atau sel dalam rentang B1: B4 akan berubah.
- Untuk data yang selisihnya lebih besar dari 25%, warna latar belakang sel akan berubah menjadi hijau.
- Jika perbedaannya lebih besar dari 50% warna latar belakang sel akan berubah menjadi merah.
Aturan yang digunakan untuk menyelesaikan tugas ini akan dimasukkan menggunakan format kondisional Aturan Pemformatan Baru kotak dialog. Mulailah dengan memasukkan data sampel ke sel A1 hingga C5 seperti yang terlihat pada gambar di atas
Dalam langkah ketiga tutorial akan menambahkan rumus ke sel C2: C4 yang menunjukkan persentase perbedaan yang tepat antara nilai-nilai dalam sel A2: A5 dan B2: B5 untuk memeriksa keakuratan aturan pemformatan bersyarat.
02 04Menetapkan Aturan Pemformatan Bersyarat
Seperti disebutkan, aturan pemformatan bersyarat yang memeriksa dua kondisi akan dimasukkan menggunakan pemformatan bersyarat Aturan Pemformatan Baru kotak dialog. Mari kita mulai dengan mengatur pemformatan bersyarat kustom kami:
Pemformatan Bersyarat untuk menemukan peningkatan lebih dari 25 Persen
= (A2-B2) / A2> 25%
- Menyoroti sel B2 untuk B5 di lembar kerja.
- Klik pada Tab Beranda dari pita.
- Klik pada Pemformatan Bersyarat ikon di pita untuk membuka drop-down.
- Memilih Aturan baru untuk membuka Aturan Pemformatan Baru kotak dialog.
- Dari Gaya: kotak, pilih Klasik pilihan.
- Di tarik-turun kedua, klik pada opsi terakhirGunakan rumus untuk menentukan sel mana yang akan diformat.
- Ketik rumus disediakan di atas ke ruang yang disediakan.
- Klik pada Format dengan: tarik-turun lalu pilih Isi Merah Muda dengan Teks Merah Tua pilihan.
- Klik baik untuk menutup kotak dialog dan kembali ke lembar kerja.
- Pada titik ini, warna latar belakang sel B3 dan B5 harus berwarna hijau.
Pemformatan Bersyarat untuk Menemukan Peningkatan Lebih Besar dari 50 Persen
= (A2-B2) / A2> 50%
Menyoroti sel B2 untuk B5 di lembar kerja.
- Klik pada Tab Beranda dari pita.
- Klik pada Pemformatan Bersyarat ikon di pita untuk membuka drop-down.
- Memilih Aturan baru untuk membuka Aturan Pemformatan Baru kotak dialog.
- Dari Gaya: kotak, pilih Klasik pilihan.
- Di tarik-turun kedua, klik pada opsi terakhirGunakan rumus untuk menentukan sel mana yang akan diformat.
- Ketik rumus disediakan di atas ke ruang yang disediakan.
- Klik pada Format dengan: tarik-turun lalu pilih Isi Hijau Muda dengan Teks Hijau Tua pilihan.
- Klik baik untuk menutup kotak dialog dan kembali ke lembar kerja.
Warna latar belakang sel B3 masih harus hijau menunjukkan bahwa perbedaan persen antara angka dalam sel-sel A3 dan B3 lebih besar dari 25 persen tetapi kurang dari atau sama dengan 50 persen. Warna latar belakang sel B5 harus berubah menjadi merah menunjukkan bahwa perbedaan persen antara angka dalam sel-sel A5 dan B5 lebih besar dari 50 persen.
03 04Memeriksa Aturan Pemformatan Bersyarat
Untuk memeriksa apakah aturan pemformatan bersyarat yang dimasukkan sudah benar, kita dapat memasukkan rumus ke dalam sel C2: C5 yang akan menghitung perbedaan persen yang tepat antara angka-angka dalam rentang A2: A5 dan B2: B5.
= (A2-B2) / A2
- Klik sel C2 untuk membuatnya menjadi sel aktif.
- Ketik rumus di atas dan tekan Memasukkan kunci pada keyboard.
- Jawabannya 10% seharusnya muncul sel C2, menunjukkan bahwa angka dalam sel A2 10% lebih besar dari angka di sel B2.
- Mungkin diperlukan untuk mengubah pemformatan sel C2 untuk menampilkan jawaban sebagai persen.
- Menggunakan isi pegangan untuk menyalin formula dari sel C2 untuk sel C3 untuk C5.
- Jawabannya untuk sel C3 untuk C5 harus 30%, 25%, dan 60%.
Jawaban dalam sel-sel ini menunjukkan bahwa aturan pemformatan bersyarat yang dibuat sudah benar sejak perbedaan sel-sel A3 dan B3 lebih besar dari 25 persen dan perbedaan antara sel-sel A5 dan B5 lebih besar dari 50 persen.
Sel B4 tidak berubah warna karena perbedaan antara sel A4 dan B4 sama dengan 25 persen, dan aturan pemformatan bersyarat kami menetapkan bahwa persentase yang lebih besar dari 25 persen diperlukan untuk warna latar belakang berubah menjadi hijau.
04 04Urutan Diutamakan untuk Pemformatan Bersyarat
Ketika beberapa aturan diterapkan pada rentang data yang sama, Excel terlebih dahulu menentukan apakah aturan tersebut bertentangan. Aturan yang bertentangan adalah aturan di mana opsi pemformatan yang dipilih untuk setiap aturan tidak dapat diterapkan ke data yang sama.
Dalam contoh yang digunakan dalam tutorial ini, aturan konflik karena kedua aturan menggunakan opsi pemformatan yang sama - yaitu mengubah warna sel latar belakang.
Dalam situasi di mana aturan kedua adalah benar (perbedaan nilai lebih besar dari 50 persen antara dua sel) maka aturan pertama (perbedaan nilai yang lebih besar dari 25 persen) juga benar.
Urutan Excel dari Presedensi
Karena sel tidak dapat memiliki latar belakang merah dan hijau pada saat yang sama, Excel perlu mengetahui aturan pemformatan bersyarat yang harus diterapkan.
Aturan mana yang diterapkan ditentukan oleh urutan prioritas Excel, yang menyatakan bahwa aturan yang lebih tinggi dalam daftar di kotak dialog Manajer Aturan Pemformatan Bersyarat memiliki preseden.
Seperti yang ditunjukkan pada gambar di atas, aturan kedua yang digunakan dalam tutorial ini lebih tinggi dalam daftar dan, oleh karena itu, lebih diutamakan daripada aturan pertama. Akibatnya, warna latar belakang sel B5 berubah menjadi merah.
Secara default, aturan baru ditambahkan ke bagian atas daftar dan, karena itu, memiliki prioritas yang lebih tinggi. Untuk mengubah urutan diutamakan gunakan Naik dan turun tombol panah di kotak dialog seperti yang diidentifikasi pada gambar di atas.
Menerapkan Aturan Non-Konflik
Jika dua atau lebih aturan pemformatan bersyarat tidak bertentangan, keduanya diterapkan saat kondisi setiap aturan sedang diuji menjadi benar.
Jika aturan pemformatan bersyarat pertama dalam contoh kami memformat rentang sel B2: B5 dengan batas hijau sebagai pengganti warna latar belakang hijau, dua aturan pemformatan bersyarat tidak akan konflik karena kedua format dapat diterapkan tanpa mengganggu yang lain.
Hasil dari, sel B5 akan memiliki batas hijau dan warna latar belakang merah, karena perbedaan antara angka-angka dalam sel-sel A5 dan B5 lebih besar dari 25 dan 50 persen.
Pemformatan Bersyarat vs. Pemformatan Biasa
Dalam kasus konflik antara aturan pemformatan bersyarat dan opsi pemformatan yang diterapkan secara manual, aturan pemformatan bersyarat selalu didahulukan dan akan diterapkan sebagai pengganti opsi pemformatan yang ditambahkan secara manual.
Jika warna latar belakang kuning pada awalnya diterapkan sel B2 untuk B5 dalam contoh, begitu aturan pemformatan bersyarat ditambahkan, saja sel B2 dan B4 akan tetap kuning. Karena aturan pemformatan bersyarat yang dimasukkan berlaku untuk sel B3 dan B5, warna latar belakang mereka akan berubah dari kuning ke hijau dan merah masing-masing.
