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

Saturday, August 27, 2016

Rumus Excel Tidak Berfungsi ? Inilah Penyebabnya!


Pernahkan anda mengalami kasus rumus excel yang tidak berfungsi, tidak bekerja atau tidak mengkalkulasi dengan benar padahal sudah dituliskan dengan benar?

Bagi user excel yang belum mengetahui akar permasalahannya mungkin akan merasa sedikit frustasi dan bertanya-tanya, kenapa ini?





Tenang… Coretan berikut akan mengupas tuntas beberapa permasalahan yang menyebabkan rumus excel tidak menjalankan fungsinya untuk mengkalkulasi dengan benar.

Silahkan di cek juga tutorialnya dalam video berikut:


Biasanya kita baru menyadari rumus excel yang tidak bekerja ketika merubah salah satu data pada referensi sel tetapi nilai dalam cell lainnya yang terkait tidak ikut berubah. Dengan kata lain hasil kalkulasinya tidak terupdate serta merta secara otomatis


Kasus ini juga dapat ditandai ketika mengcopy rumus dari sebuah sel ke sel lainnya. Nilai dalam sel yang di-paste tetap sama dengan sel yang di-copy meskipun seharusnya hasilnya berbeda.


Apakah penyebabnya?

Setidaknya ada 2 hal yang paling sering menyebabkan rumus excel  tidak berfungsi atau tidak mengkalkulasi dengan benar, yaitu:

1. Automatic Calculation tidak aktif
2. Adanya Circular Reference

Baiklah mari kita telaah satu persatu dari kedua masalah tersebut yang menyebabkan rumus excel tidak tidak bekerja semestinya.

Rumus Excel Tidak Berfungsi Karena Automatic Calculation Tidak Aktif.






Update nilai hasil rumus dalam excel sebenarnya melalui sebuah proses kalkulasi yang secara default biasanya terupdate otomatis jika ada perubahan nilai dalam referensi rumus. Namun proses kalkulasi ini tidak selamanya dilakukan secara otomatis.

Nah hal inilah yang kadang tidak disadari atau tidak diketahui oleh pengguna excel, sehingga sebagian user (mungkin) merasa sedikit frustasi begitu menghadapi kasus ini.

Dalam excel sebenarnya ada 3 pilihan setting kalkulasi yaitu:

  1. Automatic : Semua nilai dari rumus excel akan dikalkulasi otomatis setiap kali ada perubahan. Update otomatis terjadi pada semua sheet dan semua file excel yang sudah dibuka
  2. Automatic Except For Data Table: Update otomatis hanya pada sel yang ada keterkaitan rumus dengan sel yang dirubah nilainya
  3. Manual: Rumus excel tidak akan dikalkulasi sebelum dieksekusi secara manual (menekan F9)

Dari ketiga hal di atas, penyebab kenapa rumus excel tidak bekerja dengan benar adalah karena calculation setting-nya di set manual. Jadi, sebenarnya rumus excel tersebut bukan tidak berfungsi, tetapi diberhentikan sementara proses kalkulasinya sebelum  diperintahkan oleh user secara manual.

Pertanyaanya: Jika default setting kalkulasi adalah Automatic. Lalu kenapa setting kalkulasi di excel anda tiba-tiba bisa berubah menjadi manual?

Pertama: Hal ini bisa disebabkan karena anda membuka file excel dimana author atau pembuat file tersebut sengaja men-setting kalkulasi manual. Jika file tersebut dibuka pertama kali maka rumus excel dalam file lainnya yang dibuka berikutnya juga tidak dapat terkalkulasi secara otomatis meskipun tidak di-setting manual.

Kedua: Setting kalkulasi excel berubah menjadi manual bisa juga disebabkan karena adanya prosedur macro yang dijalankan untuk merubah calculation setting menjadi manual. Macro ini bisa berjalan, baik dengan sepengetahuan pengguna (misalnya dengan menekan tombol tertentu) maupun tidak.

Berikut contoh macro yang bisa merubah secara diam-diam  setting kalkulasi menjadi manual pada saat sebuah file yang mengandung code macro tersebut dibuka. Jadi hati-hatilah dalam menggunakan file macro excel.

