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.