Excel memiliki debugger formula bawaan dan hampir tidak ada yang tahu itu ada

Anda mungkin pernah mengalami saat di Excel ketika angka-angka Anda tidak sesuai. Solusi yang umum adalah menyisir setiap sel satu per satu atau memulai perhitungan Anda dari awal. Setidaknya, saya pikir itu satu-satunya opsi yang saya miliki sampai saya menemukan bahwa Excel memiliki seperangkat alat khusus untuk situasi seperti ini.

Pergi ke tab Formulas di aplikasi desktop Excel, bukan versi web. Di antara fitur Function Library dan Calculation, Anda akan menemukan grup yang disebut Formula Auditing. Cukup mudah untuk terlewatkan, tetapi di dalamnya terdapat tujuh alat yang dapat membantu Anda mendiagnosis formula yang rusak dan mengetahui dengan tepat di mana kesalahan terjadi.

Lacak Pendahulu dan Lacak Tergantung

Cari tahu dari mana angka Anda berasal dan ke mana mereka pergi

Kedua fitur ini adalah titik awal yang alami. Jejak Pendahuluan menggambar panah visual dari setiap sel yang memberikan data ke formula yang Anda pilih, jadi jika formula Anda di G5 mengambil nilai dari B2, D3, dan F4, Anda akan melihat garis yang menghubungkan mereka. Jejak Tergantung bekerja secara terbalik: pilih sebuah sel, klik tombolnya, dan Excel akan menampilkan setiap formula di tempat lain di lembar yang bergantung pada nilai sel tersebut.

Pewarnaan membuat kedua fitur menjadi intuitif. Panah biru berarti semua dalam rantai itu bebas dari kesalahan, sementara panah merah menunjukkan sumber masalah. Dalam sebagian besar kasus, Anda cukup mengikuti garis merah untuk menemukan masalahnya.

Jika sebuah panah menunjuk ke ikon lembar kerja alih-alih sel, referensi tersebut berada di lembar atau buku kerja yang berbeda, meskipun buku kerja itu perlu dibuka agar Excel dapat menelusurnya. Anda juga dapat terus mengklik salah satu tombol untuk mengupas lapisan tambahan dan bergerak lebih dalam ke rantai ketergantungan setiap kali.

Setelah selesai, klik Hapus Panah untuk membersihkan garis dari lembar Anda. Jika Anda ingin lebih selektif, kembangkan menu dropdown dan hapus hanya panah pendahulu atau panah penerus.

Tampilkan Rumus

Beralih dari hasil ke sintaks untuk menemukan inkonsistensi

