Pemformatan Bersyarat dengan Rumus di Excel
Secara default, excel telah memberi kami banyak jenis pemformatan bersyarat yang tersedia untuk dilakukan dengan data kami, tetapi jika ada pengguna yang ingin pemformatan dilakukan pada kriteria atau rumus tertentu, kami dapat melakukannya di tab beranda bagian pemformatan bersyarat saat kami klik pada aturan baru kita akan menemukan opsi untuk pemformatan bersyarat dengan rumus di mana kita dapat menulis rumus yang akan menentukan sel untuk diformat.
Pemformatan bersyarat dengan Formula tidak lain adalah mengubah format sel berdasarkan kondisi atau kriteria yang diberikan oleh pengguna. Anda mungkin telah menggunakan pemformatan bersyarat untuk menyorot nilai teratas dalam rentang, juga menyorot nilai duplikat.
Hal yang baik adalah kami memiliki rumus dalam format kondisional di excel. Semua rumus harus logis; hasilnya TRUE atau FALSE. Jika tes logika lulus, maka kita akan mendapatkan format bersyarat, dan jika tes logika excel gagal, maka kita tidak akan mendapatkan apa-apa.
Gambaran
Izinkan saya memperkenalkan Anda pada pemformatan bersyarat dengan jendela rumus.
Di bagian artikel di bawah ini, kita akan melihat berbagai macam contoh untuk menerapkan pemformatan bersyarat dengan rumus.
Bagaimana Cara Menggunakan Pemformatan Bersyarat Excel dengan Rumus?
# 1 - Sorot Sel yang Memiliki Nilai Kurang dari 500
Asumsikan di bawah ini adalah angka penjualan yang Anda kerjakan, dan di bawah ini adalah harga jual per unit.
- Langkah 1: Buka Pemformatan Bersyarat dan klik Aturan Baru.
- Langkah 2: Sekarang pilih " Gunakan rumus untuk menentukan sel mana yang akan diformat".
- Langkah 3: Sekarang, di bawah “ Format values where a formula is true,” terapkan rumus sebagai A2 <500 dan kemudian klik Format untuk menerapkan pemformatan excel.
- Langkah 4: Pilih format sesuai keinginan Anda.
- Langkah 5: Sekarang, Anda dapat melihat pratinjau format di kotak pratinjau.
- Langkah 6: Sekarang, klik OK untuk menyelesaikan pemformatan. Sekarang telah menyorot semua sel yang memiliki angka <500.
# 2 - Sorot Satu Sel Berdasarkan Sel Lain
Kami dapat menyorot satu sel berdasarkan nilai sel lain. Asumsikan kita memiliki data Nama Produk dan Harga Produk di dua kolom pertama.
Dari tabel di atas, kita perlu menyoroti produk jika harga jual> 220.
- Langkah 1: Pilih rangkaian produk dan kemudian, Pergi ke format bersyarat dan klik Aturan Baru.
- Langkah 2: Di rumus, terapkan rumus sebagai B2> 220.
- Langkah 3: Klik pada tombol Format dan terapkan format sesuai keinginan Anda.
- Langkah 5: Klik OK; kami memiliki format yang siap.
# 3 - Sorot Semua Sel Kosong di Rentang
Asumsikan di bawah ini adalah data yang Anda miliki.
Pada data di atas, Anda harus menyorot semua sel, yang kosong atau kosong. Kita perlu menggunakan rumus ISBLANK dalam format bersyarat Excel.
Terapkan rumus di bawah ini di bagian rumus.
Pilih format sesuai keinginan Anda.
Klik ok itu akan menyorot semua sel kosong dalam kisaran yang dipilih.
# 4 - Gunakan Fungsi AND untuk Menyoroti Sel
Pertimbangkan data di bawah ini untuk rumus ini.
Jika wilayahnya Tengah dan nilai penjualan> 500, kita perlu menyorot baris lengkapnya.
- Langkah 1: Pilih seluruh data terlebih dahulu.
- Langkah 2: Di sini, kita perlu menggunakan fungsi AND excel karena kita perlu menguji dua kondisi di sini, dan kedua kondisi tersebut harus TRUE. Terapkan rumus di bawah ini.
- Langkah 3: Klik Format.
- Langkah 4: Pergi ke FILL dan pilih warna dan efek yang diinginkan.
- Langkah 5: Klik, OK. Kami akan melihat baris disorot jika kedua kondisinya BENAR.
# 5- Gunakan Fungsi ATAU untuk Menyoroti Sel
Perhatikan data di bawah ini sebagai contoh.
Dalam rentang data di atas, saya ingin menyoroti nama kota Bangalore dan Mysore.
- Langkah 1: Pilih datanya terlebih dahulu dan pergi ke Conditional Formatting, lalu klik New Rule.
- Langkah 2: Karena kita perlu menyorot salah satu dari dua sel nilai, terapkan fungsi OR excel.
- Langkah 3: Klik pada format dan pilih format yang diinginkan.
- Langkah 4: Setelah pemformatan diterapkan, klik OK untuk menyelesaikan tugas. Formula akan menyorot semua sel yang memiliki nilai Bangalore & Mysore.
# 6 - Gunakan Fungsi COUNTIF untuk Menyoroti Sel
Asumsikan Anda bekerja dengan database pelanggan. Anda perlu mengidentifikasi semua klien yang namanya muncul lebih dari lima kali dalam daftar. Berikut datanya.
Pilih data dan terapkan rumus COUNTIF di bagian rumus.
Klik pada format dan terapkan pemformatan yang diperlukan.
Sekarang rumus akan menyorot semua nama jika hitungannya lebih dari 5.
Amber adalah satu-satunya nama yang muncul lebih dari lima kali dalam daftar.
# 7 - Sorot Setiap Baris Alternatif
Kami juga dapat memiliki rumus untuk menyorot setiap baris alternatif di excel data. Asumsikan di bawah ini adalah data yang sedang Anda kerjakan.
Pilih data dan terapkan rumus di bawah ini.
= MOD (BARIS (), 2)
Terapkan ke format sesuai keinginan Anda dan klik OK; kami akan memiliki setiap baris alternatif yang disorot oleh rumus.
# 8 - Sorot Setiap Kolom Alternatif
Seperti bagaimana kami menyorot setiap baris alternatif dengan cara yang sama, kami dapat menyorot setiap kolom alternatif. Pertimbangkan data yang sama sebagai contoh.
Terapkan rumus di bawah ini.
= MOD (KOLOM (), 2)
Salin rumus di atas dan terapkan di bagian rumus pada pemformatan bersyarat.
Klik OK; itu akan menyoroti baris alternatif.
Jika Anda ingin meninggalkan kolom pertama dan menyorot dari kolom kedua, Anda perlu menggunakan rumus di bawah ini.
Ini akan menyoroti kolom alternatif mulai dari kolom kedua.
Hal-hal untuk diingat
- Pemformatan bersyarat hanya menerima rumus logika yang hasilnya hanya TRUE atau FALSE.
- Pratinjau pemformatan bersyarat hanyalah indikasi bagaimana pemformatan terlihat.
- Jangan pernah menggunakan referensi absolut seperti pada rumus. Saat Anda menerapkan rumus, dan jika Anda memilih sel secara langsung, itu akan menjadikannya sebagai referensi absolut, Anda harus membuatnya menjadi referensi relatif di excel.