Private Sub Workbook_Open()
   Application.Calculation = xlCalculationManual
End Sub

Kedua hal penyebab perubahan seting kalkulasi menjadi manual secara tiba-tiba seringkali tidak disadari. Pengguna excel biasanya baru menyadarinya ketika menemukan nilai rumus excel tidak terupdate seketika ketika ada perubahan  pada salah satu sel yang terkait ke rumus.

Bagaimana Cara Mengatasinya?



Untuk mengakali masalah ini, mungkin beberapa user yang menyarankan untuk memperbaiki satu persatu sel yang nilai nya  “tidak nyambung” dengan rumusnya. Caranya dengan menekan F2 dilanjutkan menekan ENTER.

Cara tersebut memang dapat menghasilkan kalkulasi yang benar. Tapi ingat “hanya untuk satu sel saja” per satu kali proses, bagaimana jika ada ribuan rumus dalam lembar kerja excel anda? Cara tersebut tentunya bukan solusi yang tepat karena tidak menyelesaikan akar masalahnya,  dan hanya buang-buang waktu saja.


Karena akar permasalahannya adalah seting kalkulasi, maka solusi yang tepat adalah mengatur settingan excel dengan benar, yaitu: Ubah calculation setting menjadi Automatic


Untuk excel 2003 dapat dilakukan melalui menu Tools –> Options… . Kemudian pilih tab Calculation dan pilih Automatic. Lalu tekan OK sesuai ilustrasi berikut:

Calculation Setting Excel 2003


Sedangkan untuk Excel 2007, 2010, 2013 dan 2016, seting kalkulasi dapat dilakukan melalui Tab Formulas, kemudian klik Calculation Options dan pilih Automatic.

Perhatikan ilustrasi berikut menggunakan excel 2007, ini mirip dengan excel 2010 s.d 2016

Calculation Setting  excel 2007,2010,2013 dan 2017


Merubah setting kalkulasi manual menjadi otomatis juga dapat dilakukan menggunakan macro. Berikut contoh code macro-nya:

Sub setKalkulasiOtomatis()
Application.Calculation = xlCalculationAutomatic
End Sub

Kita juga dapat mempertahankan proses kalkulasi secara manual jika dikehendaki. Hal ini dapat dilakukan dengan menekan F9. Cara ini juga sangat efektif untuk memastikan bahwa penyebab rumus tidak terupdate pada excel  adalah Memang karena seting kalkulasi manual. Jika ada penyebab lain maka F9 tidak akan berfungsi. Seandainya ini terjadi maka perlu dicari lagi penyebab lainnya.

Rumus Excel Tidak Berfungsi Karena Circular Reference





Kasus yang satu ini pun cukup sering dialami oleh pengguna excel biasanya terjadi karena kesalahan yang tidak disengaja ketika mengetik atau meng-copy rumus. Jika anda menerima “pesan cinta” seperti ilustrasi berikut pada saat bekerja dengan excel, maka dipastikan bahwa spreadsheet anda sedang mengalami masalah circular reference.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula’s result, creating a circular reference. Try one of the following:
  • If you accidentally created the circular reference, click OK. This will display the Circular Referece toolbar and help for using I to correct your formula
  • For more information about circular reference and how to work with them, click help
  • To continue leaving the formula as its, click Cancel


Mengatasi Error Circular Reference

Circular Reference terjadi jika formula dalam sebuah sel mengambil referensi dari sel itu sendiri baik secara langsung maupun secara tidak langsung. 

Contoh : jika pada sel A2 dituliskan rumus =SUM(A1:A20) maka dapat dikatakan bahwa rumus tersebut bersifat circular reference karena sel A2 merupakan bagian dari range A1:A20.

Meskipun masalahnya hanya pada satu sel dan terletak dalam sheet yang berbeda.Kadang-kadang hal ini bisa menyebabkan problem kalkulasi pada bagian lainnya dalam sebuah file excel.

Lalu bagaimana cara mengatasi masalah Circular Reference?

Untuk mengatasi masalah ini tentunya kita harus langsung bertindak pada akar masalahnya yaitu sel – sel yang memiliki rumus circular reference. 

