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.
Sejak Excel 2007, 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.
Menerapkan Beberapa Aturan
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 tingkat 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.
Contoh: Menemukan Data yang Melebihi 25% dan 50% Meningkat dengan Pemformatan Bersyarat
Dalam contoh berikut, dua aturan pemformatan bersyarat kustom akan diterapkan ke rentang sel B2 hingga B5.
- aturan pertama memeriksa untuk melihat apakah data dalam sel A2 hingga A5 lebih besar daripada nilai yang sesuai dalam B2 hingga B5 oleh lebih dari 25%.
- aturan kedua memeriksa untuk melihat apakah data yang sama dalam A2: A5 melebihi nilai yang sesuai dalam B2: B5 oleh 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 sel akan berubah menjadi biru.
- jika perbedaannya lebih besar dari 50% warna latar belakang sel akan berubah menjadi merah.
Aturan yang digunakan untuk menyelesaikan tugas ini,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%akan dimasukkan menggunakan kotak dialog Aturan Pemformatan Baru bersyarat.
Memasukkan Data Tutorial
- Masukkan data ke sel A1 hingga C5 seperti yang terlihat pada gambar di atas
Catatan: Langkah 3 dari tutorial akan menambahkan rumus ke sel C2: C4 yang menunjukkan persentase perbedaan yang tepat antara nilai dalam sel A2: A5 dan B2: B5 untuk memeriksa keakuratan aturan pemformatan bersyarat.
Menetapkan Aturan Pemformatan Konduktif
Seperti yang disebutkan, aturan pemformatan bersyarat yang memeriksa dua kondisi akan dimasukkan menggunakan kotak dialog Pemformatan Baru yang bersyarat.
Menetapkan pemformatan bersyarat untuk menemukan peningkatan lebih dari 25%
- Sorot sel B2 ke B5 di lembar kerja.
- Klik pada tab Home dari pita.
- Klik pada ikon Pemformatan Bersyarat di pita untuk membuka menu drop down.
- Pilih Aturan Baru untuk membuka kotak dialog Aturan Pemformatan Baru seperti yang terlihat pada gambar di atas.
- Di bagian atas kotak dialog, klik pada opsi terakhir: Gunakan rumus untuk menentukan sel mana yang akan diformat.
- Di bagian bawah kotak dialog, klik di nilai Format di mana rumus ini benar: garis.
- Ketik rumus : = (A2-B2) / A2> 25% di tempat yang disediakan
- Klik pada tombol Format untuk membuka kotak dialog Format Cells.
- Di kotak dialog ini, klik pada tab Isi dan pilih warna biru.
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembar kerja.
- Pada titik ini, warna latar belakang sel B3 dan B5 harus berwarna biru.
Menetapkan pemformatan bersyarat untuk menemukan peningkatan lebih dari 50%
- Dengan sel B2 hingga B5 masih dipilih, ulangi langkah 1 hingga 6 di atas.
- Ketik rumus: = (A2-B2) / A2> 50% di tempat yang disediakan.
- Klik pada tombol Format untuk membuka kotak dialog Format Cells.
- Klik pada tab Isi dan pilih warna isian merah.
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembar kerja .
- Warna latar belakang sel B3 harus tetap berwarna biru yang menunjukkan bahwa perbedaan persen antara angka dalam sel A3 dan B3 lebih besar dari 25% tetapi kurang dari atau sama dengan 50%.
- Warna latar belakang sel B5 harus berubah menjadi merah menunjukkan bahwa perbedaan persen antara angka-angka dalam sel A5 dan B5 lebih besar dari 50%.
Memeriksa Aturan Pemformatan Bersyarat
Menghitung% Selisih
Untuk memeriksa bahwa aturan pemformatan bersyarat yang dimasukkan sudah benar, kita dapat memasukkan rumus ke dalam sel C2: C5 yang akan menghitung selisih persen yang tepat antara angka-angka dalam rentang A2: A5 dan B2: B5.
- Klik pada sel C2 untuk membuatnya menjadi sel aktif.
- Ketik rumus = (A2-B2) / A2 dan tekan tombol Enter pada keyboard.
- Jawaban 10% akan muncul di sel C2, menunjukkan bahwa angka dalam sel A2 adalah 10% lebih besar dari angka di sel B2.
- Mungkin perlu untuk mengubah format pada sel C2 untuk menampilkan jawaban sebagai persen.
- Gunakan gagang isian untuk menyalin rumus dari sel C2 ke sel C3 hingga C5.
- Jawaban untuk sel C3 ke C5 harus: 30%, 25%, dan 60%.
- Jawaban dalam sel ini menunjukkan bahwa aturan pemformatan bersyarat yang dibuat sudah benar karena perbedaan antara sel A3 dan B3 lebih besar dari 25% dan perbedaan antara sel A5 dan B5 lebih besar dari 50%.
- Sel B4 tidak berubah warna karena perbedaan antara sel A4 dan B4 sama dengan 25%, dan aturan pemformatan bersyarat kami menetapkan bahwa persentase yang lebih besar dari 25% diperlukan untuk warna latar belakang berubah menjadi biru.
Urutan Diutamakan untuk Aturan Pemformatan Bersyarat
Menerapkan Aturan Pemformatan Bersyarat yang Bertentangan
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% antara dua sel) maka aturan pertama (perbedaan nilai yang lebih besar dari 25%) juga benar.
Urutan Excel dari Presedensi
Karena sel tidak dapat memiliki latar belakang merah dan biru 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 (= (A2-B2) / A2> 50%) 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 prioritas, gunakan tombol panah Atas dan Bawah 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 (= (A2-B2) / A2> 25%) memformat rentang sel B2: B5 dengan batas biru sebagai ganti warna latar belakang biru, dua aturan pemformatan bersyarat tidak akan konflik sejak kedua format dapat diterapkan tanpa mengganggu yang lain.
Akibatnya, sel B5 akan memiliki perbatasan biru dan warna latar belakang merah, karena perbedaan antara angka-angka dalam 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 pada sel B2 hingga B5 dalam contoh, setelah aturan pemformatan bersyarat ditambahkan, hanya sel B2 dan B4 yang tetap berwarna kuning.
Karena aturan pemformatan bersyarat yang dimasukkan berlaku untuk sel B3 dan B5, warna latar belakang mereka akan berubah dari kuning ke biru dan merah masing-masing.