Cara Menemukan Circular Reference di Excel

Biasanya terjadi di saat yang paling tidak tepat: spreadsheet yang kemarin baik-baik saja tiba-tiba menampilkan peringatan circular reference tepat sebelum tenggat.

Kabar baiknya? Circular reference ini lebih mudah diperbaiki daripada yang Anda kira.

Dalam panduan ini, saya akan menjelaskan:

• Cara menemukan circular reference di Excel
• Memahami berbagai cara kemunculannya
• Memperbaikinya dengan metode yang terbukti

Apa Itu Circular Reference di Excel?

Circular reference di Excel terjadi saat sebuah rumus merujuk kembali ke selnya sendiri (secara langsung atau tidak langsung) dan menciptakan loop perhitungan yang tidak dapat diselesaikan Excel. Misalnya, jika sel A1 berisi =A1+10, Excel akan terus mencoba menghitung hasilnya tanpa henti.

Bahkan loop tidak langsung pun bermasalah. Jika A1 bergantung pada B1, B1 bergantung pada C1, dan C1 bergantung pada A1, Excel akan macet. File dapat memerlukan waktu lama untuk menghitung, menampilkan nilai yang salah, atau berhenti memperbarui sama sekali.

Sebagian circular reference merupakan kesalahan rumus sederhana, sementara sebagian lainnya adalah pilihan desain yang disengaja dalam model lanjutan. Saya akan membahas perbedaannya nanti, tetapi pertama-tama mari telusuri jenis-jenisnya.

Circular reference langsung

Circular reference langsung terjadi saat sebuah rumus menyertakan alamat selnya sendiri. Misalkan Anda memiliki dua nilai di sel A2 dan B2. Jika di sel C2 kita mengetik A2 + B2 + C2, maka C2 merujuk dirinya sendiri secara langsung. 

Saat Anda memasukkan rumus dan menekan Enter, muncullah pop-up peringatan circular reference. Dan ketika Anda mengeklik OK, rumus mengembalikan hasil yang tidak terduga.

Circular reference langsung. Gambar oleh Penulis.

Ini mengembalikan 0 karena saat kita menjumlahkan A2 dan B2, seharusnya hasilnya 6, tetapi 'C2' terus menambahkan dirinya sendiri. Excel tidak dapat menghitung C2 tanpa mengetahui nilai C2. Ini menciptakan loop seketika yang tidak pernah terselesaikan.

Catatan: Circular reference seperti ini paling mudah diidentifikasi karena referensi ke diri sendiri terlihat langsung dalam sintaks rumus. Bahkan, Excel mendeteksi dan menandainya segera setelah rumus dimasukkan.

Circular reference tidak langsung

Circular reference tidak langsung terjadi ketika beberapa sel membentuk rantai ketergantungan yang berputar kembali ke titik awal.

Contohnya:

• F2 merujuk H4

• H4 merujuk E4

• E4 merujuk F2

Circular reference tidak langsung. Gambar oleh Penulis.

Tidak ada satu pun rumus yang terlihat salah jika dilihat sendiri-sendiri. Masalahnya baru muncul saat Excel menelusuri seluruh rantai dan menyadari perhitungan kembali ke titik awal.

Jenis ini lebih sulit dideteksi karena loop dapat mencakup banyak sel dan, dalam workbook yang kompleks, bahkan beberapa worksheet, sebelum Excel mengidentifikasi ketergantungan melingkarnya.

Circular reference tersembunyi

Circular reference tersembunyi adalah yang loop ketergantungannya tidak terlihat jelas dari sintaks rumus. Ini sering berasal dari:

• Named range

• INDIRECT() atau fungsi OFFSET()

• Logika bersyarat yang kompleks

Misalnya, sebuah named range QuarterlyTotal mencakup sel C3 hingga C5 yang berisi nilai penjualan triwulanan.

Di sel C5, bilah rumus menampilkan:

=QuarterlyTotal*0.1Didukung oleh

