Cara Mengatasi Circular Reference di Excel

Circular reference di Excel muncul waktu sebuah rumus ngarah balik ke selnya sendiri, entah langsung atau lewat rantai sel lain. Hasilnya, Excel bingung dan biasanya ngasih peringatan plus angka nol.

Error ini sering nongol pas kamu bikin total, komisi, atau saldo berjalan yang nggak sengaja masukin selnya sendiri ke dalam hitungan.

Di bawah aku tunjukin cara nemuin sel penyebabnya, cara benerin, sama kapan circular reference justru sengaja dipakai.

Apa itu circular reference di Excel?

Circular reference adalah kondisi waktu rumus di satu sel bergantung pada hasil selnya sendiri. Contoh paling gampang, sel B10 diisi =SUM(B2:B10), padahal B10 itu bagian dari rentang yang dijumlah. Excel nggak bisa hitung nilai final karena hasilnya terus berubah tiap kali dihitung ulang.

Excel bakal munculin kotak peringatan Circular Reference Warning. Di kiri bawah layar (status bar), dia juga nampilin alamat sel yang bermasalah, misalnya Circular References: B10.

Kenapa circular reference muncul?

Penyebab paling umum adalah rentang rumus yang kelebihan satu baris atau satu kolom, jadi kena selnya sendiri. Penyebab lain, dua sel saling nunjuk: A1 ngambil dari A2, dan A2 balik ngambil dari A1. Rantai ini bisa panjang dan lewat banyak sel sebelum akhirnya balik ke titik awal.

• Rentang kelewat. =SUM(B2:B10) ditulis di B10, bukan di B11.
• Saling nunjuk. Total ngambil dari komisi, komisi ngambil dari total.
• Copy rumus. Rumus di-drag ke bawah sampai kena sel hasilnya sendiri.

Cara nemuin sel penyebab circular reference

Cara tercepat nemuin sel penyebabnya lewat menu Error Checking di tab Formulas. Excel bakal langsung nunjuk alamat sel yang bikin lingkaran, jadi kamu nggak perlu nebak satu-satu.

1. Klik tab Formulas di ribbon.
2. Di grup Formula Auditing, klik panah kecil di sebelah Error Checking.
3. Arahkan ke Circular References. Daftar alamat sel yang bermasalah bakal muncul.
4. Klik salah satu alamat itu, Excel langsung loncat ke selnya.

Kalau kamu cuma mau tau ada berapa banyak, lihat status bar di kiri bawah. Tulisan Circular References plus alamat sel selalu muncul di situ selama masalahnya belum beres.

Cara mengatasi circular reference

Setelah tau sel penyebabnya, perbaiki rentang atau referensinya biar rumus nggak ngitung dirinya sendiri. Untuk kasus total, cukup keluarkan sel hasil dari rentang. Untuk kasus saling nunjuk, putuskan salah satu rantainya.

1. Loncat ke sel penyebab lewat langkah di atas.
2. Cek rentang di dalam rumus. Kalau sel hasil ikut kejumlah, persempit rentangnya.
3. Ganti =SUM(B2:B10) di sel B10 jadi =SUM(B2:B9).
4. Kalau ada dua sel saling nunjuk, pindahkan salah satu perhitungan ke sel bantu yang terpisah.

Buat nyembunyiin sisa error lain yang muncul selama kamu benerin, IFERROR bisa bantu. Tapi ingat, IFERROR cuma nutupin tampilan error, bukan benerin circular reference-nya. Sumber masalahnya tetap harus dibetulin.

Kapan circular reference sengaja dipakai?

Kadang perhitungan berulang memang disengaja, misalnya model bunga majemuk atau alokasi biaya yang saling terkait. Untuk kasus ini, Excel punya fitur Iterative Calculation yang ngitung rumus berulang sampai hasilnya stabil. Fitur ini mati secara default, jadi harus kamu nyalain sendiri.

