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.

Mengunci dan Memproteksi Sel Berisi Formula

Ketika merancang sebuah template laporan berformat excel, mungkin kita menginginkan user hanya dapat melengkapi data pada sel yang ditentukan saja pada template laporan yang kita buat. Selain itu, aktivitas user harus dibatasi supaya tidak bisa melakukan modifikasi formula yang bisa menyebabkan kesalahan kalkulasi data. Untuk itu maka kita bisa mengatur penguncian (lock) dan proteksi sel.

Metode termudah dan paling umum untuk mengamankan template spreadsheet dari user yang hendak main-main dengan formula adalah dengan cara memproteksi worksheet. Sayangnya, proteksi sheet saja tidak hanya mencegah user melakukan modifikasi terhadap rumus, tetapi juga memberhentikan user dari memasukan apapun segalanya ke dalam worksheet. Terkadang anda tidak menginginkannya. Untuk itu ada 3 solusi yaitu:

1. Mengunci Sel Berisi Formula Saja
2. Gunakan data validation
3. Auto Toggle proteksi sheet dengan VBA

Dalam praktekanya dari ketiga cara di atas tidak ada satu pun yang benar-benar kebal terhadap gangguan. Namun tidak ada salahnya bagi kita untuk memahami dan memanfaatkannya.

Mengunci Sel Berisi Formula Saja

Secara garis besar, tahapannya sebagai berikut:

1. Setting semua cell pada posisi unlocked
2. Setting cell locked pada range yang diinginkan saja (sel berisi formula / rumus)
3. Setting proteksi sheet.

Secara default, semua sel dalam worksheet ter-setting dalam posisi terkunci (Locked). Seting Locked tidak berpengaruh apa-apa kecuali proteksi sheet sudah diaktifkan. Berikut adalah cara mudah untuk menerapkan proteksi worksheet sehingga hanya sel berisi formula saja yang dikunci dan di-protect:

• Seleksi keseluruhan sel dalam worksheet (misalnya dengan cara tekan Ctrl + A),.
• Kemudian klik kanan ➜ Format Cells ➜ masuk ke tab Protection
• Hilangkan centang pada check box locked
• Klik OK

Untuk mengunci sel yang berisi formula, ikuti langkah selanjutnya:

• Excel 2017 atau yang lebih baru: Tab Home ➜ Find Select ➜ Go To Special.
• Excel 2003 : menu Edit ➜ Go To ➜ Special
• Bisa juga dengan cara menekan shortcut (berlaku untuk semua Versi) : Tekan tombol short cut F5 atau Ctr + G
• Maka akan muncul dialog box Go To Special seperti terlihat dalam gambar di bawah ini.

• Dalam dialog box Go To Special, pilih opsi Formulas, jika diperlukan centang pilihan jenis formula di bawahnya (Numbers, Text, Logicals, Error, Jika anda ingin mengunci semua jenis formula. Biarkan semuanya tercentang). Lalu klik OK.

Setelah proses di atas, maka semua sel berisi formula akan terseleksi.

Klik kanan pada range terseleksi ➜ Format Cells ➜ masuk ke tab Protection – centang checkbox Locked. Ini akan menyebabkan semua range yang berisi formula saja yang di lock.

Setting locked cell tersebut tidak akan berpengaruh apa-apa jika  proteksi worksheet tidak diaktifkan.

Selanjutnya langkah proteksi worksheet.

Excel 2007 ke atas: dari tab Review ➜ klik Protect Sheet ➜ sehingga muncul jendela protect sheet:

Lakukan pengaturan misalnya membuat pasword, dan hal-hal yang diizinkan atau tidak untuk user. Lalu klik OK.

Metode di atas khususnya dapat menghemat banyak waktu serta mengeliminasi kemungkinan error karena kesalahan edit formula oleh user. Sayangnya penerapan metode ini juga menyebabkan user kehilangan kesempatan untuk memanfaatkan fitur-fitur lainnya yang sangat berguna, seperti sorting, formating dan sebagainya bahkan terhadap sel yang tidak di-lock sekalipun.

Data Validation

Penggunaan data validation tidak tergantung pada proteksi worksheet sama sekali, namun dapat dengan mudah mencegah edit formula.