Pada saat kita membuat kesalahan dengan membuat rumus circular reference, biasanya excel langsung memberitahu kita dengan “pesan cinta” seperti ilustrasi di atas. Jika begini, kita bisa langsung memperbaiki rumus pada saat itu juga.

Namun bagaimana jika rumus circular reference ini dibiarkan dari awal, dan kita tidak tahu lagi dimana posisi selnya? Jika kita ingin memecahkan problem ini dengan tuntas, maka mau tidak mau, biang kerok rumus circular  reference harus ditemukan. Bagaimana caranya?

Menemukan Circular Reference pada Excel 2003: 


klik menu View – Toolbars – Circular Reference
Toolbar Circular Reference dalam Escel 2003

Jika circular reference tidak ditemukan dalam list toolbars, maka kita harus memunculkannya melalui customize toolbar dengan cara:

Klik menu View –> Toolbars –> Customize –> kemudian aktifkan ceklist circular reference
Memunculkan Toolbar Circular Reference

Setelah langkah diatas, maka akan muncul tool bar Circular Reference:

Cara Menemukan Circular Reference Excel 2003


Dengan menggunakan bantuan toolbars inilah kita dapat menemukan biang kerok sel-sel yang memiliki rumus circular reference. Caranya: klik kotak list yang akan menunjukan kepada kita sel-sel yang bermasalah circular reference, pergi ke semua sel bermasalah tersebut dan perbaiki rumusnya.

Menemukan Circular Reference pada Excel 2007, 2010, 2013 dan 2016


Untuk excel versi ini caranya lebih simple lagi: Masuk ke tab Formulas –> klik Error Checking Circular References –> dijumpai list sel circular –> klik dan perbaiki rumus dalam sel-sel tersebut.

Cara Menemukan Circular Reference Excel 2007

Ketahui akar masalah dan perbaki.  Inilah apa yang ingin disampaikan penulis dari artikel excel ini. Dan untuk mengetahui akar masalah excel, kita harus memahami cara kerja excel itu sendiri. Dari coretan singkat ini diharapkan dapat menambah pemahaman kita tentang cara kerja rumus atau formula excel.

Harapan spesifik dari tulisan ini adalah pembaca dapat memahami kenapa rumus excel tidak berfungsi meskipun sudah dituliskan dengan benar. Dari inilah maka kita bisa mencari solusi yang tepat, cepat dan tuntas untuk memecahkan masalahnya.

Demikian semoga bermanfaat.
Belajar Excel.. Excellent!

Artikel terkait:
Cara Mudah Memahami Rumus VLOOKUP dengan Studi Kasus





Referensi:
https://support.office.com/en-US/article/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123
https://support.office.com/en-US/article/Formula-calculation-performance-and-error-handling-options-805928AA-6FC0-4787-B41C-FCDB65113423



