Yang Perlu Diketahui
- Excel mendukung beberapa opsi yang telah ditentukan sebelumnya seperti tanggal, data duplikat, dan nilai di atas atau di bawah nilai rata-rata rentang sel.
- Anda dapat menerapkan opsi pemformatan yang berbeda seperti warna atau ketika nilai memenuhi kriteria yang telah Anda tetapkan sebelumnya.
Artikel ini menjelaskan lima cara berbeda untuk menggunakan pemformatan bersyarat di Excel. Instruksi berlaku untuk Excel 2019, 2016, 2013, 2010; Excel untuk Mac, Excel untuk Microsoft 365, dan Excel Online.
Cara Menggunakan Pemformatan Bersyarat
Untuk mempermudah pemformatan bersyarat, Excel mendukung opsi prasetel yang mencakup situasi yang umum digunakan, seperti:
- Tanggal
- Data duplikat
- Nilai di atas atau di bawah nilai rata-rata dalam rentang sel
Dalam hal tanggal, opsi yang telah ditentukan sebelumnya menyederhanakan proses pemeriksaan data Anda untuk tanggal yang mendekati tanggal saat ini seperti kemarin, besok, minggu lalu, atau bulan depan.
Jika Anda ingin memeriksa tanggal yang berada di luar opsi yang terdaftar, namun, sesuaikan pemformatan bersyarat dengan menambahkan rumus Anda sendiri menggunakan satu atau beberapa fungsi tanggal Excel.
Periksa Tanggal Jatuh Tempo 30, 60, dan 90 Hari
Sesuaikan pemformatan bersyarat menggunakan rumus dengan menetapkan aturan baru yang diikuti Excel saat mengevaluasi data dalam sel.
Excel menerapkan pemformatan bersyarat dalam urutan atas-ke-bawah seperti yang muncul di Pengelola Aturan Pemformatan Bersyarat kotak dialog.
Meskipun beberapa aturan mungkin berlaku untuk beberapa sel, aturan pertama yang memenuhi kondisi diterapkan ke sel.
Demo ini menggunakan tanggal saat ini, 40 hari sebelum tanggal saat ini, 70 hari sebelum tanggal saat ini, dan 100 hari sebelum tanggal saat ini untuk menghasilkan hasil.
Periksa Tanggal 30 Hari Lewat Jatuh Tempo
Dalam lembar kerja Excel kosong, sorot sel C1 ke C4 untuk memilihnya. Ini adalah rentang di mana aturan pemformatan bersyarat akan diterapkan.
- Pilih Home > Conditional Formatting > New Rule untuk membuka Aturan Pemformatan Baru kotak dialog.
- Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat.
- Dalam Format nilai di mana rumus ini benar kotak teks, masukkan rumus:
- Pilih Format untuk membuka kotak dialog Format Sel.
- Pilih tab Fill untuk melihat pilihan warna isian latar belakang.
- Pilih warna isian latar belakang.
- Pilih tab Font untuk melihat opsi format font.
- Mengatur warna font.
- Pilih OK dua kali untuk menutup kotak dialog dan kembali ke lembar kerja.
- Warna latar belakang sel C1 ke C4 berubah menjadi warna isian yang dipilih, meskipun tidak ada data di dalam sel.
=TODAY()-C1>30Rumus ini memeriksa untuk melihat apakah tanggal di sel C1 hingga C4 lebih dari 30 hari.
Tambahkan Aturan untuk Tanggal Lebih Dari 60 Hari Lewat Jatuh Tempo
Daripada mengulangi semua langkah di atas untuk menambahkan dua aturan berikutnya, gunakan opsi Kelola Aturan untuk menambahkan aturan tambahan sekaligus.
- Sorot sel C1 hingga C4, jika perlu.
- Pilih Home > Conditional Formatting > Kelola Aturan untuk membuka Conditional Formatting Rules Manager kotak dialog.
- Pilih Aturan Baru.
- Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat.
- Dalam Format nilai di mana rumus ini benar kotak teks, masukkan rumus:
- Pilih Format untuk membuka kotak dialog Format Sel.
- Pilih tab Fill untuk melihat pilihan warna isian latar belakang.
- Pilih warna isian latar belakang.
- Pilih OK dua kali untuk menutup kotak dialog dan kembali ke Conditional Formatting Rules Manager kotak dialog.
=TODAY()-C1>60Rumus ini memeriksa apakah tanggal di sel C1 hingga C4 lebih besar dari 60 hari yang lalu.
Tambahkan Aturan untuk Tanggal Lebih Dari 90 hari Lewat Jatuh Tempo
- Sorot sel C1 hingga C4, jika perlu.
- Pilih Home > Conditional Formatting > Kelola Aturan untuk membuka Conditional Formatting Rules Manager kotak dialog.
- Pilih Aturan Baru.
- Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat.
- Dalam nilai Format di mana rumus ini benar, masukkan rumus:
- Rumus ini memeriksa untuk melihat apakah tanggal dalam sel C1 hingga C4 lebih besar dari 90 hari yang lalu.
- Pilih Format untuk membuka kotak dialog Format Sel.
- Pilih tab Fill untuk melihat pilihan warna isian latar belakang.
- Pilih warna isian latar belakang.
- Pilih OK dua kali untuk menutup kotak dialog dan kembali ke Conditional Formatting Rules Manager kotak dialog.
- Pilih OK untuk menutup kotak dialog ini dan kembali ke lembar kerja.
=HARI INI()-C1>90
Warna latar belakang sel C1 ke C4 berubah menjadi warna isian terakhir yang dipilih.
Uji Aturan Pemformatan Bersyarat
Uji aturan pemformatan bersyarat di sel C1 hingga C4 dengan memasukkan tanggal berikut:
- Masukkan tanggal saat ini di sel C1. Sel berubah menjadi latar belakang putih default dengan teks hitam karena tidak ada aturan pemformatan bersyarat yang berlaku.
- Masukkan rumus berikut di sel C2:
=HARI INI()-40Rumus ini menentukan tanggal mana yang terjadi 40 hari sebelum tanggal saat ini. Sel diisi dengan warna yang Anda pilih untuk aturan pemformatan bersyarat untuk tanggal lebih dari 30 hari lewat jatuh tempo.
=HARI INI()-70Rumus ini menentukan tanggal mana yang terjadi 70 hari sebelum tanggal saat ini. Sel diisi dengan warna yang Anda pilih untuk aturan pemformatan bersyarat untuk tanggal lebih dari 60 hari lewat jatuh tempo.
=HARI INI()-100Rumus ini menentukan tanggal mana yang terjadi 100 hari sebelum tanggal saat ini. Warna sel berubah menjadi warna yang Anda pilih untuk aturan pemformatan bersyarat untuk tanggal lebih dari 90 hari lewat jatuh tempo.