Fitur ini adalah fitur lain yang sederhana tetapi berguna. Ketika Anda mengklik Tampilkan Rumus (atau menekan Ctrl + `), setiap sel yang berisi rumus akan menampilkan sintaks rumusnya, bukan hasil perhitungannya. Saya menemukan ini sangat berguna untuk dua hal: dengan cepat menemukan ketidaksesuaian di sepanjang baris rumus dan menangkap sel di mana seseorang mengetikkan nilai langsung alih-alih menggunakan rumus yang benar. Misalnya, jika satu sel menampilkan angka biasa sementara sel-sel sekitarnya menampilkan rumus SUM, Anda akan tahu ada yang tidak beres.

Setelah Anda memastikan bahwa sintaksnya benar, klik Show Formulas lagi atau gunakan pintasan yang sama untuk mengembalikan semuanya ke kondisi normal.

Pemeriksaan Kesalahan

Pemeriksaan bawaan untuk referensi yang rusak, logika melingkar, dan kesalahan yang jelas

Menu Pemeriksaan Kesalahan berisi tiga alat, masing-masing dirancang untuk menangani jenis rumus yang rusak yang berbeda. Dialog utama Pemeriksaan Kesalahan bekerja sedikit seperti pemeriksa ejaan, memindai seluruh lembar Anda untuk kesalahan rumus Excel yang umum seperti #DIV/0! atau #REF! dan membimbing Anda melalui mereka satu per satu. Saat menemukan masalah, Anda akan mendapatkan opsi untuk memperbaikinya, meninjau satu per satu, atau mengabaikannya.

Trace Error adalah fitur yang lebih terarah. Jika sel tertentu sudah menampilkan kesalahan, Excel menggambar panah merah melalui rantai perhitungan untuk menunjukkan tepatnya di mana data yang salah dimasukkan ke dalam persamaan. Circular References, sementara itu, mencari sel yang merujuk pada dirinya sendiri, baik secara langsung maupun melalui rantai sel lainnya, dan memungkinkan Anda melompat langsung ke referensi yang bermasalah untuk meluruskannya.

Evaluasi Rumus

Tonton Excel memecah formula kompleks satu per satu

Jika ada satu alat dalam kotak alat ini yang menjadi favorit saya, itu adalah Evaluate Formula. Fitur ini mungkin adalah hal yang paling mendekati apa yang dimiliki Excel sebagai debugger langkah-demi-langkah yang nyata. Pilih sel yang berisi XLOOKUP, pernyataan IF yang kompleks, fungsi bersarang, atau formula lanjutan lainnya, klik Evaluate Formula, dan sebuah kotak dialog akan terbuka untuk memandu Anda melalui perhitungan satu langkah pada satu waktu.

Di dalam kotak dialog, satu bagian dari rumus muncul dengan garis bawah. Bagian yang digarisbawahi itu adalah bagian yang akan dievaluasi oleh Excel. Setiap kali Anda mengklik Evaluasi, bagian yang dipilih akan menghasilkan hasil yang ditampilkan dengan huruf miring, sementara Excel beralih ke langkah berikutnya dalam perhitungan. Terus klik, dan akhirnya, Anda akan sampai pada hasil akhir.

Jika tersedia, Anda juga dapat menggunakan Step In untuk masuk ke sel atau sub-formula yang dirujuk dan Step Out untuk kembali ke formula induk.

Jendela Pengawas

Tetap tampilkan nilai penting tanpa harus berpindah antar lembar

Alat terakhir dalam grup Pemeriksaan Formula dirancang untuk buku kerja besar dengan banyak lembar. Jendela Pantau membuka bilah alat mengambang di mana Anda dapat menambahkan sel mana pun yang ingin Anda pantau, terlepas dari lembar mana mereka berada dalam buku kerja. Anda bahkan dapat memantau sel dari buku kerja yang sama sekali berbeda.

Setiap entri menampilkan alamat sel, nilai saat ini, dan rumus dalam satu panel, dan Jendela Pantau tetap terlihat bahkan saat Anda berpindah antar lembar. Jadi, jika Anda menyesuaikan input di Lembar 1 dan perlu melihat bagaimana total berjalan di Lembar 5 merespons secara real time, Anda tidak perlu terus bolak-balik. Sebaliknya, Anda dapat memantau semuanya dari satu tempat. Anda juga dapat mengklik dua kali entri apa pun di Jendela Pantau untuk langsung melompat ke sel itu kapan pun Anda membutuhkannya.

Ketika Excel mulai bertingkah aneh, mulailah di sini

Anda mungkin tidak akan bisa menguraikan setiap formula yang rusak hanya dengan menggunakan grup Pemeriksaan Formula saja. Meski begitu, alat-alat ini bisa menyelamatkan Anda dari menghabiskan berjam-jam mengklik sel dan berharap secara tidak sengaja menemukan masalahnya. Mereka ada karena suatu alasan: Excel dibuat untuk menangani kompleksitas, dan kompleksitas membutuhkan alat debugging yang sesuai.

Lain kali ketika sebuah workbook mulai berperilaku aneh, buka tab Rumus dan buka grup Audit Rumus. Alih-alih menebak di mana kesalahan terjadi, Anda bisa membiarkan Excel menunjukkan apa yang sudah diketahuinya.