95 comments:

  1. Replies
    1. sip gan, disini juga bagus tutoriallnya mudah juga, https://www.lutradiksi.xyz/

      Delete
  2. makasih banyak maz udah ngasih solusi secara detail..bermanfaat skali,mudah d pahami..👍🏻

    ReplyDelete
    Replies
    1. Terimakasih kembali, sudah brrkunjung.. :-)

      Delete
  3. Bagus sekali artikel nya .. terimakasih ilmu nya ..

    ReplyDelete
  4. alhamdulillah..dari sekian blog dan artikel ini yang membantu , terima kasih

    ReplyDelete
  5. Terimakasih..sangat bermanfaat.

    ReplyDelete
  6. i love you hehehehe .....

    ReplyDelete
  7. terimakasih..semoga ilmu yang ditularkan akan berkembang terus..dan menjadi amal baik anda..salam.

    ReplyDelete
  8. Gan saya punya masalah , rumus excel #Ref karena merubah nama file yg saya link di workbook itu , gimana ya solusi nya biar terupdate sendiri mengikuti nama yg telah dirubah.

    dan bisa ga ya gan meskipun data link nya sudah dihapus tetapi data di workbook tidak rusak

    mohon penjelasan nya

    ReplyDelete
  9. gan saya mau nanya kenapa rumus vlookup saya gak bisa terus, selallu gagal contoh(=vlookup(C2,'[data invest Asmo TGR.xls]Invest Asmo Tangerang'!$D$2:$G$166,4,0) hasilnya selallu kaya gini

    ReplyDelete
  10. Terimakasih kak informasinya, bermanfaat banget.. kebetulan saya di kantor mengalami kendala dengan rumus excel yang tertera di atas.. sekali lagi terimakasih informasinya

    ReplyDelete
  11. Terima kasih Mas... Sangat membantu... :)

    ReplyDelete
  12. makasih gan masukan ilmunya,....

    ReplyDelete
  13. Maaf kak, di komputer saya ms.office 2013, tapi circular reference nya g bisa di klik, tlg jawabannya

    ReplyDelete
    Replies
    1. Maaf, yang tidak bisa di klik sel nya atau apanya ya, atau sel circularnya tidak ketemu?

      Delete
    2. nahh sya sma kyak kasus mba ini,, circular reference nya gk bisa di klik(gk berwarna biru) yg bsa d klik cmn error checking sama trace error 😭

      Delete
    3. Berarti file excel nya bebas dari rumus sircular.
      Klw mo bisa di klik, buat saja rumus sircular.
      Misal di sel A1 buat rumus =A1 atau =SUM(A1:B1)
      Trus cek lagi, apakah tombol circular reference masih belum bisa di klik?

      Delete
  14. ka circular refrence nya ga bisa diklik yang bisa diklik cuma eror checking sama trace eror ajj itu gimana ka ?

    ReplyDelete
  15. Terimakasih Banyak Mas

    ReplyDelete
  16. mas mau tanya. sy kan pake macro di sebagian sheet sebGiNnya sheetnya tdk. nah yg jadi masalah yg sheet yg tdk menggunakan macro ini pada saat sy masukkan rumus tdk berfungsi padahal setingan calculasinya sudah otomatis. trus untuk circular referennya tdk bsa di klik. kira2 masalahnya dimna ya mas?

    ReplyDelete
  17. mas mau tanya nih sayakan pake macro di sebagian sheets dan sebagiannya tdk nah sheet yg tdk pake macro ini yg bermasalah setiap saya masukkan rumus itu tdk berfungsi padahal calculationnya sudah otomatis dan circular referennya tdk bisa di klik. kira2 solusinya gmna ya mas?

    ReplyDelete
    Replies
    1. Kalau boleh tahu, code macronya apa ya mas... Dan fungsi yg dipakai untuk rumus apakah fungsi bawaan excel atau udf?

      Delete
  18. Nice artikelnya, sangat membantu problem yg sering saya temui dengan pekerjaan saya sehari hari.
    Trims.

    ReplyDelete
  19. Akang / teteh terimakasih banyak, tulisannya membantu sekali.
    Sekali lagi hatur nuhun

    ReplyDelete
  20. Terima kasih banyak massss! Sangat bermanfaat

    ReplyDelete
  21. tapi dia bukan circular reference, bro. rumus sudah betul, dicek di formula, ada angka hasilnya, tapi di kolom tabelnya muncul 0

    ReplyDelete
  22. tapi dia bukan circular reference, bro. rumus sudah betul, dicek di formula, ada angka hasilnya, tapi di kolom tabelnya muncul 0

    ReplyDelete
  23. Mas mau tanya kalau fungsi count if totalannya salah kenapa ya ?

    ReplyDelete
  24. Thanks Info nya
    Sangat bermanfaat

    ReplyDelete
  25. akhirnya dapat solusi...
    matur tengkyu infonya

    ReplyDelete
  26. Kenapa ya pas klik erroe checking, si circular references nya gabisa di klik?thanks before√

    ReplyDelete
  27. Klw circular referencenya tdk bisa di klik, berarti memang circular reference nya tidak ada

    ReplyDelete
    Replies
    1. kalo tidak adaa,, harus bagaimana biar dia ada,, hruskah ke service computer???

      Delete
    2. Berarti file excel nya bebas dari rumus sircular.
      Klw mo bisa di klik, buat saja rumus sircular.
      Misal di sel A1 buat rumus =A1 atau =SUM(A1:B1)
      Trus cek lagi, apakah tombol circular reference masih belum bisa di klik?

      Delete
  28. terima kasih, artikelnya sangat membantu saya dalam memecahkan masalah circular reference.
    laporan saya jadi kembali ke jalan yang lurus :')

    ReplyDelete
  29. Terimakasih atas share ilmunya...

    ReplyDelete
  30. Mo saya coba, Terima kasih bagi ilmunya.🙏

    ReplyDelete
  31. Alhamdulillah, terimakasih atas ilmunya🙏

    ReplyDelete
  32. terima kasih banyak..sangat bermanfaat

    ReplyDelete
  33. Terimakasih banyak..alhmdulillah sangat membantu

    ReplyDelete
  34. Alhamdullah makasih infonya sangat membantu....

    ReplyDelete
  35. Mas knp di hasil link antar sheet tidak muncul angka tapi cuma strep datar?

    ReplyDelete
  36. Thank you yaa... membantu sekali

    ReplyDelete
  37. circular references sy gak bisa di klik itu gimanaa caranyaaaaaa 😭😭

    ReplyDelete
    Replies
    1. Berarti file excel nya bebas dari rumus sircular.
      Klw mo bisa di klik, buat saja rumus sircular.
      Misal di sel A1 buat rumus =A1 atau =SUM(A1:B1)
      Trus cek lagi, apakah tombol circular reference masih belum bisa di klik?

      Delete
  38. Mantap bos... Sangat membantu... Trimakasih solusinya. Kerja jadi lancar lagu

    ReplyDelete
  39. makasih mas, mempir gan tutorial percetakan www.lutradiksi.web.id

    ReplyDelete
  40. Thanks ya kak, hampir panik aku kali ini karena udah dapet 6 file dan setiap file ada page yg lumayan banyak. Akhirnya ketemu artikel ini, semoga ilmunya bermanfaat ya kak

    ReplyDelete
  41. Terima kasih... tulisannya sangat membantu

    ReplyDelete
  42. Terimakasih banyak mas bro...
    Sampai stres, kirain rusak excel saya...

    ReplyDelete
  43. awalnya sih pusing 100 keliling.... setelah baca yang ini ... ngkak wkwkwk...

    ReplyDelete
  44. Circular references yang jadi masalahnya

    ReplyDelete
  45. mw ty gan, kita ngelink antar sheet pakai excel 2016 tp sering muncul rumusnya tp ga muncul data dari sheet yang kita maksud...mhn pencerahaan gan
    nuwun...cah solo

    ReplyDelete
    Replies
    1. Kemungkinan 1: cell di format text --> ganti menjadi general
      Kemungkinan 2 : di setting show formula--> tekan tombol short cut Ctrl + `

      Delete
  46. sangat membantu sekali, semoga semakin sukses.

    ReplyDelete
  47. terimakasih, sangat membantu

    ReplyDelete
  48. Hi author, terimakasih banyak yah sangat membantu sekali. Sebelumnya saya selalu pindah ke excel baru buat ngatasi ini. Tapi, setelah baca ini ternyata cukup one klik F9 or ke formula automatic calculation aja ternyata wkwkw. Thankyou so much

    ReplyDelete
  49. Alhamdulillah ketemu masalahnya. Thanks min. Mantap

    ReplyDelete
  50. Pagi gan,
    Kalo saya memiliki kendala disaat link nya saya pencet breaklink itu bisa gk ada problem namun di dalam kolom "Edit Links" masih terdapat link ke file yang sudah saya delete tadi. bagaimana cara menghilangkan link yang seperti itu ya gan?
    Terima kasih

    ReplyDelete
  51. terima kasih sangat membantu.. ternyata ada circular references

    ReplyDelete
  52. Thanks a lot, membantu bangetttt. Udah stress harus F2+enter data-data di kantor huhuhu

    ReplyDelete
  53. sangat sangat membantu. terimakasih

    ReplyDelete
  54. ALHAMDULILLAH, BGAIMANA SOLUSI KALAU RUMUS TIDAK BERFUNGSI, MUNGKIN ADA SULUSINYA, SAYA DARI FLORES NTT

    ReplyDelete

Terimakasih sudah berkunjung dan membaca blog ini. Silahkan berkomentar.