Seseorang dari Cadangan Angkatan Darat AS bertanya:
Saya memiliki spreadsheet sederhana dengan tanggal jatuh tempo. Adakah cara untuk membuat tanggal jatuh tempo berubah hijau ketika 10 hari keluar, dan kemudian merah ketika 5 hari keluar?
Ini adalah tugas yang mudah menggunakan Conditional Formatting. Fitur ini diperkenalkan di Excel 95. Fitur ini memungkinkan Anda menentukan hingga tiga kondisi dan secara otomatis mengubah pemformatan sel berdasarkan kondisi tersebut. Anda dapat mengubah fonta, membuat font tebal, mengubah warna, warna latar belakang. Jika Anda memiliki sel yang ingin Anda pantau untuk melihat apakah mereka meninggalkan toleransi tertentu, pemformatan bersyarat adalah alat yang hebat untuk digunakan.
Metode terbaik adalah menyiapkan format bersyarat untuk sel pertama di kolom tanggal Anda, lalu menyalin format itu ke semua sel lain dalam rentang.
Misalkan rentang tanggal Anda ada di E2: 200. Anda akan memilih sel E2 dan mengikuti langkah-langkah ini: (gulir ke bawah sedikit untuk melihat gambar saat Anda membaca…)
- Dari menu, pilih Format - Conditional Formatting…
- Di sebelah kiri kotak dialog Conditional Formatting, ada dropdown yang bertuliskan "Cell Value Is". Klik Dropdown itu dan ubah nilainya menjadi "Formula Is". Ini akan mengubah sisi kanan kotak dialog menjadi satu bidang panjang.
- Di bidang itu, masukkan rumus ini:
=(E2-TODAY())<6(Catatan jika tanggal pertama Anda tidak di E2, ubah E2 ke sel tempat Anda berada) - Klik tombol Format di dekat bagian bawah kotak dialog pemformatan bersyarat.
- Di tengah tab Font adalah dropdown warna. Pilih Merah. Atau - pilih kuning dan pada tab pola pilih Merah. Itu akan membuat mereka sangat menonjol. Klik OK untuk kembali ke dialog Conditional Formatting.
Langkah-langkah di atas akan membuat tanggal jatuh tempo dalam 5 hari dari hari ini berubah menjadi merah. Saat Anda masih berada dalam kotak dialog pemformatan bersyarat, lakukan langkah-langkah berikut:
- Klik tombol Add >> di bagian bawah kotak dialog dan Kondisi 2 baru akan muncul.
- Ubah "Cell Value Is" menjadi "Formula Is"
- Masukkan rumus
=(E2-TODAY())<11 - Klik tombol Format dan pilih font biru, dan pola hijau. Klik OK untuk menutup dialog Format Cells.
- Dialog Pemformatan Bersyarat Anda akan terlihat seperti ini. Klik OK.
Kami hampir sampai. Setelah Anda melalui semua langkah ini untuk mendapatkan format bersyarat yang diatur untuk sel pertama, mudah menggunakan fitur Tempel Spesial untuk menyalin format ke seluruh rentang tanggal Anda. Saat Anda Menempelkan Khusus - Format, pemformatan bersyarat juga akan disalin.
- Pilih sel yang Anda tambahkan pemformatan bersyarat. Dalam contoh ini, ini adalah E2.
- Dari menu, pilih Edit - Salin.
- Sorot semua tanggal lain dalam data Anda - E3: E200. JIKA Anda akan menambahkan lebih banyak data, silakan menyorot beberapa baris tambahan.
- Dari menu, pilih Edit - Tempel Spesial…
- Dalam dialog Tempel Spesial, pilih entri ke-4 di kolom kiri - Format.
- Klik OK.
Ini akan menyalin pemformatan bersyarat ke semua sel yang Anda pilih. Perhatikan bahwa itu akan menyalin batas dan format angka juga, jadi Anda mungkin harus menyesuaikan batas Anda setelah melakukan operasi ini.
Catatan:
- Ada batasan 3 format bersyarat yang bisa diterapkan ke sel mana pun. Menyertakan pemformatan default yang berlaku untuk sel yang tidak memenuhi ketentuan apa pun, ini berarti Anda dapat secara otomatis menerapkan hingga 4 format ke sel.
- Jika Anda perlu memperluas ini ke lebih dari 4 format, Anda harus menulis makro di VBA. Makro akan relatif lambat, karena harus memperbarui semua sel setelah Anda membuat perubahan pada spreadsheet. Anda juga dapat menyewa konsultan Excel untuk melakukannya.
- Bagian tersulit dalam menyiapkan pemformatan bersyarat adalah mengetik rumus di kotak rumus. Ingatlah untuk selalu memulai rumus dengan tanda sama dengan. Mungkin ada satu bab dalam buku yang dikhususkan untuk rumus berbeda yang dapat diketik ke dalam kotak ini. Dimungkinkan untuk memasukkan rumus yang sepenuhnya bergantung pada sel lain.
- Jika Anda keluar dari entri rumus dan kembali lagi nanti untuk mengedit rumus, Excel memiliki kebiasaan buruk dalam menafsirkan karakter spasi mundur sebagai upaya untuk menunjuk ke sel. Ini akan selalu mengubah rumus Anda menjadi hal yang salah. Tekan Escape untuk kembali ke rumus asli. Berguna untuk menyorot teks yang salah dengan mouse dan kemudian mengetik lagi.
