Cara Audit Formula Excel: Trace Precedents & Evaluate Formula

Audit formula Excel adalah cara nelusuri sel mana yang dipakai sebuah rumus dan sel mana yang bergantung ke situ. Gunanya: nemuin sumber error tanpa nebak-nebak.

Semua alatnya udah ada di Excel, di tab Formulas. Nggak perlu add-in, nggak perlu bayar.

Kamu bakal belajar empat alat: Trace Precedents, Trace Dependents, Evaluate Formula, dan Watch Window. Plus cara nemuin circular reference yang bikin file nge-freeze.

Kapan Kamu Butuh Audit Formula?

Empat situasi yang paling sering.

1. Total di laporan salah, tapi kamu nggak tau rumus mana yang bocor.
2. Muncul #REF!, #VALUE!, atau #N/A di sel yang kemarin masih normal.
3. Kamu warisin file dari orang lain dan rumusnya bertingkat 5 lapis.
4. Kamu mau ngehapus satu kolom tapi takut ada rumus lain yang ngandelin kolom itu.

Situasi keempat yang paling sering bikin kacau. Orang ngehapus kolom bantu, terus 40 rumus di sheet lain berubah jadi #REF! semua.

Cara Pakai Trace Precedents: Sel Ini Ngambil Data dari Mana?

Trace Precedents nunjukin sel mana aja yang jadi bahan hitung sebuah rumus. Panah biru bakal muncul dari sel sumber ke sel rumus.

1. Klik sel yang rumusnya mau kamu cek.
2. Buka tab Formulas.
3. Klik Trace Precedents. Shortcut: Alt + M + P.
4. Klik sekali lagi buat lihat lapisan berikutnya — sumber dari sumbernya.

Contoh: sel E10 isinya =SUM(D2:D9)*C1. Setelah Trace Precedents, panah bakal nunjuk dari blok D2:D9 dan dari C1 ke E10.

Kalau muncul ikon tabel kecil dengan panah putus-putus, artinya rumus kamu ngambil data dari sheet atau file lain. Klik dua kali panahnya, dialog Go To bakal nunjukin alamat lengkapnya.

Buat ngilangin panahnya: klik Remove Arrows di tab yang sama.

Cara Pakai Trace Dependents: Siapa yang Bergantung ke Sel Ini?

Ini kebalikan Trace Precedents. Dia nunjukin sel mana aja yang hasilnya bakal berubah kalau kamu ngutak-atik sel yang lagi dipilih.

1. Klik sel yang mau kamu cek.
2. Tab Formulas → Trace Dependents. Shortcut: Alt + M + D.

Pakai ini sebelum ngehapus baris, kolom, atau sel. Kalau panahnya nyebar ke mana-mana, jangan hapus.

Aku bikin ini kebiasaan: tiap mau hapus kolom bantu di file warisan, Trace Dependents dulu. Sepuluh detik, tapi nghemat setengah jam benerin #REF!.

Cara Pakai Evaluate Formula: Jalanin Rumus Pelan-pelan

Ini alat paling berguna buat rumus bertingkat yang panjang. Evaluate Formula ngitung rumus kamu selangkah demi selangkah, persis kayak cara Excel ngitungnya.

1. Pilih sel yang rumusnya bermasalah.
2. Tab Formulas → Evaluate Formula.
3. Bagian rumus yang digarisbawahi itu yang bakal dihitung duluan.
4. Klik Evaluate buat ngitung bagian itu. Hasilnya nggantiin bagian yang digarisbawahi.
5. Ulangi klik sampai rumusnya kelar.

Contoh rumus yang bikin pusing:

=IF(ISNA(VLOOKUP(A2,Produk!A:C,3,FALSE)),0,
   VLOOKUP(A2,Produk!A:C,3,FALSE)*IFERROR(D2,1))

Sekilas rumus ini kelihatan aman. Tapi waktu aku Evaluate, di langkah ketiga hasilnya jadi 0*1 — padahal harusnya nggak nol.

Ternyata A2 punya spasi di belakang teksnya. VLOOKUP nggak nemu, ISNA ngasih TRUE, hasilnya 0.

Tanpa Evaluate Formula, aku bakal ngira masalahnya di kolom D. Padahal di kolom A.

Dua tombol tambahan: Step In buat masuk ke rumus sel referensinya, Step Out buat balik. Detail resminya ada di dokumentasi Microsoft soal Evaluate Formula.

Cara Pakai Watch Window: Pantau Sel di Sheet Lain

Watch Window nampilin nilai sel pilihan kamu di panel kecil yang tetap kelihatan, walau kamu lagi buka sheet lain.

1. Tab Formulas → Watch Window.
2. Klik Add Watch.
3. Pilih sel yang mau dipantau (biasanya sel total atau grand total).
4. Klik Add.

Sekarang kamu bisa ngutak-atik input di Sheet1 sambil lihat total di Sheet5 berubah real-time. Nggak perlu bolak-balik tab.

Panelnya nampilin nama file, sheet, alamat sel, nilai, dan rumusnya sekaligus.

Cara Nemuin Circular Reference

Circular reference terjadi kalau rumus di A1 ngacu balik ke A1 — langsung, atau lewat rantai sel lain.

Excel bakal nulis "Circular References: A1" di status bar bawah. Tapi dia cuma nampilin satu.

Buat lihat semuanya: tab Formulas → klik panah kecil di sebelah Error Checking → Circular References. Daftarnya muncul, klik buat loncat ke selnya.

Bonus: Show Formulas (Ctrl + ~)

