TL;DR
Audit formula Excel adalah cara nelusuri sel mana yang dipakai dan dipengaruhi sebuah rumus, supaya kamu bisa nemuin sumber error. Empat alat utamanya ada di tab Formulas: Trace Precedents (sel yang dipakai rumus), Trace Dependents (sel yang bergantung ke rumus ini), Evaluate Formula (jalanin rumus langkah demi langkah), dan Watch Window (pantau sel di sheet lain). Semua gratis dan udah ada di Excel.
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.
Empat alat yang bisa langsung kamu pakai: Trace Precedents, Trace Dependents, Evaluate Formula, dan Watch Window. Plus cara nemuin circular reference yang bikin file nge-freeze.
Empat situasi yang paling sering.
#REF!, #VALUE!, atau #N/A di sel yang kemarin masih normal.Situasi keempat yang paling sering bikin kacau. Orang ngehapus kolom bantu, terus 40 rumus di sheet lain berubah jadi #REF! semua.
Trace Precedents nunjukin sel mana aja yang jadi bahan hitung sebuah rumus. Panah biru bakal muncul dari sel sumber ke sel rumus.
Alt + M + P.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.
Ini kebalikan Trace Precedents. Dia nunjukin sel mana aja yang hasilnya bakal berubah kalau kamu ngutak-atik sel yang lagi dipilih.
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!.
Ini alat paling berguna buat rumus bertingkat yang panjang. Evaluate Formula ngitung rumus kamu selangkah demi selangkah, persis kayak cara Excel ngitungnya.
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.
Watch Window nampilin nilai sel pilihan kamu di panel kecil yang tetap kelihatan, walau kamu lagi buka sheet lain.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.