Sekilas, ini terlihat baik-baik saja. Rumus tidak merujuk C5 secara langsung. Namun, karena QuarterlyTotal menyertakan C5, Excel harus menghitung C5 sebagai bagian dari total yang digunakan untuk menghitung C5.

Ini menciptakan loop tersembunyi:

• Excel mencoba menghitung QuarterlyTotal

• QuarterlyTotal menyertakan C5

• C5 bergantung pada QuarterlyTotal

Referensinya bersifat melingkar, meskipun sintaks rumus tidak menampilkannya secara eksplisit.

Inilah yang membuat circular reference tersembunyi sangat sulit ditangani. Rumus terlihat benar, workbook mungkin lama berjalan normal, dan circular reference baru muncul ketika Excel mengevaluasi named range.

Circular reference tersembunyi. Gambar oleh Penulis.

Logika bersyarat dapat membuatnya makin sulit dideteksi. Mengapa? Karena IF() bersarang mungkin hanya menimbulkan circular reference dalam kondisi tertentu. Akibatnya, file berfungsi hingga nilai tertentu berubah.

Circular reference tersembunyi adalah yang paling menantang untuk diperbaiki karena loop ketergantungan disamarkan oleh abstraksi, bukan oleh referensi sel yang terlihat.

Circular reference yang disengaja vs. tidak sengaja

Sebagian circular reference ada karena desain. Sebagian lainnya adalah kesalahan yang perlu Anda hilangkan.

Circular reference yang disengaja

Circular reference yang disengaja dibuat secara sadar untuk mendukung kebutuhan pemodelan tertentu ketika nilai saling bergantung.

Ambil contoh pemodelan keuangan: beban bunga bergantung pada saldo utang yang masih berjalan, sementara saldo utang berubah berdasarkan arus kas, yang mencakup beban bunga. Ini menciptakan loop umpan balik yang mencerminkan logika bisnis di dunia nyata.

Model dimulai dengan struktur sederhana:

• Debt Balance merepresentasikan jumlah awal
• Interest Expense dihitung sebagai persentase dari utang
• Updated Debt mencakup saldo awal dan bunganya

Circular reference yang disengaja. Gambar oleh Penulis.

Ketika bunga dihitung berdasarkan utang, dan utang yang diperbarui menyertakan bunga tersebut, perhitungannya secara alami berputar kembali pada dirinya sendiri. Excel menandainya sebagai circular reference dan secara default mengembalikan nol karena tidak dapat menyelesaikan ketergantungan dalam satu kali proses.

Untuk mencegah hal ini, aktifkan iterative calculation seperti ini:

1. Buka Files > Options > Formulas
2. Di bagian Calculation Option centang kotak Enable iterative calculation

Aktifkan opsi iterative calculation. Gambar oleh Penulis.

Setelah iterative calculation diaktifkan, Excel akan berulang kali menghitung nilai bunga dan utang yang diperbarui. Pada setiap putaran, angkanya menyesuaikan sedikit demi sedikit hingga stabil dan berhenti berubah. 

Hasil akhir merepresentasikan solusi yang konvergen, bukan perhitungan satu langkah. Ini berhasil karena circular reference-nya disengaja. Bunga memengaruhi utang, dan utang memengaruhi bunga. Masing-masing nilai saling memengaruhi, jadi perhitungan berulang memang diharapkan dan diperlukan.

Iterative calculation. Gambar oleh Penulis.

Catatan: Iterative calculation tidak serta-merta menjamin hasil yang bermakna. Fitur ini hanya mengulang logika yang Anda bangun di dalam model, itulah mengapa circular reference yang disengaja memerlukan pemantauan dan validasi yang cermat.

Circular reference tidak sengaja

Circular reference tidak sengaja terjadi akibat kesalahan rumus, kekeliruan salin-tempel, atau perubahan yang tidak diinginkan. Misalkan Anda memasukkan nilai 2, 4, dan 6 di sel A1:A3.

Di A4, Anda ingin total, jadi Anda memasukkan:

=SUM(A1:A4)Didukung oleh