Tekan Ctrl + ~ (tilde, di kiri tombol angka 1). Semua sel bakal nampilin rumusnya, bukan hasilnya.

Ini cara tercepat buat nemuin sel yang diketik manual padahal harusnya rumus. Angka yang hardcode bakal langsung kelihatan beda di antara barisan rumus.

Tekan Ctrl + ~ lagi buat balik normal.

Contoh Kasus: Audit Laporan Toko Berkah

Dataset toko_berkah punya Ngulik Data isinya 8.400 transaksi warung kelontong di Semarang. Pemiliknya bikin rekap bulanan di Excel, dan omzet Februari kelihatan aneh: cuma Rp 41,2 juta, padahal Januari Rp 68,7 juta.

Turun 40% dalam sebulan tanpa alasan jelas. Ini yang aku lakuin.

Langkah 1: Tekan Ctrl + ~. Langsung kelihatan — sel total Februari isinya =SUM(C2:C1847), sementara Januari =SUM(C2:C2903).

Langkah 2: Trace Precedents di sel total Februari. Panahnya berhenti di baris 1847, padahal data Februari mentok di baris 2540.

Temuannya: ada 693 baris transaksi yang nggak ikut kehitung. Rumus SUM-nya nggak ke-update waktu data baru ditempel di bawah.

Setelah dibenerin, omzet Februari yang bener: Rp 63,4 juta. Turun 7,7% dari Januari, bukan 40%.

Beda kesimpulannya jauh. Yang satu bikin panik, yang satu cuma variasi musiman biasa.

Butuh 4 menit buat nemuin ini pakai audit formula. Kalau nebak-nebak, bisa setengah hari.

Kesalahan Umum Waktu Audit Formula

Langsung nulis ulang rumusnya. Godaan pertama waktu lihat error. Tapi kalau kamu nggak tau salahnya di mana, rumus baru kamu bakal punya bug yang sama.

Cuma cek sel yang error. Error sering muncul jauh dari akar masalahnya. Trace Precedents dulu, ikutin panahnya sampai ke sumber.

Lupa Remove Arrows. Panah biru numpuk bikin sheet nggak kebaca. Bersihin tiap selesai satu sel.

Nggak pakai range dinamis. Kasus toko_berkah di atas nggak bakal kejadian kalau datanya diubah jadi Excel Table (Ctrl + T). Range-nya ikut nambah otomatis waktu ada baris baru.

Skip Evaluate Formula karena kelihatan ribet. Buat rumus 3 lapis ke atas, ini satu-satunya cara yang akurat. Sepuluh klik, tapi jawabannya pasti.

FAQ

Apa itu Trace Precedents dan Trace Dependents?

Trace Precedents nunjukin sel mana aja yang dipakai sebuah rumus sebagai bahan hitungnya. Trace Dependents nunjukin kebalikannya: sel mana aja yang hasilnya bergantung ke sel yang lagi kamu pilih. Dua-duanya nampilin panah biru di worksheet. Pakai Trace Precedents kalau kamu mau tau dari mana angka ini datang, dan Trace Dependents sebelum kamu ngehapus atau ngubah sebuah sel.

Gimana cara pakai Evaluate Formula di Excel?

Pilih sel yang mau dicek, buka tab Formulas, klik Evaluate Formula. Bagian rumus yang digarisbawahi bakal dihitung duluan waktu kamu klik tombol Evaluate. Klik terus sampai rumusnya selesai. Ini cara paling akurat buat nemuin di langkah mana rumus mulai ngasih hasil salah, terutama buat rumus bertingkat yang panjang.

Kenapa panah Trace Precedents muncul ikon tabel kecil?

Ikon tabel kecil dengan panah putus-putus artinya rumus kamu ngambil data dari sheet lain atau dari file Excel lain. Klik dua kali panah putus-putusnya, nanti muncul dialog Go To yang nunjukin alamat lengkap selnya. Kalau file sumbernya lagi ketutup, kamu harus buka dulu file itu supaya panahnya bisa ditelusuri.

Apa fungsi Watch Window di Excel?

Watch Window nampilin nilai sel tertentu di panel kecil yang tetap kelihatan walaupun kamu lagi scroll ke sheet lain. Berguna banget waktu kamu lagi ngutak-atik sel input di Sheet1 tapi mau langsung lihat efeknya ke total di Sheet5. Buka lewat tab Formulas, klik Watch Window, lalu Add Watch dan pilih selnya.

Gimana cara nemuin circular reference di Excel?

Excel bakal ngasih peringatan di status bar bawah dengan tulisan Circular References plus alamat selnya. Buat nemuin semuanya, buka tab Formulas, klik panah kecil di sebelah Error Checking, lalu pilih Circular References. Daftarnya bakal muncul dan kamu bisa langsung loncat ke sel bermasalah. Circular reference terjadi kalau rumus di A1 ngacu ke dirinya sendiri, langsung atau lewat rantai sel lain.

Penutup

Urutan yang aku pakai tiap kali ketemu rumus bermasalah: Ctrl + ~ buat lihat semua rumus, Trace Precedents buat ikutin sumbernya, Evaluate Formula buat nemuin langkah yang bocor.

Tiga langkah itu nyelesain 90% kasus. Sisanya biasanya circular reference atau data sumber yang kotor.

Kebiasaan yang paling nghemat waktu: Trace Dependents sebelum ngehapus apa pun.

Mau rumusnya lebih tahan error dari awal? Pelajari fungsi IFERROR dan XLOOKUP yang lebih aman ketimbang VLOOKUP. Kalau file kamu udah kegedean buat Excel, cek juga perbandingan Excel vs Google Sheets.