Menambahkan pemformatan bersyarat ke sel di Excel memungkinkan Anda menerapkan opsi pemformatan yang berbeda, seperti warna, ketika data dalam sel itu memenuhi ketentuan yang Anda tetapkan.
Untuk membuat penggunaan pemformatan bersyarat lebih mudah ada opsi pra-set yang tersedia yang mencakup situasi yang umum digunakan, seperti:
Dalam hal tanggal, opsi yang sudah ditentukan memudahkan untuk memeriksa 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 tercantum, Anda dapat menyesuaikan pemformatan bersyarat dengan menambahkan rumus Anda sendiri menggunakan satu atau beberapa fungsi tanggal Excel.
01 06
Memeriksa Tanggal 30, 60, dan 90 Hari Yang Lalu
Menyesuaikan pemformatan bersyarat menggunakan rumus dilakukan dengan menetapkan aturan baru yang diikuti Excel ketika mengevaluasi data dalam sel.
Contoh langkah demi langkah di sini menetapkan tiga aturan pemformatan bersyarat baru yang akan memeriksa untuk melihat apakah tanggal yang dimasukkan ke rentang sel yang dipilih telah melewati 30 hari, melewati 60 hari, atau 90 hari terakhir.
Rumus yang digunakan dalam aturan ini mengurangi jumlah hari tertentu dari tanggal saat ini dalam sel C1 hingga C4.
Tanggal saat ini dihitung menggunakan fungsi TODAY .
Agar tutorial ini berfungsi, Anda harus memasukkan tanggal yang termasuk dalam parameter yang tercantum di atas.
Catatan : Excel menerapkan pemformatan bersyarat dalam urutan, dari atas ke bawah, bahwa aturan tercantum dalam kotak dialog Pengelola Aturan Pemformatan Bersyarat seperti yang terlihat pada gambar di atas.
Meskipun beberapa aturan mungkin berlaku untuk beberapa sel, aturan pertama yang memenuhi ketentuan diterapkan ke sel.
02 06
Memeriksa Tanggal 30 Hari Yang Lalu Karena
- Sorot sel C1 ke C4 untuk memilihnya. Ini adalah rentang di mana kita akan menerapkan aturan pemformatan bersyarat
- Klik tab Beranda dari menu pita .
- Klik ikon Pemformatan Bersyarat untuk membuka menu tarik-turun.
- Pilih opsi Aturan Baru . Ini akan membuka kotak dialog Aturan Pemformatan Baru.
- Klik Gunakan Formula untuk menentukan sel mana yang akan diformat .
- Masukkan rumus berikut ke dalam kotak di bawah nilai Format di mana nilai ini adalah opsi yang benar di bagian bawah kotak dialog:
= HARI INI () - C1> 30
Rumus ini memeriksa untuk melihat apakah tanggal dalam sel C1 hingga C4 lebih dari 30 hari yang lalu - Klik tombol Format untuk membuka kotak dialog Format Cells.
- Klik tab Isi untuk melihat opsi warna isi latar belakang.
- Pilih warna pengisi latar belakang — untuk mencocokkan contoh dalam tutorial ini, pilih hijau muda.
- Klik tab Font untuk melihat opsi format font
- Di bawah bagian warna, atur warna font menjadi putih agar sesuai dengan tutorial ini.
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembar kerja.
- Warna latar belakang sel C1 ke C4 akan berubah menjadi warna isi yang dipilih, meskipun tidak ada data dalam sel.
03 06
Menambahkan Aturan untuk Tanggal Lebih dari 60 hari yang lalu
Menggunakan Opsi Pengaturan Aturan
Daripada mengulangi semua langkah di atas untuk menambahkan dua aturan berikutnya, kami akan menggunakan opsi Kelola Aturan yang akan memungkinkan kami menambahkan aturan tambahan sekaligus.
- Soroti sel C1 ke C4, jika perlu.
- Klik pada tab Beranda dari menu pita.
- Klik pada ikon Pemformatan Bersyarat untuk membuka menu dropdown.
- Pilih opsi Kelola Aturan untuk membuka kotak dialog Manajer Aturan Pemformatan Bersyarat.
- Klik opsi Aturan Baru di sudut kiri atas kotak dialog
- Klik Gunakan Rumus untuk menentukan sel mana yang akan memformat opsi dari daftar di bagian atas kotak dialog.
- Masukkan rumus berikut ke dalam kotak di bawah nilai Format di mana nilai ini adalah opsi yang benar di bagian bawah kotak dialog :
= HARI INI () - C1> 60Rumus ini memeriksa untuk melihat apakah tanggal dalam sel C1 hingga C4 lebih besar dari 60 hari yang lalu.
- Klik tombol Format untuk membuka kotak dialog Format Cells.
- Klik tab Isi untuk melihat opsi warna isi latar belakang.
- Pilih warna pengisi latar belakang; untuk mencocokkan contoh dalam tutorial ini, pilih warna kuning.
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke kotak dialog Manajer Aturan Pemformatan Bersyarat.
04 06
Menambahkan Aturan untuk Tanggal Lebih Dari 90 Hari Yang Lalu
- Ulangi langkah 5 hingga 7 di atas untuk menambahkan aturan baru.
- Untuk penggunaan rumus:
= HARI INI () - C1> 90 - Pilih warna pengisi latar belakang; untuk mencocokkan contoh dalam tutorial ini, pilih oranye.
- Atur warna font menjadi putih agar sesuai dengan tutorial ini.
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke kotak dialog Manajer Aturan Pemformatan Bersyarat
- Klik OK lagi untuk menutup kotak dialog ini dan kembali ke lembar kerja .
- Warna latar belakang sel C1 hingga C4 akan berubah menjadi warna pengisi terakhir yang dipilih.
05 06
Menguji Aturan Pemformatan Bersyarat
Seperti dapat dilihat dalam gambar tutorial, kita dapat menguji aturan pemformatan bersyarat dalam sel C1 hingga C4 dengan memasukkan tanggal berikut:
- Tanggal saat ini — sel harus berubah ke latar belakang putih default dengan teks hitam, karena tidak ada aturan pemformatan bersyarat yang berlaku.
- 40 hari sebelum tanggal saat ini — sel harus berubah menjadi latar hijau muda dengan teks putih.
(Catatan: Jika Anda mengalami kesulitan dalam menghitung tanggal seperti itu, seperti yang terlihat pada gambar di atas, rumus = TODAY () - 40 akan memasukkan tanggal 40 hari sebelum tanggal saat ini.) - 70 hari sebelum tanggal saat ini — sel harus berubah ke latar belakang kuning dengan teks putih.
- 100 hari sebelum tanggal saat ini — sel harus berubah menjadi latar merah gelap dengan teks putih.
06 06
Aturan Pemformatan Kondisional Alternatif
Jika lembar kerja Anda sudah menampilkan tanggal saat ini — dan sebagian besar lembar kerja — rumus alternatif untuk yang di atas dapat menggunakan referensi sel ke sel tempat tanggal saat ini ditampilkan daripada menggunakan fungsi TODAY.
Misalnya, jika tanggal ditampilkan di sel B4, rumus yang dimasukkan sebagai aturan untuk memformat secara kondisional tanggal yang lebih dari 30 hari yang lalu mungkin disebabkan:
= $ B $ 4> 30
Tanda dolar ($) di sekitar referensi sel B4 mencegah referensi sel berubah jika aturan pemformatan bersyarat disalin ke sel lain di lembar kerja.
Tanda dolar menciptakan apa yang dikenal sebagai referensi sel absolut .
Jika tanda dolar dihilangkan dan aturan pemformatan bersyarat disalin, sel atau sel tujuan kemungkinan besar akan menampilkan #REF! pesan eror.