1. Klik File, lalu Options.
2. Masuk ke tab Formulas.
3. Centang Enable iterative calculation.
4. Atur Maximum Iterations (misal 100) dan Maximum Change (misal 0,001).

Nyalain fitur ini bikin Excel berhenti nampilin peringatan. Jadi pastiin circular reference-nya memang kamu inginkan, bukan kesalahan rumus yang kelewat.

Contoh kasus: rekap komisi toko_berkah

Di file rekap penjualan toko_berkah, ada kolom total omzet dan kolom komisi karyawan sebesar 5% dari total. Masalahnya, sel total ditulis =SUM(C2:C13) di baris ke-13, padahal komisi ada di C13. Begitu komisi masuk, total jadi ngitung dirinya sendiri.

Salah:
C13 (Total) = SUM(C2:C13)
C14 (Komisi) = C13 * 0,05

Benar:
C13 (Total penjualan) = SUM(C2:C12)
C14 (Komisi) = C13 * 0,05

Waktu aku rapiin file kayak gini, dari 12 baris data cuma 1 sel yang bikin seluruh sheet error. Begitu rentang total dipersempit, angka komisi Rp1.240.000 langsung keluar benar tanpa peringatan lagi.

Kesalahan umum

• Langsung nyalain Iterative Calculation. Ini nyembunyiin peringatan, bukan benerin rumus yang salah rentang.
• Nutup semua pakai IFERROR. Angka tetap salah, cuma error-nya nggak keliatan.
• Lupa cek status bar. Alamat sel penyebab selalu ada di kiri bawah, tinggal dibaca.

FAQ

Kenapa hasil rumus jadi nol setelah muncul circular reference?

Excel nggak bisa nyelesaiin perhitungan yang muter ke dirinya sendiri, jadi dia berhenti dan naruh nol sebagai hasil sementara. Nolnya bukan jawaban benar, cuma penanda kalau rumusnya belum bisa dihitung. Begitu kamu benerin rentang atau referensi yang bikin lingkaran, angka aslinya langsung muncul lagi tanpa perlu ngetik ulang rumusnya.

Gimana cara nemuin circular reference yang lewat banyak sel?

Pakai menu Circular References di tab Formulas. Excel bakal nunjuk salah satu sel di dalam rantai itu. Dari situ kamu bisa telusuri pakai tombol Trace Precedents dan Trace Dependents buat lihat sel mana nunjuk ke mana. Panah biru yang muncul bantu kamu ngelacak rantainya sampai ketemu titik yang bikin lingkaran balik ke awal.

Apakah circular reference selalu error?

Nggak selalu. Untuk sebagian model keuangan, perhitungan berulang memang diinginkan, misalnya bunga yang ngaruh ke saldo dan saldo yang ngaruh balik ke bunga. Buat kasus itu kamu nyalain Iterative Calculation biar Excel ngitung berulang sampai hasilnya stabil. Selama itu disengaja dan diatur batasnya, circular reference aman dipakai.

Kenapa peringatan circular reference muncul terus padahal sudah dibenerin?

Biasanya masih ada sel lain yang kena masalah sama. Excel cuma nampilin satu alamat di peringatan, padahal bisa jadi ada beberapa. Buka lagi menu Circular References buat lihat daftar lengkapnya, lalu benerin satu per satu. Kalau peringatan tetap muncul, cek sheet lain di workbook yang sama, karena referensi bisa nyambung antar tab.

Rapikan rumus, hilangkan error

Intinya: circular reference muncul karena rumus ngitung dirinya sendiri, dan cara benerinnya selalu mulai dari nemuin sel penyebab lewat menu Circular References. Persempit rentang atau putuskan rantai yang saling nunjuk, dan aktifkan Iterative Calculation cuma kalau perhitungan berulang memang kamu mau.

Mau makin lancar debug rumus Excel? Lanjut baca cara atasi error #N/A pada VLOOKUP dan cara mengatasi error #SPILL. Kamu juga bisa latihan rapiin data beneran di NgulikSheet.

Referensi resmi soal ini ada di dokumentasi Microsoft Excel.