Sel A4 kini menyertakan dirinya sendiri dalam perhitungan.

Saat Anda menekan Enter, Excel menampilkan peringatan circular reference. Setelah mengeklik OK, Excel mengembalikan 0.

Circular reference tidak sengaja. Gambar oleh Penulis.

Circular reference ini tidak merepresentasikan logika nyata. Ini terjadi karena rentang rumusnya salah.

Cara Memperbaiki Circular Reference di Excel

Begitu Excel menandai adanya loop, Anda harus menemukan tepat di mana loop itu dimulai dan menentukan cara menyelesaikannya. 

Mari kita lihat beberapa metode untuk memperbaiki circular reference, tergantung apakah itu tidak sengaja atau disengaja: 

Deteksi cepat untuk menemukan masalah

Excel menyertakan beberapa alat deteksi bawaan untuk dengan cepat menemukan circular reference, masing-masing dengan kelebihan dan keterbatasannya. Mari telusuri opsi-opsi ini. 

Notifikasi bilah status

Cara tercepat mendeteksi circular reference adalah melalui bilah status di pojok kiri bawah jendela Excel.

Saat circular reference ada, Excel menampilkan teks seperti “Circular References: G4.” Ini memberi tahu Anda sel yang paling baru terdeteksi terlibat dalam loop.

Menampilkan circular reference di bilah status. Gambar oleh Penulis.

Namun, bilah status hanya menampilkan satu circular reference pada satu waktu. Jika ada beberapa loop, Excel akan menampilkannya satu per satu.

Begini cara mengatasinya: 

1. Perbaiki sel yang ditampilkan di bilah status
2. Periksa bilah status lagi
3. Ulangi hingga pesannya hilang

Tips: Catat setiap alamat sel, karena memperbaiki satu loop sering kali menyingkap loop lain.

Pesan peringatan awal

Excel juga menampilkan pesan peringatan ketika:

• Anda membuat circular reference, atau
• Anda membuka workbook yang sudah mengandungnya

Peringatan biasanya menyatakan bahwa satu atau lebih rumus merujuk dirinya sendiri dan mungkin menghitung secara tidak benar.

Peringatan ini dapat berhenti muncul jika:

• Anda menutup pesan sebelumnya dengan mengeklik OK
• Iterative calculation diaktifkan
• Mode perhitungan disetel ke Manual (Formulas > Calculation Options > Manual)

Jika hasil terlihat tidak benar dan tidak ada peringatan yang muncul, paksa perhitungan ulang seperti ini:

• Tekan F9 untuk menghitung ulang worksheet aktif
• Tekan Ctrl + Alt + F9 untuk memaksa perhitungan ulang di semua worksheet

Tip: Menutup dan membuka kembali workbook juga dapat membuat Excel menampilkan peringatan lagi.

Alat pengecekan kesalahan

Untuk melihat beberapa circular reference sekaligus, gunakan alat Error Checking di Excel: Buka Formulas > Error Checking > Circular References

Menu ini menampilkan semua circular reference yang terdeteksi dan memungkinkan Anda lompat langsung ke tiap sel, sehingga lebih mudah mendiagnosis workbook yang kompleks.

Jika submenu Circular References tampak kosong tetapi Excel masih berperilaku tidak semestinya, tekan F9 untuk memaksa perhitungan ulang dan menyegarkan daftar.

Alat pengecekan kesalahan untuk menemukan circular reference. Gambar oleh Penulis.

Catatan: Di Excel 2007 dan yang lebih baru, fitur ini tersedia di Ribbon. Versi lebih lama menampilkan opsi serupa melalui menu bar.

Alat penelusuran visual

Setelah Anda mengidentifikasi sel bermasalah, gunakan alat penelusuran visual untuk melihat bagaimana circular reference terbentuk.

Alat ini menggambar panah antar sel dan membuat ketergantungan menjadi terlihat alih-alih tersembunyi di dalam rumus. Keduanya tersedia di grup Formula Auditing pada tab Formulas:

• Trace Precedents menampilkan sel mana yang menjadi input bagi rumus terpilih. Excel menggambar panah dari sel input ke sel rumus. Pintasannya Alt + M, P
• Trace Dependents menampilkan sel mana yang menggunakan nilai sel terpilih. Excel menggambar panah dari sel terpilih ke rumus mana pun yang merujuknya. Pintasannya Alt + M, D

Jika penelusuran precedents dan dependents pada akhirnya kembali ke sel awal, panah membentuk loop dan mengonfirmasi circular reference.

Trace Precedents dan Trace Dependents. Gambar oleh Penulis.

Setelah rumus diperbaiki, panah tidak lagi berputar kembali, dan circular reference pun hilang. Anda dapat menghapus panah menggunakan Remove Arrows di menu yang sama.

Restrukturisasi rumus dan perhitungan alternatif

Sebagian besar circular reference terjadi karena urutan perhitungan yang salah, bukan karena Excel mengalami gangguan. Untuk circular reference yang tidak sengaja, restrukturisasi rumus adalah solusi paling efektif. 

Berikut beberapa metode untuk memperbaikinya:  

Restrukturisasi rumus untuk menghapus ketergantungan diri

Circular reference sering muncul ketika sebuah rumus mencoba menghitung nilai menggunakan hasilnya sendiri.

Contohnya, rumus seperti =A2+1 di sel A2 menciptakan loop seketika karena sel bergantung pada dirinya sendiri. Untuk memperbaikinya, pindahkan input yang diperlukan ke sel terpisah dan rujuk nilai tersebut sebagai gantinya.

Setelah ketergantungan dihapus, Excel dapat menghitung rumus secara normal.

Gunakan kolom bantu untuk perhitungan perantara

Circular reference juga terjadi ketika satu rumus mencoba menangani beberapa langkah sekaligus.

Misalnya, jika dua sel saling bergantung untuk menghitung nilai bersama, Excel tidak memiliki titik awal yang jelas. Untuk mengatasinya, perkenalkan sel bantu yang melakukan perhitungan perantara terlebih dahulu.

Dengan menghitung nilai bersama di sel bantu terpisah, Excel dapat mengikuti urutan yang jelas:

1. Sel bantu dihitung terlebih dahulu
2. Rumus yang bergantung dihitung berikutnya

Pendekatan ini menyederhanakan rumus dan menghilangkan ketergantungan melingkar.

Pisahkan ringkasan dari perhitungan

Total dan rumus ringkasan sebaiknya tidak dicampur di sel yang sama dengan yang mereka rujuk. Ketika ringkasan menarik angka dari rentang yang menyertakan dirinya sendiri, Excel membuat circular reference. 

Misalnya, jika A2 berisi jumlah dasar 1000, dan B2 berisi tarif 10%, C2 menampilkan jumlah total, dan D2 menghitung biaya.

Jika C2 menggunakan =A2+D2 dan D2 menggunakan =C2*$B$2, circular reference akan muncul karena total bergantung pada biaya, dan biaya bergantung pada total. 

Excel tidak dapat menghitung salah satunya terlebih dahulu.

Untuk memperbaikinya, biaya harus dihitung dari nilai dasar, bukan dari total. Jadi ubah D2 menjadi =A2*$B$2 dan biarkan C2 sebagai =A2+D2.

Gunakan sel terpisah untuk menghindari circular reference. Gambar oleh Penulis.

Kini Excel dapat menghitung biaya terlebih dahulu lalu menambahkannya ke nilai dasar. Circular reference menghilang karena setiap rumus memiliki titik awal yang jelas.

Mengaktifkan iterative calculation untuk logika melingkar yang disengaja

Iterative calculation hanya boleh digunakan ketika circular reference merupakan elemen desain yang disengaja, bukan kesalahan yang tidak sengaja. Metode ini cocok untuk model keuangan dan skenario lain yang memiliki loop umpan balik yang sah, dan logika melingkarnya mencerminkan perilaku dunia nyata.

Untuk mengaktifkan iterative calculation di Excel untuk Windows:

1. Buka File > Options > Formulas
2. Di bawah opsi Calculation, centang Enable iterative calculation
3. Klik OK

Setelah diaktifkan, Excel akan berulang kali menghitung rumus yang terlibat dalam circular reference hingga stabil atau mencapai kondisi penghentian.

Pengaturan iterasi utama

Dua pengaturan mengontrol perilaku iterative calculation:

• Maximum Iterations menentukan berapa kali Excel menghitung ulang rumus sebelum berhenti. Nilai 100 iterasi umumnya cocok untuk sebagian besar model bisnis dan keuangan.
• Maximum Change mendefinisikan seberapa kecil perbedaan antara hasil perhitungan yang harus dicapai sebelum Excel berhenti menghitung ulang. Nilai 0,001 adalah titik awal umum yang menyeimbangkan akurasi dan kinerja.

Meningkatkan salah satu pengaturan dapat meningkatkan ketelitian tetapi mungkin memperlambat perhitungan ulang pada workbook besar.

Kapan menggunakan metode ini

Aktifkan iterative calculation hanya ketika:

• Circular reference merepresentasikan logika bisnis yang valid
• Model mencakup loop umpan balik yang sah (misalnya, bunga bergantung pada utang, sementara utang berubah berdasarkan bunga)
• Anda mengharapkan nilai-nilai berkonvergensi alih-alih berosilasi tanpa henti

Metode ini bukan solusi untuk circular reference tidak sengaja akibat kesalahan rumus.

Peringatan penting yang perlu dipertimbangkan

Sebelum mengaktifkan iterative calculation, ingat risiko berikut:

• Circular reference yang tidak sengaja mungkin tidak lagi memicu peringatan, sehingga kesalahan lebih sulit dideteksi.
• Model yang besar atau kompleks mungkin menghitung ulang lebih lambat.
• Jika nilai berosilasi alih-alih stabil, hasilnya mungkin tidak andal.

Karena risiko ini, iterative calculation harus selalu didokumentasikan secara eksplisit.

Implementasi circuit breaker untuk model kompleks

Circuit breaker adalah teknik lanjutan yang memberi Anda kendali kapan logika melingkar diaktifkan. Alih-alih menghapus circular reference, Anda sengaja menjeda atau mengizinkannya menggunakan nilai kontrol di dalam rumus.

Pendekatan ini paling cocok untuk pemodel keuangan dan pengguna tingkat lanjut yang membutuhkan logika melingkar dalam beberapa skenario tetapi tidak sepanjang waktu.

Circuit breaker biasanya menggunakan:

• Sel kontrol (sering disetel ke 0 atau 1)

• Pernyataan IF yang beralih antara logika melingkar dan alternatif non-melingkar

Pola umumnya seperti ini:

=IF($A$1=1, circular_logic, non_circular_alternative)Didukung oleh

• Saat sel kontrol bernilai 0, rumus menghindari circular reference
• Saat sel kontrol bernilai 1, logika melingkar diizinkan berjalan

Misalkan Anda memasukkan nilai berikut: 

• A1: Circuit breaker (0 atau 1)

• A2: Saldo awal = 1000

• B2: Bunga

• C2: Saldo diperbarui

Di B2, gunakan =IF($A$1=1, C2*10%, A2*10%) dan di C2 masukkan =A2+B2

Sekarang:

• Ketika A1 = 0, bunga dihitung dari saldo awal. Circular reference dinonaktifkan, dan Excel menghitung secara normal.

• Saat A1 = 1, bunga dihitung dari saldo yang diperbarui. Logika melingkar menjadi aktif dan menciptakan loop umpan balik yang disengaja.

Menggunakan circuit breaker. Gambar oleh Penulis.

Kapan menggunakan circuit breaker

Circuit breaker bermanfaat untuk:

• Menjaga logika melingkar tetap nonaktif saat membangun dan memvalidasi rumus
• Menyalakan dan mematikan logika melingkar untuk mengisolasi masalah dan memverifikasi hasil
• Menampilkan model dengan logika melingkar dinonaktifkan agar asumsi dan mekanisme lebih mudah dijelaskan

Menghapus rumus bermasalah dan memulai ulang

Ini adalah opsi terakhir: gunakan saat metode lain gagal. Cocok untuk workbook dengan ketergantungan yang sangat kusut, file warisan dengan logika tidak jelas, atau situasi ketika tekanan waktu menuntut solusi cepat namun andal.

Alih-alih mencoba mengurai rumus yang rusak, Anda mengatur ulang model dan membangunnya kembali secara metodis. 

Mari lihat caranya.

Langkah 1: Buat salinan cadangan

Sebelum melakukan perubahan, buat cadangan penuh dari workbook. Caranya:

• Buka workbook di Excel

• Buka File > Save As > Browse dan pilih lokasi berbeda atau tetap di folder yang sama untuk menyimpan file Anda

• Ganti nama file, misalnya, Workbook_Backup.xlsx

• Klik Save

Buat salinan cadangan workbook. Gambar oleh Penulis.

Ini mempertahankan rumus, logika, dan hasil asli sehingga Anda dapat merujuknya nanti atau memulihkan jika diperlukan.

Jangan pernah mencoba metode ini pada satu-satunya salinan file.

Langkah 2: Dokumentasikan struktur saat ini

Selanjutnya, tangkap cara kerja workbook saat ini:

• Ambil tangkapan layar bagian-bagian kunci
• Catat apa yang dihitung tiap worksheet
• Identifikasi input, output, dan ringkasan

Dokumentasi ini membantu Anda memahami apa yang perlu dibangun kembali dan mencegah logika penting hilang selama pembersihan.

Langkah 3: Ganti rumus dengan nilai

Untuk memutus semua ketergantungan melingkar sekaligus, salin rentang yang terdampak dan gunakan Paste Special. Klik kanan > Paste Special > Values untuk mengganti rumus dengan hasil perhitungannya.

Gunakan opsi Paste Special. Gambar oleh Penulis.

Ini langsung menghapus setiap circular reference sambil mempertahankan angka yang terlihat sebagai baseline sementara.

Langkah 4: Bangun ulang rumus secara bertahap

Bangun ulang workbook bagian demi bagian alih-alih sekaligus:

1. Tambahkan rumus untuk satu blok logis
2. Tekan F9 untuk memaksa perhitungan ulang
3. Pastikan tidak muncul circular reference
4. Lanjut ke bagian berikutnya

Langkah 5: Verifikasi hasil dengan cadangan

Saat setiap bagian dibangun kembali, bandingkan hasilnya dengan file cadangan. Anda mungkin melihat perbedaan yang menunjukkan:

• Input yang hilang
• Logika rumus yang salah
• Kesalahan perhitungan yang sebelumnya tersembunyi

Deteksi lanjutan untuk referensi yang sulit ditangkap

Beberapa circular reference tidak muncul dengan alat standar Excel. Dalam kasus seperti itu, coba teknik lanjutan: 

Makro VBA untuk pemindaian menyeluruh

Ketika penelusuran manual terlalu lambat, VBA dapat memindai ribuan sel dalam hitungan detik dan mengidentifikasi circular reference di seluruh workbook.

Pendekatan dasarnya seperti ini:

• Tekan Alt + F11 untuk membuka Visual Basic Editor.
• Buka Insert > Module.
• Tempelkan makro pendeteksi circular reference.
• Tekan F5 untuk menjalankannya.

Antarmuka VBA. Gambar oleh Penulis.

Tip: Anda tidak perlu menulis VBA dari nol. Jelaskan kebutuhan Anda dengan bahasa sederhana dan biarkan ChatGPT membuat makro deteksi khusus.

Berikut contoh skrip VBA: 

Option Explicit
Public Sub HighlightCircularReferences_ActiveSheet()
    Dim ws As Worksheet, c As Range
    Dim hasAny As Boolean

    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    ' Optional: clear previous highlighting (only on used range)
    On Error Resume Next
    ws.UsedRange.Interior.Pattern = xlNone
    On Error GoTo 0

    ' Scan formula cells only
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If IsCircularFormula(c) Then
            c.Interior.Color = RGB(255, 230, 153) ' light yellow
            hasAny = True
        End If
    Next c

    Application.ScreenUpdating = True

    If hasAny Then
        MsgBox "Circular reference cells highlighted on: " & ws.Name, vbInformation
    Else
        MsgBox "No circular references found on: " & ws.Name, vbInformation
    End If
End Sub

Private Function IsCircularFormula(ByVal startCell As Range) As Boolean
    Dim inPath As Object
    Set inPath = CreateObject("Scripting.Dictionary")

    IsCircularFormula = TraceForCycle(startCell, startCell, inPath, 0)
End Function

Private Function TraceForCycle(ByVal startCell As Range, ByVal curCell As Range, _
                               ByVal inPath As Object, ByVal depth As Long) As Boolean
    Dim prec As Range, a As Range, x As Range
    Dim key As String

    ' Safety limit to avoid deep/huge graphs freezing Excel
    If depth > 200 Then Exit Function

    key = curCell.Parent.Name & "!" & curCell.Address(False, False)

    If inPath.Exists(key) Then
        ' We hit a loop in the current path
        TraceForCycle = True
        Exit Function
    End If

    inPath.Add key, True

    ' Direct precedents (cells referenced by curCell)
    On Error Resume Next
    Set prec = curCell.DirectPrecedents
    On Error GoTo 0

    If Not prec Is Nothing Then
        For Each a In prec.Areas
            For Each x In a.Cells
                ' If any precedent is the start cell -> circular
                If x.Address(External:=True) = startCell.Address(External:=True) Then
                    TraceForCycle = True
                    Exit Function
                End If

                ' Recurse only within worksheets (avoid names/constants)
                If x.Parent Is curCell.Parent Or True Then
                    If TraceForCycle(startCell, x, inPath, depth + 1) Then
                        TraceForCycle = True
                        Exit Function
                    End If
                End If
            Next x
        Next a
    End If

    inPath.Remove key
End FunctionDidukung oleh

Gunakan makro VBA untuk mengidentifikasi sel dengan circular reference. Gambar oleh Penulis.

Makro ini memeriksa sheet aktif untuk circular reference dalam rumus. Jika sebuah rumus merujuk kembali ke dirinya sendiri, secara langsung atau melalui sel lain, makro menyorot sel-sel tersebut agar Anda mudah melihat letak masalahnya. Ini tetap berfungsi meskipun Iterative calculation AKTIF.

VBA menyorot sel circular reference. Gambar oleh Penulis.

Investigasi lintas worksheet

Circular reference yang melintasi beberapa worksheet lebih sulit dideteksi karena panah penelusur tidak melintas tab.

Bentuknya bisa seperti ini:

• Sheet A merujuk Sheet B
• Sheet B merujuk Sheet C
• Sheet C merujuk Sheet A

Salah satu cara mendeteksinya adalah memetakan ketergantungan antar sheet. Caranya, catat sheet mana yang merujuk sheet lain dan cari loop pada daftar tersebut.

Anda juga dapat menggunakan Find & Replace untuk menemukan referensi lintas sheet:

• Tekan Ctrl + H

• Masukkan nama sheet diikuti ! (misalnya, Sheet1!)

• Klik Find All

Excel akan menampilkan setiap rumus yang merujuk sheet tersebut, sehingga Anda dapat menelusuri ketergantungan lintas tab.

Temukan sheet yang saling terhubung dalam sebuah workbook. Gambar oleh Penulis.

Catatan: Find & Replace tidak mendeteksi circular reference secara otomatis. Fitur ini hanya menemukan ketergantungan lintas sheet, yang kemudian Anda analisis untuk mencari loop.

Pemecahan masalah named range

Named range dapat menyembunyikan circular reference karena rumus tidak lagi menampilkan alamat sel langsung. Untuk meninjaunya:

• Buka “Name Manager” dengan Ctrl + F3
• Periksa setiap definisi named range
• Cari rentang yang menyertakan sel yang merujuk nama rentang yang sama dalam rumus

Periksa named range. Gambar oleh Penulis.

Masalah logika bersyarat

Sebagian circular reference hanya muncul dalam kondisi tertentu.

Contohnya:

=IF(A1>100, A2+10, 100)Didukung oleh

Saat A1 di bawah 100, Excel menggunakan kondisi kedua dan circular reference tidak ada. Ketika A1 melebihi 100, Excel mengevaluasi kondisi pertama dan mencoba menghitung A2 menggunakan A2 itu sendiri, memicu circular reference.

Untuk mendeteksi masalah ini:

• Uji berbagai nilai input untuk memaksa semua jalur kondisi
• Perhatikan peringatan circular reference yang muncul hanya dalam kondisi tertentu

Untuk memperbaikinya, pindahkan logika melingkar ke sel bantu terpisah dan sederhanakan IF bersarang menjadi perhitungan yang lebih jelas, langkah demi langkah.

Cara Mencegah Circular Reference di Excel

Beberapa kebiasaan sejak awal dapat menghemat berjam-jam debugging saat workbook berkembang atau dibagikan dengan orang lain. 

Jadi berikut praktik terbaik yang perlu Anda ikuti: 

Perencanaan workbook dan perancangan rumus

Struktur yang baik mencegah sebagian besar circular reference sejak awal. Pastikan melakukan hal berikut:

• Pisahkan input, perhitungan, dan output agar rumus bergerak dalam satu arah yang jelas.

• Gunakan sel bantu atau kolom bantu alih-alih melakukan semuanya dalam satu rumus.

• Perhitungan harus bergantung pada nilai yang lebih awal, bukan pada hasilnya sendiri atau total hilir.

• Jauhkan sel input dari total dan ringkasan untuk menghindari rentang yang merujuk diri sendiri.

Operasi salin-tempel dan manajemen referensi

Banyak circular reference diperkenalkan saat salin-tempel. Jadi, berikut yang perlu Anda pertimbangkan: 

• Tempel rumus ke satu sel terlebih dahulu dan periksa Formula Bar sebelum menyalinnya ke seluruh rentang.

• Gunakan referensi absolut (misalnya, $A$1) untuk tarif dan asumsi yang tidak boleh berpindah.

• Jangan tempatkan rumus ringkasan di dalam rentang yang mereka hitung.

• Nama yang jelas membuat rujukan rumus lebih mudah dipahami dan mengurangi kesalahan saat rentang berkembang.

Dokumentasi dan audit rutin

Dokumentasi ringan sangat membantu. Karena itu Anda harus mengingat hal-hal berikut saat bekerja:

• Tambahkan komentar singkat pada sel atau buat worksheet dokumentasi sederhana.

• Tinjau rumus menggunakan alat Formula Auditing sebelum presentasi penting atau serah terima.

• Simpan versi file kritis agar Anda dapat melacak kapan masalah diperkenalkan.

• Dalam file kolaboratif, periksa perubahan secara berkala agar kesalahan kecil tidak menyebar.

Penutup

Jika Anda sedang menghadapi circular reference, pastikan terlebih dahulu apakah itu tidak sengaja atau disengaja. Keputusan itu menentukan apakah Anda harus merestrukturisasi rumus, mengaktifkan iterasi, atau mendesain ulang sebagian model.

Jangan terburu-buru menggunakan pengaturan atau jalan pintas sebelum Anda yakin logikanya memang masuk akal.

Setelah masalah diperbaiki, luangkan waktu sejenak untuk membuat file lebih mudah digunakan ke depannya. Tambahkan catatan singkat pada rumus penting, simpan salinan bersih workbook, dan pastikan siapa pun yang menggunakannya memahami cara kerja perhitungannya.