Featured Post

Identifikasi Formula Dengan Conditional Formating

Bagaimanakah cara mengenali dan membedakan jenis konten sel, apakah berupa nilai statis atau sebuah formula? Cara pertama yang umumnya dig...

Friday, February 21, 2020

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.

Cara menggunakan fungsi ISFORMULA


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. 

Identifikasi Konten Sel

  • 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.


Cara Conditional Formating Mengenali Formula

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

Penerapan fungsi ISFORMULA dalam Conditional Formating.

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:


Sunday, February 9, 2020

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:



Sekian, semoga bermanfaat.
Salam…

Sunday, February 2, 2020

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:



Friday, January 31, 2020

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:




Thursday, January 9, 2020

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.

Menyeleksi Sel Berisi Rumus


  • 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:

How To Protect Worksheet


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:
Melindungi Formula dengan Data Validation

  • 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


How To Show VBA 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


Auto Toggle Sheet Protection With VBA


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:

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

Saturday, October 5, 2019

Cara Membatasi Scroll Area Pada Worksheet

Pada Excel versi 2007 atau yang lebih baru, area worksheet dibatasi oleh jumlah kolom maksimal 16.364 dan jumlah baris maksimal 1.048.576.  Namun pada kenyataanya, user lebih sering menggunakan sebagian kecil saja dari jumlah sel yang tersedia dalam worksheet. Penggunaan sebagian area dalam worksheet tanpa pembatasan scroll area terkadang cukup menyulitkan dalam melakukan observasi lembar kerja excel. Karena itulah maka disarankan untuk membatasi scroll area.

Keuntungan pembatasan scroll area:


  • Pembatasan scroll area memudahkan dalam melakukan observasi dan navigasi workhsheet.
  • Jika anda pembuat tempate laporan, maka anda bisa menyembunyikan data yang tidak ingin diketahui oleh user di luar lokasi scroll area.


Pembatasan scroll area dapat dilakukan dengan beberapa cara, antara lain:

  • Menyembunyikan kolom dan baris tidak terpakai
  • Menentukan valid range
  • Mengaktifkan range terpakai (used range)



Menyembunyikan kolom dan baris tidak terpakai


Tehnik paling sederhana untuk membuat batasan lembar kerja excel adalah dengan cara menyembunyikan baris dan kolom tidak terpakai.

Langkahnya cukup mudah:


  • Seleksi semua kolom yang tidak digunakan, kemudian klik kanan ➨ hide
  • Dengan cara serupa, seleksi semua baris yang tidak digunakan, kemudian klik kanan ➨ hide


Hasilnya dapat dilihat seperti screenshot di bawah ini.

Menyembunyikan Kolom dan Baris


Dari gambar di atas, kita dapat melihat, excel hanya menyampilan kolom dan baris untuk area yang digunakan saja, yaitu baris 1 s/d 15 dan kolom A s/d H. Hal tersebut karena area yang tidak digunakan sudah disembunyikan (di-hide).


Menentukan Valid Range Spesifik


Metode kedua untuk menetapkan batasan scroll area adalah dengan cara menentukan valid range spesifik. Valid range dapat di-setting melalui property worksheet.

Caranya:


  • Klik kanan pada tab sheet yang akan ditentukan sroll area nya.
  • Klik view Code, sehingga akan masuk ke VBA editor. 
Cara Memunculkan VBA Editor

  • Di dalam vba editor, cari jendela properties. Jika jendela propoerties belum terlihat, tekan F4 untuk memunculkannya.
  • Pada jendela properties, pada field ScrollArea, ketikan valid range yang anda inginkan. Contoh pengisian seperti dalam gambar di bawah ini.
Mengatur scroll area melalui properties worksheet




Keterangan: tanda absolute ($) akan muncul otomatis meskipun kita mengetik valid range tanpa tanda dolar $.

Setelah selesai, kembali ke worksheet dan kita bisa buktikan bahwa scroll range akan dibatasi sesuai valid range yang kita ketikan di jendela properties. Jika scroll area yang kita gunakan sedikit saja atau kurang dari ukuran range yang terlihat (visible range), maka kolom dan baris diluar scroll area masih akan nampak. Namun kita  tidak bisa menyeleksi sel / range dan mengetik pada sel diluar scroll area. Silahkan dibuktikan sendiri.

Sayangnya, kita tidak bisa menyimpan setting scrollarea yang dibuat dengan cara edit properties karena scroll area akan di-reset kembali setelah close file.

Untuk mengatasi hal tersebut, kita bisa menggunakan code vba untuk menetapkan scroll area. Code ini ditempatkan pada even worksheet_activate sehingga akan dijalankan setiap kali worksheet yang disisipkan code ini diaktifkan.