Sayangnya data validation masih jauh dari kata tahan banting untuk mencegah user memasukan data tidak tervalidasi ke sel. User memang tidak bisa mengetik data tidak valid pada sel yang divalidasi. Namun user masih bisa copy paste data tidak valid pada sel yang divalidasi. Bahkan validasi sel bisa terhapus atau berubah sesuai sel yang dicopy paste.

Untuk memahami apa yang dimaksudkan silahkan lakukan langkah – langkah berikut dimulai dengan menyeleksi sel berisi formula yang harus diamankan.

Seleksi satu sel sembarang, kemdian tekan F5 atau Ctr+G untuk masuk ke jendelal Go To,

• Klik Special
• Tick Formulas
• Klik OK

Setelah langkah tersebut diatas, maka semua sel berisi formula akan terseleksi.

Langkah selanjutnya lakukan setting data validation

• Excel 2003:  menu Data ➜ Validation
• Excel 2007 atau yang lebih baru: Masuk ke Tab Data ➜ Data validation 
• Sehingga muncul jendela Data Validation
• Masuk ke tab setting,  pada kotak allow pilih custom dan pada kotak formula, masukan rumus ="" seperti dapat dilihat dalam gambar di bawah ini:

• Kemudian klik OK

Hasil dari metode tersebut akan mencegah kesalahan pengetikan yang tidak sengaja pada cell tervalidasi. Sekali lagi, ini bukan metode yang sempurna. Tehnik ini hanya dapat mencegah salah ketik yang tidak sengaja. Tetapi jika anda melakukan copy paste dari sel lain ke sel tervalidasi tersebut, maka setting yang kita buat akan tertimpa oleh sel yang di copy paste. Keunggulan dari cara ini adalah kita dapat mempertahankan fungsi fitur fitur excel lainnya dibandingkan dengan cara protect sheet.

Auto – Toggle Worksheet Protection

Cara ke-3 ini menggunakan kode VBA untuk proteksi worksheet secara otomatis ketika user sel aktif berisi formula. Dengan metode ini, user masih bisa menggunakan fitur-fitur excel ketika sel aktive sedang berada di sel yang tidak di-lock.

Caranya ikuti langkah berikut:

• Seleksi semua cell dengan menekan shortcut Ctr + A, kemudian klik kanan ➜ format cells ➜ tab protection ➜ hilangkan centang Locked
• Seleksi range formula yang akan di proteksi.  Jika posisi formula tersebar maka lakukan langkah berikut: Tekan Ctrl + G, atau tekan F5 untuk membuka jendela Go To, kemudian klik Special ➜ centang Formula ➜ klik Ok
• Kemudian tekan Ctrl + 1 atau Klik kanan ➜ format cells ➜ Protection ➜ centang Locked.  Hal ini akan menerapkan setting locked pada range sel yang diseleksi (range sel berisi formula)
• Selanjutnya Klik kanan tab sheet kemudian klik view Code

• Kemudian masukan kode berikut kedalam modul worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect
Else
Me.Unprotect
End If
End Sub


Jika diperlukan, anda juga bisa menambahkan pasword pada kode VBA. Misalnya paswordnya =
“abc” (tanpa tanda kutip), maka code VBA menjadi sebagai berikut:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect pasword:="abc"
Else
Me.Unprotect pasword:="abc"
End If
End Sub


Setelah code terpasang maka secara otomatis worksheet akan terproteksi ketika sel aktive berada pada sel yang berisi formula (setting locked). Tentu saja dengan syarat setting macro enabled.
Jika menggunakan tenhik ke-3 ini, jika anda menggunakan versi excel 2007 atau yang lebih baru maka file harus disimpan dengan extensi .xlsm atau .xlsb

Meskipun bekerja cukup baik, cara ini tidak lepas dari kelemahan. Target dalam kode, mereferensikan sel aktif. Jika kita menyeleksi sejumlah range dan sel aktif nya adalah sel yang tidak di lock maka kita bisa menghapus semua data dalam range yang diseleksi meskipun dalam range tersebut ada sel yang di lock.

Sampai disini mudah-mudahan uraian mengenai beberapa cara untuk proteksi formula atau rumus ini bisa mudah difahami. Mau pilih cara yang mana? Silahkan pilih mana saja sesuai preferensi anda. Semoga bermanfaat.

Dapatkan tips excel yang sangat berguna:

• Cara Membatasi Scroll Area
• Cara Mencegah Save As, Print, dan Insert Sheet
• Co-Cote Senjata Pamungkas Pejuang Insert Comment

Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc