Menambahkan pemformatan bersyarat di Excel memungkinkan Anda menerapkan opsi pemformatan berbeda ke sel, atau rentang sel, yang memenuhi kondisi tertentu yang Anda tetapkan. Menyetel kondisi seperti itu dapat membantu mengatur spreadsheet Anda dan mempermudah pemindaian. Opsi pemformatan yang dapat Anda gunakan mencakup perubahan warna font dan latar belakang, gaya font, batas sel, dan menambahkan pemformatan angka ke data.
Excel memiliki opsi bawaan untuk kondisi yang umum digunakan seperti menemukan angka yang lebih besar dari atau kurang dari nilai tertentu atau menemukan angka yang berada di atas atau di bawah nilai rata-rata. Selain opsi prasetel ini, Anda juga dapat membuat aturan pemformatan bersyarat kustom menggunakan rumus Excel.
Petunjuk ini berlaku untuk Excel 2019, 2016, 2013, 2010, dan Excel untuk Microsoft 365.
Menerapkan Beberapa Kondisi di Excel
Anda dapat menerapkan lebih dari satu aturan ke data yang sama untuk menguji kondisi yang berbeda. Misalnya, data anggaran mungkin memiliki kondisi yang ditetapkan yang menerapkan perubahan format saat tingkat pembelanjaan tertentu tercapai, seperti 50%, 75%, dan 100%, dari total anggaran.
Dalam keadaan seperti itu, Excel pertama-tama menentukan apakah berbagai aturan bertentangan, dan, jika demikian, program mengikuti urutan prioritas untuk menentukan aturan pemformatan bersyarat mana yang akan diterapkan ke data.
Menemukan Data yang Melebihi 25% dan Peningkatan 50%
Dalam contoh berikut, dua aturan pemformatan bersyarat khusus akan diterapkan ke kisaran sel B2 hingga B5.
- Aturan pertama memeriksa untuk melihat apakah data di sel A2:A5 lebih besar dari nilai yang sesuai di B2:B5 oleh 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 terlihat 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 dari 25%, warna background cell akan berubah menjadi hijau.
- Jika selisihnya lebih besar dari 50%, warna latar belakang sel akan berubah menjadi merah.
Aturan yang digunakan untuk menyelesaikan tugas ini akan dimasukkan menggunakan kotak dialog New Formatting Rule. Mulailah dengan memasukkan data sampel ke sel A1 hingga C5 seperti yang terlihat pada gambar di atas.
Di bagian akhir tutorial kita akan menambahkan rumus ke sel C2:C4 yang menunjukkan perbedaan persentase yang tepat antara nilai dalam sel A2:A5 dan B2:B5; ini akan memungkinkan kami untuk memeriksa keakuratan aturan pemformatan bersyarat.
Mengatur Aturan Pemformatan Bersyarat
Pertama, kami akan menerapkan pemformatan bersyarat untuk menemukan peningkatan yang signifikan sebesar 25 persen atau lebih.
Fungsinya akan terlihat seperti ini:
=(A2-B2)/A2>25%
- Sorot sel B2 ke B5 di lembar kerja.
- Klik pada tab Home dari pita.
- Klik ikon Conditional Formatting di pita untuk membuka drop-down.
-
Pilih Aturan Baru untuk membuka kotak dialog Aturan Pemformatan Baru.
- Di bawah Pilih Jenis Aturan, klik opsi terakhir: Gunakan rumus untuk menentukan sel mana yang akan diformat.
- Ketik formula yang disebutkan di atas ke dalam ruang di bawah Format nilai di mana rumus ini benar:
- Klik tombol Format untuk membuka kotak dialog. Klik tab Fill dan pilih warna.
- Klik OK untuk menutup kotak dialog dan kembali ke lembar kerja.
- Warna latar belakang sel B3 dan B5 harus berubah menjadi warna yang Anda pilih.
Sekarang, kita akan menerapkan pemformatan bersyarat untuk menemukan peningkatan 50 persen atau lebih besar. Rumusnya akan terlihat seperti ini:
- Ulangi lima langkah pertama di atas.
- Ketik formula yang disediakan di atas pada ruang di bawah Format nilai di mana rumus ini benar:
- Klik tombol Format untuk membuka kotak dialog. Klik tab Fill dan pilih warna yang berbeda dari yang Anda lakukan pada rangkaian langkah sebelumnya.
- Klik OK untuk menutup kotak dialog dan kembali ke lembar kerja.
Warna latar belakang sel B3 harus tetap sama yang menunjukkan bahwa persentase perbedaan antara angka di sel A3 danB3 lebih besar dari 25 persen tetapi kurang dari atau sama dengan 50 persen. Warna latar belakang sel B5 harus berubah ke warna baru yang Anda pilih yang menunjukkan bahwa persentase perbedaan antara angka di sel A5 dan B5 lebih besar dari 50 persen.
Memeriksa Aturan Pemformatan Bersyarat
Untuk memverifikasi bahwa aturan pemformatan bersyarat yang dimasukkan sudah benar, kita dapat memasukkan rumus ke dalam sel C2:C5 yang akan menghitung persentase perbedaan yang tepat antara angka dalam rentangA2:A5 dan B2:B5.
Rumus di sel C2 terlihat seperti ini:
=(A2-B2)/A2
- Klik sel C2 untuk menjadikannya sel aktif.
- Ketik rumus di atas dan tekan tombol Enter pada keyboard.
- Jawaban 10% seharusnya muncul di sel C2, menunjukkan bahwa angka di sel A2 10% lebih besar dari angka di sel B2.
- Mungkin perlu mengubah format pada sel C2 untuk menampilkan jawaban dalam persen.
- Gunakan fill handle untuk menyalin rumus dari cell C2 ke cells C3 ke C5.
- Jawaban untuk sel C3 hingga C5 harus 30%, 25%, dan 60%.
Jawaban dalam sel ini menunjukkan bahwa aturan pemformatan bersyarat akurat karena perbedaan antara sel A3 dan B3 lebih besar dari 25 persen, dan perbedaan antara sel A5 dan B5 lebih besar dari 50 persen.
Sel B4 tidak berubah warna karena perbedaan antara sel A4 dan B4 sama 25 persen, dan aturan pemformatan bersyarat kami menetapkan bahwa persentase yang lebih besar dari 25 persen diperlukan untuk mengubah warna latar belakang.
Urutan Prioritas untuk Pemformatan Bersyarat
Saat Anda menerapkan beberapa aturan ke rentang data yang sama, Excel pertama-tama menentukan apakah aturan tersebut bertentangan. Aturan yang bertentangan adalah aturan di mana opsi pemformatan tidak dapat diterapkan keduanya pada data yang sama.
Dalam contoh kami, aturan bertentangan karena keduanya menggunakan opsi pemformatan yang sama - mengubah warna sel latar belakang.
Dalam situasi di mana aturan kedua benar (perbedaan nilai lebih dari 50 persen antara dua sel) maka aturan pertama (perbedaan nilai lebih besar dari 25 persen) juga benar.
Karena sel tidak dapat memiliki dua latar belakang warna yang berbeda secara bersamaan, Excel perlu mengetahui aturan pemformatan bersyarat mana yang harus diterapkan.
Urutan prioritas Excel menyatakan bahwa aturan yang lebih tinggi dalam daftar di kotak dialog Pengelola Aturan Pemformatan Bersyarat diterapkan terlebih dahulu.
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 berwarna hijau.
Secara default, aturan baru berada di bagian atas daftar; untuk mengubah urutan, gunakan tombol panah Atas dan Bawah di kotak dialog.
Menerapkan Aturan yang Tidak Bertentangan
Jika dua atau lebih aturan pemformatan bersyarat tidak bertentangan, keduanya diterapkan saat kondisi setiap aturan yang diuji menjadi benar.
Jika aturan pemformatan bersyarat pertama dalam contoh kita memformat rentang sel B2:B5 dengan batas oranye alih-alih warna latar belakang oranye, dua aturan pemformatan bersyarat tidak akan konflik karena kedua format dapat diterapkan tanpa mengganggu yang lain.
Pemformatan Bersyarat vs. Pemformatan Reguler
Dalam kasus konflik antara aturan pemformatan bersyarat dan opsi pemformatan yang diterapkan secara manual, aturan pemformatan bersyarat selalu diutamakan dan akan diterapkan daripada opsi pemformatan yang ditambahkan secara manual.
