Identifikasi Formula Dengan Conditional Formating

Bagaimanakah cara mengenali dan membedakan jenis konten sel, apakah berupa nilai statis atau sebuah formula? Cara pertama yang umumnya digunakan adalah dengan klik sel tertentu dan melihat formula bar, apakah disitu ada rumus/formula atau tidak. Cara kedua adalah dengan menampilkan mode show formula (tekan shortcut Ctrl + `).  

Selain kedua cara tersebut, mulai dari versi 2013, excel sudah menyediakan fungsi ISFORMULA yang dapat digunakan untuk mengetest apakah kontent sel berupa formula atau bukan. Dengan menggunakan fungsi ISFORMULA dalam rule conditional formating, maka kita bisa mengenali sel berisi formula dengan mudah dan cepat. 

Fungsi ISFORMULA

Fungsi ISFORMULA berguna untuk mengetest apakah sebuah sel berisi formula atau tidak. Fungsi ini dapat dituliskan sebagai berikut:    =ISFORMULA(reference)

Adapun referensi di sini adalah berupa sebuah sel. Misalnya untuk mengecek apakah sel A1 berisi formula atau tidak, maka kita bisa menuliskan rumus:  =ISFORMULA(A1)

Jika sel A1 berisi formula maka rumus akan menghasilkan nilai TRUE, sebaliknya jika sel A1 berisi nilai statis atau tidak berisi formula maka rumus akan menghasilkan nilai FALSE. Untuk lebih jelasnya perhatikan gambar di bawah ini.

Gambar di atas menunjukan cara menggunakan rumus ISFORMULA  dan nilai TRUE dan FALSE sebagai hasil dari rumus tersebut.

Fungsi ISFORMULA dengan Conditional Formating.

Untuk memggunakan fungsi  ISFORMULA dalam rule conditional formating maka ikuti langkah berikut:

Contoh: Sebuah tabel pada range A1:C5 berisi serangkaian data. Conditional formating diperlukan untuk mengidentifikasi sel-sel dalam range tersebut apakah berisi formula atau hanya berisi nilai statis saja. 

• Seleksi range A1:C5 dimulai dari ujung Atas Kiri (sel A1) sampai ke ujung bawah kanan (sel C5)
• Dari tab home, klik Conditional Formating ➜ klik New Rule… sehingga masuk ke jendela New Formating Rule
• Select rule type: pilih user a Formula to determine which cells to format.
• Ketika formula berikut:  =ISFORMULA(A1)  
• Perhatikan kembali rumus ISFORMULA(A1),  pastikan penulisan referensi A1 tidak boleh ada tanda dolar.
• Klik tombol Format untuk mengatur format sel apabila sel berisi formula
• Klik Tab Fill ➜ Pilih warna yang anda inginkan, misalnya kuning  (untuk pengaturan format ini ada juga bisa memilih pengaturan format sesuai preferensi anda, mana yang anda suka)
• Setelah setting format dirasa sesuai, lalu klik OK, dan klik OK lagi.
• Berikut gambaran langkah-langkah penerapan fungsi ISFORMULA dalam rule conditional formating.

Setelah langkah-langkah diatas diikuti, maka sampai tahap tersebut kita sudah bisa membuat conditional formating untuk mengecek apakah konten sel berisi formula atau bukan. Berikut contoh kenampakannya di excel setelah dilakukan setting rule conditional formating dengan rumus ISFORMULA

Bagaimana jika fungsi ISFORMULA tidak tersedia pada versi excel yang anda gunakan?

Jika masih menggunakan excel versi 2010 atau yang lebih lama, anda tidak menemukan fungsi ISFORMULA pada versi tersebut. Namun demikian, tenang saja,  anda masih bisa membuat fungsi ISFORMULA sendiri menggunakan bantuan VBA. Fungsi ISFORMULA buatan user ini hanya memerlukan code VBA yang sangat sederhana, yaitu:

Function ISFORMULA(sel As Range)

ISFORMULA = sel.HasFormula

End Function

Supaya bisa digunakan, code tersebut harus diketik atau dicopy pada VBA editor, modul standar (tekan short cut Alt + F11 untuk masuk ke VBA editor, kemudian klik insert  modul  untuk membuat modul baru. Selanjutnya copy atau ketik kode VBA seperti contoh diatas.

Langkah selanjutnya tinggal menerapkan fungsi ISFORMULA dalam conditional formating. 

Demikian pembahasan singkat mengenai bagaimana membuat rule conditional formating untuk mengenali dan membedakan kontent sel yang berisi formula dengan yang bukan formula / rumus. Semoga bermanfaat.

Terima kasih.

Silahkan dicek juga catatan pelajaran excel lainnya:

• Control Conditional Formating dengan Check Box
• Menemukan Data Duplikat dengan Conditional Formating
• Menandai Data yang Muncul 2 kali, 3 kali atau lebih
• Merubah Warna Sel berdasarkan Value dan Formula

Control Conditional Formating Dengan Check Box

Ketika kita menetapkan sejumlah rules untuk conditional formating, mungkin saja kita mengharapkan conditional formating dapat dirubah dari aktif menjadi nonaktif atau sebaliknya dengan cepat. Untuk melakukan aktivasi dan deaktivasi conditional formating secara cepat, kita dapat memanfaatkan form check box dan melakukan sedikit modifikasi pada rumus conditional formating.

Conditional formating merupakan fitur excel yang tersedia sejak excel versi 97.  Fitur ini berfungsi untuk menerapkan format pada range sel terpilih yang memenuhi kriteria tertentu berdasarkan syarat yang anda tentukan. Syarat yang digunakan dalam rule conditional formating yang paling sederhana biasanya berupa sel value. Namun kita juga bisa menentukan syarat berdasarkan formula. Penggunaan formula atau rumus dalam conditional formating memberikan fleksibilitas dan secara signifikan memperluas kemampuan fitur ini.

Membuat mekanisme perubahan mode aktif menjadi nonaktif atau sebaliknya pada conditional formating merupakah salah satu contoh penggunaan formula dalam fitur ini. 

Berikut contoh penerapan form Check Box untuk aktivasi dan deaktivasi Conditional Formating.

Misalnya kita ingin menyembunyikan atau sebaliknya dengan hanya klik centang check box.

Cara menambahkan dan setting check box.

Buat form checkbox, caranya: Masuk ke tab Developer ➜ insert ➜ check box (form control).

Kemudian dengan menggunakan pointer mouse, buat form check box dalam lembar kerja excel, hasilnya akan seperti gambar berikut:

Jika form control tersebut kita klik maka akan muncul tanda tick dalam kotak 

Check box tersebut kemudian harus kita link-kan dengan sebuah cell (linked cell), caraya: klik kanan pada check box ➜ Format control… sehingga masuk ke jendela Format Control.

Dalam jendela Format Control ➜ masuk ke tab Control ➜ pada field Cell link, ketikan alamat cell yang akan di link (misal $E$2). Selain dengan diketik, cara yang lebih mudah adalah dengan cara klik, tempatkan kursor dalam field, kemudkan klik cell yang diinginkan untuk di-link-kan.

Berikut gambar pengaturan cell link pada form checkbox.

Pada saat tidak aktif, form checkbox akan memberikan nilai FALSE pada linked cell.

Sedangkan pada saat aktif, akan memberikan nilai TRUE pada linked cell.

Kondisi TRUE dan FALSE ini yang dapat kita sambungkan dengan formula conditional formating untuk membuat mekanisme aktivasi dan deaktivasi dengan cepat melalui checkbox. 

Untuk lebih memahami apa yang dimaksud, mari kita coba sebuah contoh sederhana cara menyembunyikan data  menggunakan conditional formating dan check box. 

Dengan membuat check box yang sudah kita buat sesuai panduan di atas, yaitu checkbox 1, selanjutnya ikuti langkah berikut:

Ganti nama (caption) check box 1 menjadi “Sembunyikan data”, caranya: sambil menekan tombol Ctrl di keyboard, klik check box, kemudian lepas tombol Ctr, dan klik lagi pada text check box sehingga muncul kursor, ganti text “check box 1” menjadi “Sembunyikan Data”

Hasilnya akan terlihat seperti gambar di bawah ini:

Selanjutnya gunakan range $A$1:$A$10 untuk menyimpan data berupa angka 1 s/d 10. 

Perhatikan value FALSE di sel E2, Ketika anda klik check box berulang kali maka kita bisa melihat perubahan FALSE ke TRUE atau sebaliknya.

 Karena anda tidak perlu melihat value pada sel E2, maka anda bisa menyembunyikannya dengan merubah warna font menjadi putih atau sesuai warna dasar sel.

Cara setting rule conditional formating.

Seleksi range $A$1:$A$10, dimulai dari sel A1. Kemudian masuk ke Tab Home dan dibawah opsi Styles, pilih conditional formating ➜ New Rule. pilih “user formula to determine which cell to format”

Di bawah format value dimana formula adalah  ketik =$E$2=TRUE. Kemudian klik tombol Format dan seleksi warna putih, kemudian klik Ok , dan Ok sekali lagi.

Setelah proses Ok, dan Ok lagi maka control aktivasi dan deaktivasi conditional formating menggunakan checkbox sudah bisa digunakan.

Silahkan dicek dengan cara klik checkbox sehingga kotak akan tercentang yang berarti aktif, dan warna font data pada range A1:A10 akan secara otomatis berwarna putih. Karena warna dasar putih, maka data akan tidak kelihatan alias disembunyikan. 

Klik lagi check box untuk menghilangkan tanda centang, maka data akan kembali normal.

Secara lebih rinci, urutan proses pembuatan control conditional formating menggunakan check box dapat dilihat pada video berikut ini.

Anda mungkin tertarik mengetahui Catatan Pelajaran Excel lainnya:

• Menemukan Data Duplikat Dengan Conditional Formating
• Merubah Warna Sel Berdasarkan Value dan Formula
• Menandai Data yang Muncul 2 kali, 3 kali atau lebih

Sekian, semoga bermanfaat.

Salam…

Menandai Data yang Muncul 2 kali, 3 Kali, atau lebih

Bagaimana cara menemukan dan menandai data yang muncul dua kali, tiga kali, empat kali atau lebih?  Hal ini dapat dengan mudah dilakukan menggunakan fitur conditional formating.  Tehnik ini juga bisa diterapkan baik menggunakan excel versi baru maupun versi jadul.

Video >>

Pada catatan sebelumnya tentang cara mencari data duplikat di excel, sudah disampaikan bagaimana menggunakan fitur conditional formating untuk menemukan data ganda, yaitu data yang muncul lebih dari satu kali. Namun dengan tehnik tersebut  pencarian data ganda tidak membedakan seberapa banyak kemunculan data duplikat. Data yang muncul 2 kali, 3 kali, 4 kali atau lebih teridentifikasi sebagai data duplikat dan divisualisasikan dengan warna yang sama.

Bagaimana jika kita ingin membedakan warna sel untuk data yang muncul 2 kali, 3 kali, 4 kali dan seterusnya. Caranya cukup mudah, kita hanya tinggal memodifikasi rumus pada conditional formating.

Adapun langkah-langkahnya sebagai berikut:

 Excel Versi Baru (2007 atau lebih baru)

Note: Ilustrasi disini menggunakan excel versi 2016

Seleksi tabel data dimana akan dicari dan ditandai data ganda yang muncul 2 kali atau lebih, misal range A1:H20

Dari tab Home ➜ Klik Conditional Formating ➜ Manage Rules ➜ sehingga masuk ke jendela Conditional Formating Rules Manager ➜

1. Setting rule conditional formating untuk data yang muncul dua kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, ketikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=2
• Rumus tersebut untuk mengecek data yang muncul 2 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: kuning) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager

2. Setting conditional formating untuk data yang muncul 3 kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, kektikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=3
• Rumus tersebut untuk mengecek data yang muncul 3 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: biru) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager

3. Setting conditional formating untuk data yang muncul 4 kali

• Klik New Rule ➜ muncul kotak dialog New Formating Rule
• Pada bagian Select a Rule Type, pilih Use Formula to determine which cell format
• Pada bagian Edit the Rule Description, kektikan rumus berikut: =COUNTIF($A$1:$H$20,A1)=4
• Rumus tersebut untuk mengecek data yang muncul 4 kali.
• Klik tombol format ➜ masuk ke tab Fill ➜ filih warna fill yang diinginkan (misal: hijau) ➜ klik Ok ➜ kembali ke jendela Conditional Formating Rules Manager
• Setelah menentukan rule conditional formating untuk data dengan kemunculan 2 kali, 3 kali, dan 4 kali, maka tampilan jendela Conditional Formating Rules Manager mungkin akan tampak seperti berikut:

Selanjutnya klik Ok untuk menerapkan Conditional formating pada range yang sudah kita A1:H20, yaitu yang yang kita seleksi sebelumnya. Pada akhirnya data - data yang muncul  2 x, 3x dan 4x akan ditandai dengan warna yang berbeda seperti gambar berikut:

• Warna kuning, merupakan data yang muncul 2 kali
• Warna biru, merupakan data yang mucul 3 kali
• Warna hijau, merupakan data yang muncul 4 kali.

Contoh diatas menggambarkan cara menandai data yang muncul 2 kali, 3 kali dan 4 kali.  Pada excel versi baru, anda dapat menambahkan rule lagi untuk menandai data yang muncul 5 kali, 6 kali dan seterusnya.

Excel Versi Lama (2003)

Di excel 2003, kita juga bisa menandai data yang muncul 2 kali (kondisi 1), 3 kali (kondisi 2) dan 4 kali (kondisi 3). Sayangnya pada versi ini, conditional formating dibatasi 3 format, sehingga kita tidak bisa membuat rule untuk kondisi ke-4.

Adapun langkah nya sebagai berikut:

• Seleksi tabel data dimana akan dicari dan ditandai data  yang muncul 2 kali (kondisi ke-1), 3 kali (kondisi ke-2) dan 4 kali (kondisi ke-3), misal range A1:H20.
• Klik menu Format ➜ Conditional Formating ➜ muncul jendela Conditional Formating
• Pada Condition 1, pilih Formula Is, dan ketikan rumus berikut: COUNTIF($A$1:$H$20,A1)=2
• Klik Format ➜ Pattern ➜ Tentukan warna yang anda inginkan ➜ klik OK
• Klik Add untuk untuk menambahkan kondisi 2 dan ulangi langkah di atas, dan add lagi untuk menambahkan kondisi 3 dan ulangi langkah tadi, sehingga akhirnya akan diperoleh 3 kondisi dengan pengaturan conditional formating seperti gambah di bawah ini:

Klik Ok, sehingga diperoleh hasil seperti di bawah ini:

Pada akhirnya kita bisa melihat bahwa data yang muncul 2 kali, 3 kali dan 4 kali dikenali dengan warna yang berbeda.

Sampai di sini pembahasan mengenai cara mencai dan menandai data yang muncul 2 kali atau lebih di excel, baik excel versi baru maupun versi lama. Semoga bermanfaat.

Catatan Pelajaran Excel Terkait:

• Menemukan Data Duplikat dengan Conditional Formating
• Merubah Warna Sel Berdasarkan Value dan Formula
• Rumus Excel Menghitung Nilai Unik.

Menemukan Data Duplikat Dengan Conditional Formating

Conditional formating di excel biasanya digunakan untuk mengidentifikasi value dalam range tertentu. Namun kita juga bisa menggunakan conditional formating untuk mengidentifikasi data duplikat dalam list atau tabel. Jika anda termasuk golongan muda yang sudah menggunakan versi excel terbaru, mencari data duplikat sangat mudah karena fitur conditional formating pada versi excel terbaru sudah mengalami improvement yang sangat significant. Tetapi jika ada termasuk golongan tua yang tidak mau move on dari excel versi jadul, santai aja, anda masih bisa menemukan data duplikat dengan melakukan sedikit modifikasi pada rumus conditional formating.

Pengguna excel seringkali harus mengidentifikasi data duplikat dalam list atau tabel secara manual. Cara ini tentu saja sangat menguras waktu dan riskan human error. Untuk menjadikan pekerjaan ini lebih mudah, maka kita bisa memanfaatkan salah satu fitur excel standar, yaitu Conditional Formating.

Mencari data duplikat data menggunakan excel versi jadul

Yang dimaksud excel versi jadul di sini adalah versi 1997 s/d 2003 atau. Wow… cukup tua ya, lebih dari 17 tahun yang lalu. Bisa dibayangkan berapa usia user yang masih suka menggunakan versi excel versi jadul ini…

Tapi apa mungkin ya di Zaman sekarang masih ada yang menggunakan excel versi jadul. Tapi tak apalah. Meski tehnik ini hanya cocok digunakan pada excel versi jadul. Anggaplah ini sebagai tambahan pengetahuan.

Sebagai contoh, bagaimana mencari duplikat data pada sebuah tabel data dalam range $A$1:$J$20 :

• Seleksi range tersebut mulai dari sel A1 s.d sel J20. Sangat penting diperhatikan disini bahwa A1 harus sebagai aktive cell.
• Selanjutnya dari menu Format ➜ Conditional Formating… ➜ Muncul kotak dialog Conditional Formating.
• Pilih Formula Is  pada bagian atas kiri menu pop-up. Dalam field sebelah kanannya masukan rumus berikut:    =COUNTIF($A$1:$J$20,A1)>1
• Masih dalam kotak dialog Conditional Formating, masuk ke tab Format, masuk ke tab Pattern dan pilih warna yang anda inginkan untuk memvisualisasikan data duplikat. Klik Ok untuk kembali ke kotak dialog Conditional Formating. Selanjutnya klik Ok untuk menerapkan conditional formating pada range terseleksi.

Setelah proses di atas, maka semua sel yang mengandung data duplikat akan berwarna sesuai warna yang anda pilih dalam langkah sebelumnya. Dengan demikian mata anda akan lebih mudah menemukan semua data duplikat.

Sekali lagi, penting diperhatikan bahwa sel paling kiri atas harus sebagai sel aktif. Artinya harus sebagai sel yang pertama pada saat anda menggerakan mouse untuk menyeleksi range.

Contoh: 

• Range A1:A10, maka sel aktif harus A1
• Range B3:J50, maka sel aktif harus B3
• Range D2:H50, maka sel aktif harus D2

Dengan kata lain, ketika anda menyeleksi range menggunakan kursor mouse, maka anda harus memulainya dari sel paling kiri atas kemudian bergerak ke bagian paling kanan bawah.

Jika anda tidak memperhatikan ketentuan ini maka rumus conditional formating yang anda buat tidak  akan menghasilkan apa yang diharapkan.

Selain itu,  penting juga untuk memperhatikan tanda dolar dalam penulisan rumus.

Range harus ada tanda dolar tetapi individu cel tidak boleh pake tanda dolar.

Perhatikan contoh rumus di atas:  =COUNTIF($A$1:$J$20,A1)>1

Range $A$1:$J$20 (dengan tanda dolar) merupakan lokasi tabel atau list yang akan dicari nilai duplikat nya. Sedangkan sel A1 merupakan individual cell tidak boleh menggunakan tanda dolar.

Sel A1 (tanpa tanda dolar) merupakan sel dalam tabel atau list yang akan dicek kontentnya apakah memiliki nilai duplikat di sel lainnya atau tidak dalam tabel/list.

Mencari data duplikat data menggunakan conditional formating pada excel versi baru.

Yang dimaksud excel versi baru di sini adalah versi 2007 atau yang lebih baru. Mencari dan mewarnai data duplikat pada excel versi baru lebih simple dari versi sebelumnya. Hal ini karena adanya improvement yang sangat signifikant pada fitur conditional formating.

Caranya:

• Seleksi range sel berisi data yang akan di cek apakah ada data duplikat di dalamnya, misalnya range A1:J20
• Lalu dari tab Home, klik Conditional Formating ➜ Higlight Cells Rules ➜ Duplicate Values… ➜ Ok

Sebagai rangkuman atas apa yang sudah dibahas diatas, berikut video langkah-langkah mencari data duplikat menggunakan conditional formating, baik pada excel versi lama maupun versi baru: 

Demikian tips singkat bagaimana mencari dan mewarnai data duplikat dalam excel. Semoga bermanfaat.

Tips excel lainnya:

• Merubah warna sel berdasarkan value dan formula
• Rumus excel menghitung nilai unik
• 3  Fitur Canggih Excel yang Memudahkan Pekerjaaan.

Merubah Warna Sel Berdasarkan Value dan Formula.

Merubah warna sel? Saya rasa, hal ini bukanlah hal yang asing bagi setiap pengguna excel. Selain untuk  estetika, pewarnaan sel juga dapat membantu mempermudah membaca, menemukan dan menganalisa data tertentu. Cukup dengan seleksi sel atau range tertentu, kemudian dengan menggunakan tombol “fill color”, ataupun dengan memilih style tertentu maka kita sudah bisa menghiasi spreadsheet dengan aneka warna.

Kalau sudah tahu bahwa mewarnai sel bukanlah hal yang asing, jadi kenapa artikel ini masih membahas perihal merubah warna sel?

Nah artikel ini dibuat yaitu untuk membahas beberapa tehnik mewarnai cell dengan cara yang agak jarang digunakan oleh kebanyakan user.

Adapun pembahasan difokuskan pada 2 hal:

1. Merubah warna sesuai nilai bilangan dalam sel 
2. Merubah warna sel kosong dan sel error.

Dan pada masing masing 2 hal tersebut dibagi lagi 2 pembahasan yaitu :

• Tehnik conditional formating yang menghasilkan perubahan warna yang bersifat dinamis artinya warna akan berubah secara otomatis mengikuti kriteria atau kondisi yang ditetapkan.
• Tehnik pencarian dan seleksi sel khusus sehingga dapat dilakukan formating secara sekaligus pada seleksi sel dengan kriteria atau kondisi tertentu. Formating dengan tehnik ini bersifat statis, artinya warna sel tidak bisa berubah secara otomatis jika ada perubahan kondisi pada nilai sel.

Baiklah, mari kita bahas satu per satu.

Bagaimana merubah warna sel berdasarkan value dalam sel.

Conditional Formating : Merubah Warna Secara Dinamis Berdasarkan Cell Values.

Misalnya kita memiliki sebuah tabel berisikan sejumlah data dan kita ingin mewarnai atau meng-highlight sel berdasarkan kriteria nilai atau kondisi tertentu. Sayangnya data tersebut bisa berubah sewaktu-waktu dan kita tidak ingin repot merubah warna secara manual setiap kali ada perubahan data

Anggaplah tabel tersebut berupa data jumlah penjualan produk tertentu di lokasi pemasaran yang dibagi menjadi beberapa area. Selanjutnya kita ingin meng-highlight sel berisi data penjualan yang lebih dari 250 unit.

Di sinilah conditional formating memainkan perannya.

Perhatikan screenshot berikut:

Catatan: screenshots dalam artikel ini diambil menggunakan excel 2010, akan tetapi tombol, dialog box dan settingnya hampir saja baik menggunakan excel 2010, 2013 maupun 2016.

Lakukan langkah - langkah berikut untuk membuat rule conditional formating merubah warna sesuai value.

Seleksi tabel atau range dimana kita ingin merubah warna background sel. Dalam hal contoh yang kita gunakan dalam pembahasan ini, lakukanlah seleksi pada range B2:H8 (baris pertama yang berisi nama kolom dan kolom pertama yang berisi nama area tidak termasuk area yang diseleksi).

Masuk ke tab Home, kemudian grup Style dan pilih Conditional Formating → New Rule

• Di dalam kotak dialog New Formating Rule, pada bagian “Select a Rule Type” pilih “Format only cells that contain” 
• Kemudian lanjutkan ke bagian bawah dialog box pada bagian Edit Rule Description → Format Only Cells With dimana terdapat 3 field yang perlu dipilih atau diisi. 
• Pada bagian paling kiri Pilih Cell Value, 
• Pada bagian tengah pilih greater than 
• Dan pada bagian paling kanan  ketikan angka 250, 
• Kemudian klik tombol  format untuk memilih warna background yang akan diaplikasikan ketika kondisi terpenuhi.
• Perhatikan langkah-langkah nya dalam screenshot di bawah ini.

Di dalam kotak dialog format cells, masuk ke tab Fill dan kemudian pilih warna yang anda inginkan, misalnya hijau, dan kemudian klik OK.

Setelah menekan tombol OK, maka kita akan kembali melihat jendela Edit Formating Rule untuk melihat preview dari format yang sudah dibuat. Jika dirasa sudah oke, maka klik tombol OK.

Dan hasilnya adalah seperti terlihat seperti screenshot di bawah ini. Semua sel berisi value lebih dari 250 akan berwarna hijau atau warna lain sesuai pengaturan pada langkah sebelumnya. Warna hijau tersebut akan kembali menjadi putih jika kita merubah value sel menjadi 250 atau kurang. Dengan kata lain warna sel bersifat dinamis.

Seandainya ada lebih dari satu kondisi yang harus ditampilkan dalam format berbeda, maka kita dapat menambahkan format lainnya dengan cara yang sama dengan langkah sebelumnya yaitu dari tab Home, kemudian klik Conditional Formating.

Pada dialog box Edit Formating Rule pilih Format Only Cells That Contain, dan pda bagian Format Only Cells With pilih Cell Value , dan Less Than Or Equal to, kemudian ketikan angka misalnya 150. Rule ini untuk memberikan format pada sel yang berisi angka 150 atau kurang.

Setelah menekan tombol OK, maka kita akan mendapatkan tabel dengan warna sel berwarna merah muda untuk data penjualan dengan nilai 150 atau kurang.

Dengan cara yang sama, kita juga dapat menambah dan mengatur format yang diinginkan. Untuk memprcantik tampilan data, selain merubah warna background sel, kita juga dapat mengatur format lainnya seperti font, warna font, border dan sebagainya.

Cara Menemukan dan Menyeleksi Semua Sel Berisi Nilai Tertentu dan Kemudian Mewarnainya.

Tehnik ini mencoba memberikan alternative lain seleksi sel tertentu menggunakan fitur Find & Select. 

Fitur ini sebenarnya sudah cukup populer digunakan untuk mencari data tertentu dalam spreadsheet.

Namun langkah menyeleksi sel dengan kriteria tertentu menggunakan fitur ini masih sering luput dari perhatian. Hal ini wajar saja karena kebanyakan user lebih senang menyeleksi range atau sel tertentu satu persatu menggunakan mouse kemudian merubah format sesuai yang diinginkan. 

Sedangkan dengan fitur yang akan dijelaskan ini, kita bisa bisa mencari nilai dengan kriteria tertentu dalam range  data, kemudian menerapkan format secara bersamaan. Penerapannya Ada beberapa skenario tergantung jenis data atau nilai yang kita cari.

Misalnya kita perlu menemukani sel dengan nilai tertentu, anggaplah 100 Maka cukup masuk ke tab Home, Editing group dan klik Find & Select → Find…

Masukan nilai yang diinginkan dan kemudian klik tombol Find All.

Tip: Klik tombol Options  untuk mendapatkan opsi pencarian yang lebih advanced seperti “Match Case”, “Match entire cell contents”. 

Untuk mendapatkan hasil pencarian dengan scope yang lebih lebar, kita bisa menggunakan karakter wildcard seperti asterisk (*) untuk menemukan beberapa karakter tertentu atau tanda tanya (?) untuk menemukan karakter tunggal. Karakter wildcard tersebut diketikan pada field Find What bersamaan dengan angka kunci.

Misalnya kita ingin mencari dan menyeleksi sel berisi nilai 200 atau lebih tetapi kurang dari 300.

• Seleksi range B2:H8
• Dari Tab Home, kemudian klik tombol Find & Select, klik Find.  Atau bisa juga menggunakan shorcut CTR + F
• Pada kotak isian Find What, ketik 2??
• Klik Tombol Find All
• Tekan keyboard CTR + A untuk menyeleksi semua sel yang memenuhi kritera pencarian
• Close jendela Find & Replace
• Maka kita akan mendapatkan semua nilai yang memenuhi kriteria nilai 200 lebih tetapi kurang dari 300 yang sudah terseleksi.
• Lakukan edit format seperti biasa, misalnya dengan fitur autofill atau dengan klik kanan Format Cell atau melalui command-command lainnya yang kita sukai.

Merubah Warna Background Sel Khusus (Blanks dan Formula Error)

Seperti pada contoh sebelumnya pada section ini juga akan dibahas bagaimana merubah warna secara dinamis dan secara statis. Conditional Formating untuk merubah warna secara dinamis. Sedangkan untuk perubahan warna secara statis, kita gunakan bantuan alat Go to Special untuk menyeleksi sel dengan kriteria yang kita inginkan dan kemudian merubah formatnya dengan cara-cara yang sudah kita ketahui.

Conditional Formating : Menggunakan Formula Untuk Merubah Warna  Sel Kosong dan Sel Error.

Metode ini bisa menjadi solusi yang kemungkinan besar sangat anda perlukan dan banyak kasus. 

Kita akan menggunakan table nilai penjualan di beberapa area dan beberapa bulan seperti contoh sebelumnya, dengan perubahan beberapa kasus data dimana terdapat bebeapa sel kosong. Perhatikan bagaimana kita bisa mendeteksi semua sel kosong tersebut dan merubah warnanya secara bersamaan. 

• Seleksi range sel yang akan kita atur warnanya sesuai kondisi blank atau error, misalnya range B2:H8
• Masuk ke Tab Home, kemudian pada grup Style, klik Conditional Formmating → New Rule
• Kemudian di dalam kotak dialog “New Formating Rule”, pilih option “Use a formula to determine which cells to format”.  Lalu masukan salah satu formula berikut di dalam field “Format falues where this formula is true” 
• Untuk merubah warna sel kosong (blank cells) rumusnya =ISBLANK()
• Untuk merubah warna sel yang mengandung nilai error =ISERROR() 
• Anggaplah kita lebih tertarik untuk merubah warna sel kosong maka masukan formula =ISBLANK(), kemudian tempatkan kursor di antara tandak kurung dan dengan menggunakan mouse, klik sel paling pojok kiri atas dari range yang diseleksi sebelumnya, kemudian delete tanda dolarnya, sehingga rumus menjadi ISBLANK(B2). 
• Kita juga dapat mengetikan referensi B2 secara langsung sebagai parameter fungsi ISBLANK()
• Kemudian tekan tombol format, dan atur formatnya pada bagian Fill, kemudian tekan tombol OK, dan Tekan tombol Ok lagi.

Untuk lebih jelasnya perhatikan langkah – langkah berikut berikut:

Perlu diperhatikan, bahwa B2 merupakan alamat sel pertama atau sel yang paling kiri dan paling atas dari range yang diseleksi. Penulisan referensi alamat sel tidak menggunakan tanda dolar artinya alamat tersebut bersifat relatif agar formula pada conditional formating setiap sel mengambil referensi dari sel itu sendiri. Jika referensi ditulis $B$1 maka perubahan pada sel B1 akan berdampak pada format seluruh sel dalam lingkup conditional formating.

Screenshot berikut menunjukan hasil dari conditonal formating menggunakan formula untuk mewarnai sel kosong atau blank cells

Dengan tehnik serupa kita juga bisa mewarnai sel yang mengandung formula dengan nilai error. Caranya  cukup dengan mengganti rumus ISBLANK(B2) menjadi ISERROR(B2). Silahkan dicoba.

Cara Menyeleksi Seleksi Seluruh Cell Kosong atau Cell Error Dan Kemudian Mewarnainya.

Selain menggunakan conditional formating yang dapat merubah warna sel secara dinamis mengikuti kondisi blank atau nilai error, kita juga dapat menyeleksi sel kosong ataupun error menggunakan alat Go To Special dan kemudian mewarnainya dangan fill color untuk menghasilkan format warna yang statis.

Caranya cukup mudah

• Seleksi range yang akan dicari sel kosong atau error nya, misalnya range B2:H8
• Kemudian dari Tab Home, grup Editing, klip Tombol Find & Select dan klik Go To Special.. ini untuk memunculkan kotak dialog Go To Special.

Tips: Selain menggunakan cara di atas, kotak dialog Go To Special juga dapat dimunculkan dengan menggunakan shortcut CTR+G   atau F5.

• Untuk menyeleksi sel kosong, pilih opsi Blanks
• Untuk menyeleksi formula error, pilih opsi Formula, kemudian aktifkan centang pada cek box Error, dan cek box lainnya non aktifkan.
• Klik tombol Ok
• Terakhir : Warnai menggunakan Fill Color ataupun format sel lainnya.
• Selesai.

----

Sampai disini pembahasan mudah mudahan bermanfaat. Yaitu Mewarnai sel secara dinamis menggunakan conditional formating. Juga mewarnai sel secara statis dengan cara-cara yang umum dilakukan, namun cara menyeleksi sel yang sedikit berbeda sehingga dapat melakukan formating secara sekaligus terhadap sel yang memenuhii kriteria.

Silahkan di cek juga catatan pelajaran excel lainnya:

• Cara Efektif Menggunakan Format Painter Secara Berulang
• Menghitung Jumlah Sel Berwarna Tertentu.
• Cara Cepat Menghapus Baris Kosong
• Cara Cepat Mengisi Sel Kosong

Referensi:

https://www.ablebits.com/office-addins-blog/2013/10/18/change-background-color-excel-based-on-cell-value/