Caranya:

  • Klik kanan pada tab sheet yang akan dibatasi sroll area nya.
  • Klik view Code, sehingga akan masuk ke VBA editor.
  • Pada jendela project explorer, double clik nama sheet yang akan dibatasi scroll area nya sehingga muncul muncul object sheet tersebut.
  • Ketik atau copy code berikut pada modul ojbect sheet:


Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:J50"
End Sub


Contoh penempatan code dapat dilihat pada screenshot di bawah ini.


Contoh code vba untuk mengaktifkan scroll area


Gambar di atas menunjukan contoh penempatan code untuk membatasi scroll area sheet 3  pada range A1:J50. Code berjalan setiap kali sheet 3 diaktifkan (berpindah dari sheet lain ke sheet 3).

Untuk membuat batasan scroll area pada sheet lainnya, silahkan masuk kembali ke VBA editor, kemudian pada jendela project explorer, double klik nama sheet yang akan disisipkan code. Selanjutnya ketik code yang sama dengan contoh sebelumnya pada modul object sheet yang diinginkan.

Kita juga bisa berexperimen dengan merubah range scroll area. Silahkan range A1:J50 diedit kembali dengan range yang anda inginkan.

Bagaimana Jika kita ingin mengedit sel di luar scroll area?

Sebagaimana penjelasan di atas, setelah kita setting scroll area maka kita tidak bisa mengedit sel di luar scroll area. Untuk itu kita harus menghapus setting scroll area terlebih dahulu jika ingin memodifikasi area sheet di luar scrollarea.

Kita bisa menghapus secara manual dengan cara menghapus scroll area pada property worsksheet. Cara yang lebih cepat dan disarankan adalah dengan menggunakan macro / VBA. Adapun codenya sangat sederhana.

Misalnya:

Sub deleteScrollArea()
ActiveSheet.ScrollArea = ""
End Sub


Untuk menempatkan code pada module VBA, ikuti langkah berikut:
  • Masuk ke jendela VBA dengan cara tekan Alt + F11
  • Jika module standar belum tersedia, Klik menu Insert ➨ Module
  • Maka akan muncul modul baru (misalnya : Module1)
  • Tempatkan code pada module 1. Bisa dilakukan dengan copy paste contoh code di atas, maupun dengan mengetiknya sendiri.


Screenshot di bawah ini menggambarkan langkah-langkah penempatan code pada modul standar.

contoh code vba scroll area



Setelah langkah di atas diikuti satu-persatu, maka kita bisa menghapus scroll range menggunakan makro.

Caranya:

  • Tekan Alt + F8 untuk memunculkan list Macro 
  • Pilih macro yang sudah kita buat sebelumnya (macro deleteScrollArea)
  • Lalu klik Run


Mengaktifkan Range Terpakai (Used Range)


Cara Ketiga dan yang paling fleksibel adalah dengan menetapkan used range sebagai scroll area. Untuk menempatkan code, caranya serupa dengan cara kedua yaitu:


  • Klik kanan tab sheet yang ingin di-setting scroll area nya
  • Pada module object sheet terpilih, ketik atau copy code berikut:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = Me.UsedRange.Address
End Sub


  • Setelah itu, keluar kembali dari VBA editor.


Karena code diatas akan dijalankan ketika sheet diaktifkan maka kita harus pindah dulu ke sheet lain, kemudian masuk kembali (mengaktifkan) ke sheet dimana code sudah disisipkan. Maka kita akan mendapati sheet dengan batasan scroll area sudah terpasang.

Jika kita ingin mengedit areal di luar used range, maka kita bisa mereset sementara scroll area dengan cara yang sudah dijelaskan sebelumnya

Sampai disini, mudahan-mudahan catatan excel mengenai cara membatasi scroll area dapat difahami dan semoga bermanfaat.
Thks

Artikel terkait:



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


Tuesday, October 1, 2019

Mencegah Save As, Print dan Insert Sheet

Sebagai pembuat sebuah template laporan, mungkin kita tidak menginginkan user melakukan tindakan tertentu. Misalnya melakukan save as, print, insert sheet, dan sebagainya. Dengan menambahkan sedikit code vba, kita bisa me-manage interaksi antara user dengan spreadsheet yang kita buat. Nah, jex-cel telah mencatat beberapa contoh code yang dapat digunakan untuk mencegah user menjalankan command tertentu di excel.

1. Mencegah Save As
2. Mencegah print
3. Mencegah print sheet tertentu
4. Mencegah insert sheet

Mencegah User Melakukan Save As


Atas dasar alasan tertentu, mungkin kita tidak ingin user melakukan save as dan mengganti nama file template yang kita buat. Untuk itu, kita bisa menyisipkan code pada module workbook.
  • Jika kita membuat file baru, maka save dulu file tersebut sebelum disisipkan code. Jika workbook baru belum di save, dan kemudian kita menyisipkan code cegah save as maka file tersebut tidak akan bisa di-save sama sekali.
  • Tekan Alt F11 untuk masuk ke VBA editor.
  • Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
  • Copy code berikut pada module object thisworkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim responUser As Long
If SaveAsUI = True Then
responUser = MsgBox("Maaf, file tidak bisa di save as dengan nama lain" _
& Chr(10) & "Apakah anda akan menyimpan saja?", vbQuestion + vbOKCancel)
Cancel = (responUser = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub



Letak code dalam modul VBA dapat dilihat dalam screenshot berikut:

Code VBA untuk mencegah Save As


Setelah code di copy paste atau diketik di modul object thisworkbook, silahkan coba lakukan proses Save As

Maka akan muncul pesan seperti pada gambar di bawah ini:

Pesan tidak bisa save as


Jika user menekan tombol ok, maka file akan disimpan biasa (bukan save as) dan tidak ada perubahan nama file. Jika user menekan cancel, maka perubahan tidak akan tersimpan.

Mencegah User Melakukan Print.


Karena melihat banyak kertas print berakhir di tempat sampah  atau tercecer / menumpuk di bawah meja kerja, kita bisa berinisiatif untuk membantu menghemat kertas dengan cara mencegah user untuk melakukan print. Untuk itu kita bisa menyisipkan code pada event before print di module thisworkbook.


  • Tekan Alt F11 untuk masuk ke VBA editor
  • Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
  • Copy code berikut pada module object thisworkbook.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Maaf anda tidak bisa print buku kerja ini", vbInformation
End Sub


Silahkan test dengan cara menjalankan command print (File --> Print --> Tombol Print)

Maka user akan melihat pesan pemberitahuan tidak bisa  print,  seperti gambar di bawah ini.

pesan tidak bisa print



Kita bisa saja menyisipkan code tanpa code pesan (msgbox) seperti berikut:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub


Code tersebut sebenarnya sudah cukup untuk mencegah user melakukan print. Namun jika tidak ada pesan pemberitahuan, maka kemungkinan besar user akan menyangka ada error dan mengejar orang IT untuk memperbaikinya. Dengan kata lain, msgbox meski opsional tetapi sangat penting untuk memberitahu user mengenai apa yang tidak boleh dilakukan maupun yang harus dilakukan sehingga bisa mencegah adanya kesalahpahaman.

Mencegah print sheet tertentu


Jika kita ingin mencegah user melakukan print sheet tertentu saja maka gunakan code berikut:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2"
Cancel = True
MsgBox "Maaf anda tidak bisa print " & ActiveSheet.Name, vbInformation
End Select
End Sub



pesan tidak bisa print sheet


Catatan:  dalam contoh di atas, nama sheet yang dicegah untuk diprint adalah "Sheet1", "Sheet2". Kita dapat merubah dan menambah sheet sesuai keperluan.
Misalnya:

Case "data 1", "data 2", "data 3"


Mencegah Insert Sheet


Excel sebenarnya sudah menyediakan fiture Protect Workbook’s Structure yang dapat mencegah user untuk bisa men-delete worksheet, merubah susunan sheet, merubah nama sheet dan sebagainya. Namun adakalanya kita hanya ingin mencegah user untuk menambahkan sheet baru dan tetap mengizinkan perubahan struktur  lainnya.

Untuk mencegah user menambahkan sheet baru, lakukan langkah seperti contoh sebelumnya. Bedanya hanya pada code yang digunakan.


  • Masuk ke VBA editor dengan cara tekan Alt + 11
  • Selanjutnya Masuk ke module object Thisworkbook dengan cara double klik object thisworkbook di jendela project explorer
  • Selanjutnya pada module object thisworkbook, copy code di bawah ini:



Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Maaf, anda tidak bisa menambahkan sheet baru", vbInformation
Sh.Delete
Application.DisplayAlerts = True
End Sub


Silahkan lakukan test dengan cara mencoba tambahkan sheet baru. Maka akan tampil pesan seperti screenshot di bawah ini.

pesan tidak bisa tambah sheet baru



Sekian dulu catatan mengenai contoh-contoh code untuk mencegah user melakukan tindakan tertentu pada excel, yaitu: mencegah save as, mencegah print dan mencegah insert sheet. Semoga bermanfaat.

Artikel terkait:
Teknik Find & Replace Text dalam Comment Box
Cara Entri Data Pada Beberapa Sheet Sekaligus
Cara Membuat Daftar Isi Otomatis

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