Come trovare i riferimenti circolari in Excel

Di solito succede nel momento peggiore: un foglio di calcolo che ieri funzionava alla perfezione improvvisamente mostra un avviso di riferimento circolare proprio prima di una scadenza.

La buona notizia? Questi riferimenti circolari sono più facili da sistemare di quanto pensi.

In questa guida ti spiego:

• Come trovare i riferimenti circolari in Excel
• Capire le diverse modalità con cui si presentano
• Correggerli con metodi collaudati

Cosa sono i riferimenti circolari in Excel?

Un riferimento circolare in Excel si verifica quando una formula fa riferimento alla propria cella (direttamente o indirettamente) e crea un ciclo di calcolo che Excel non riesce a risolvere. Per esempio, se la cella A1 contiene =A1+10, Excel continua a provare a calcolare il risultato all'infinito.

Anche i cicli indiretti creano problemi. Se A1 dipende da B1, B1 dipende da C1 e C1 dipende da A1, Excel resta bloccato. Il file può impiegare molto tempo a calcolare, mostrare valori errati o smettere del tutto di aggiornarsi.

Alcuni riferimenti circolari sono semplici errori di formula, mentre altri sono scelte progettuali intenzionali usate in modelli avanzati. Tornerò su questa distinzione più avanti, ma prima vediamo i diversi tipi.

Riferimenti circolari diretti

Un riferimento circolare diretto si verifica quando una formula include l'indirizzo della propria cella. Supponiamo di avere due valori nelle celle A2 e B2. Se nella cella C2 scriviamo A2 + B2 + C2, C2 fa riferimento direttamente a se stessa. 

Quando inserisci la formula e premi Invio, compare un pop-up di riferimento circolare. E quando fai clic su OK, la formula restituisce un risultato inaspettato.

Riferimento circolare diretto. Immagine dell'autore.

Questo restituisce 0 perché quando sommiamo A2 e B2 dovrebbe restituire 6, ma "C2" continua ad aggiungere se stessa. Excel non può calcolare C2 senza conoscere il valore di C2. Si crea così un ciclo immediato che non si risolve mai.

Nota: Questi riferimenti circolari sono i più facili da individuare perché l'auto-riferimento è visibile direttamente nella sintassi della formula. Infatti, Excel li rileva e li segnala non appena la formula viene inserita.

Riferimenti circolari indiretti

Un riferimento circolare indiretto si verifica quando più celle formano una catena di dipendenze che torna al punto di partenza.

Per esempio:

• F2 fa riferimento a H4

• H4 fa riferimento a E4

• E4 fa riferimento a F2

Riferimento circolare indiretto. Immagine dell'autore.

Nessuna formula, presa singolarmente, sembra sbagliata. Il problema emerge solo quando Excel traccia l'intera catena e si accorge che il calcolo torna al punto di partenza.

Questi sono più difficili da rilevare perché il ciclo può estendersi su molte celle e, nei file complessi, anche su più fogli di lavoro, prima che Excel identifichi la dipendenza circolare.

Riferimenti circolari nascosti

Un riferimento circolare nascosto è quello in cui il ciclo di dipendenza non è evidente dalla sintassi della formula. Spesso derivano da:

• Intervalli denominati

• Funzioni INDIRECT() o OFFSET()

• Logiche condizionali complesse

Per esempio, un intervallo denominato QuarterlyTotal include le celle da C3 a C5, che contengono i valori delle vendite trimestrali.

Nella cella C5, la barra della formula mostra:

=QuarterlyTotal*0.1Offerto da

A prima vista sembra tutto ok. La formula non fa riferimento direttamente a C5. Tuttavia, poiché QuarterlyTotal include C5, Excel deve calcolare C5 come parte del totale usato per calcolare C5.

Questo crea un ciclo nascosto:

• Excel prova a calcolare QuarterlyTotal

• QuarterlyTotal include C5

• C5 dipende da QuarterlyTotal

Il riferimento è circolare, anche se la sintassi della formula non lo mostra esplicitamente.

È proprio questo a rendere i riferimenti circolari nascosti così difficili da diagnosticare. Le formule sembrano corrette, il file può calcolare normalmente per molto tempo e il riferimento circolare emerge solo quando Excel valuta l'intervallo denominato.

Riferimento circolare nascosto. Immagine dell'autore.

La logica condizionale può rendere tutto ancora più difficile da rilevare. Perché? Perché le istruzioni IF() annidate possono creare un riferimento circolare solo in condizioni specifiche. Di conseguenza, il file funziona finché non cambiano certi valori.

I riferimenti circolari nascosti sono i più difficili da correggere perché il ciclo di dipendenza è mascherato dall'astrazione invece che da riferimenti di cella visibili.

Riferimenti circolari intenzionali vs accidentali

Alcuni riferimenti circolari esistono per scelta progettuale. Altri sono errori da eliminare.

Riferimenti circolari intenzionali (deliberati)

Un riferimento circolare intenzionale viene creato appositamente per supportare specifiche esigenze di modellazione in cui i valori dipendono a vicenda.

Prendiamo la modellazione finanziaria: il costo degli interessi dipende dal saldo del debito residuo, mentre il saldo del debito cambia in base al flusso di cassa, che include il costo degli interessi. Questo crea un circuito di feedback che riflette la logica aziendale reale.

Il modello parte da una struttura semplice:

• Il saldo del debito rappresenta l'importo iniziale
• Il costo degli interessi è calcolato come percentuale del debito
• Il debito aggiornato include sia il saldo originale sia gli interessi

Riferimenti circolari intenzionali. Immagine dell'autore.

Quando gli interessi sono calcolati sul debito e il debito aggiornato include quegli interessi, il calcolo torna naturalmente su se stesso. Excel segnala questo come riferimento circolare e restituisce zero per impostazione predefinita perché non può risolvere la dipendenza in un solo passaggio.

Per evitarlo, attiva il calcolo iterativo in questo modo:

1. Vai su File > Opzioni > Formule
2. Nella sezione Opzioni di calcolo, seleziona la casella Abilita calcolo iterativo

Abilita l'opzione di calcolo iterativo. Immagine dell'autore.

Una volta abilitato il calcolo iterativo, Excel ricalcola ripetutamente gli interessi e il debito aggiornato. A ogni passaggio, i numeri si aggiustano leggermente finché non si stabilizzano e smettono di cambiare. 

Il risultato finale rappresenta una soluzione convergente, non un calcolo a singolo passaggio. Questo funziona perché il riferimento circolare è intenzionale. Gli interessi influenzano il debito e il debito influenza gli interessi. Ogni valore influenza l'altro, quindi il ricalcolo ripetuto è previsto e necessario.

Calcolo iterativo. Immagine dell'autore.

Nota: il calcolo iterativo di per sé non garantisce un risultato significativo. Ripete la logica che hai costruito nel modello, ed è per questo che i riferimenti circolari intenzionali richiedono monitoraggio e validazione accurati.

Riferimenti circolari accidentali

Un riferimento circolare accidentale deriva da errori di formula, errori di copia-incolla o modifiche non volute. Supponiamo di inserire i valori 2, 4 e 6 nelle celle A1:A3.

In A4 vuoi un totale, quindi inserisci:

=SUM(A1:A4)Offerto da

La cella A4 ora include se stessa nel calcolo.

Quando premi Invio, Excel mostra un avviso di riferimento circolare. Dopo aver fatto clic su OK, Excel restituisce 0.

Riferimento circolare accidentale. Immagine dell'autore.

Questo riferimento circolare non rappresenta una logica reale. Esiste perché l'intervallo della formula è sbagliato.

Come correggere i riferimenti circolari in Excel

Una volta che Excel segnala un ciclo, devi individuare esattamente da dove parte e decidere come risolverlo. 

Vediamo alcuni metodi per correggere i riferimenti circolari, a seconda che siano accidentali o intenzionali: 

Rilevamento rapido per individuare il problema

Excel include diversi strumenti integrati per individuare rapidamente i riferimenti circolari, ciascuno con punti di forza e limiti. Vediamo insieme queste opzioni. 

Notifiche sulla barra di stato

Il modo più veloce per rilevare un riferimento circolare è tramite la barra di stato nell'angolo in basso a sinistra della finestra di Excel.

Quando esiste un riferimento circolare, Excel mostra un testo come “Riferimenti circolari: G4”. Questo indica la cella più recentemente rilevata coinvolta in un ciclo.

Mostra il riferimento circolare sulla barra di stato. Immagine dell'autore.

Tuttavia, la barra di stato mostra un solo riferimento circolare alla volta. Se esistono più cicli, Excel li rivela uno alla volta.

Ecco come procedere: 

1. Correggi la cella mostrata nella barra di stato
2. Controlla di nuovo la barra di stato
3. Ripeti finché il messaggio scompare

Suggerimento: annota ogni indirizzo di cella, perché correggere un ciclo spesso ne rivela un altro.

Messaggi di avviso iniziali

Excel mostra anche un messaggio di avviso quando:

• Crei un riferimento circolare, oppure
• Apri una cartella di lavoro che ne contiene già uno

L'avviso di solito afferma che una o più formule fanno riferimento a se stesse e potrebbero calcolare in modo non corretto.

Questi avvisi possono smettere di apparire se:

• Hai ignorato il messaggio in precedenza facendo clic su OK
• Il calcolo iterativo è abilitato
• La modalità di calcolo è impostata su Manuale (Formule > Opzioni di calcolo > Manuale)

Se i risultati sembrano errati e non compare alcun avviso, forza un ricalcolo così:

• Premi F9 per ricalcolare il foglio attivo
• Premi Ctrl + Alt + F9 per forzare il ricalcolo su tutti i fogli

Suggerimento: Chiudere e riaprire la cartella di lavoro può far comparire di nuovo l'avviso.

Strumento di controllo errori

Per visualizzare più riferimenti circolari contemporaneamente, usa lo strumento Controllo errori di Excel: vai su Formule > Controllo errori > Riferimenti circolari

Questo menu elenca tutti i riferimenti circolari rilevati e ti permette di saltare direttamente a ciascuna cella, facilitando la diagnosi dei file complessi.

Se il sottomenu Riferimenti circolari appare vuoto ma Excel si comporta ancora in modo anomalo, premi F9 per forzare il ricalcolo e aggiornare l'elenco.

Uno strumento di controllo errori per trovare i riferimenti circolari. Immagine dell'autore.

Nota: In Excel 2007 e versioni successive, questa funzione è disponibile nel Ribbon. Le versioni precedenti offrono opzioni simili tramite la barra dei menu.

Strumenti di tracciamento visivo

Una volta individuata una cella problematica, usa gli strumenti di tracciamento visivo per vedere come si forma il riferimento circolare.

Questi strumenti disegnano frecce tra le celle e rendono visibili le dipendenze invece di nasconderle dentro le formule. Entrambi sono disponibili nel gruppo Verifica formule nella scheda Formule:

• Individua precedenti mostra quali celle alimentano la formula selezionata. Excel disegna frecce dalle celle di input verso la cella della formula. Scorciatoia: Alt + M, P
• Individua dipendenti mostra quali celle usano il valore della cella selezionata. Excel disegna frecce dalla cella selezionata verso le formule che la referenziano. Scorciatoia: Alt + M, D

Se il tracciamento di precedenti e dipendenti porta alla fine di nuovo alla cella di partenza, le frecce formano un ciclo e confermano il riferimento circolare.

Individua precedenti e Individua dipendenti. Immagine dell'autore.

Dopo aver corretto la formula, le frecce non torneranno più indietro e il riferimento circolare scomparirà. Puoi rimuovere le frecce con Rimuovi frecce nello stesso menu.

Ristrutturazione delle formule e calcoli alternativi

La maggior parte dei riferimenti circolari si verifica perché i calcoli sono ordinati in modo scorretto, non perché Excel non funzioni. Per questi riferimenti circolari accidentali, la ristrutturazione delle formule è la soluzione più efficace. 

Ecco alcuni metodi per risolvere il problema:  

Ristruttura le formule per rimuovere le auto-dipendenze

I riferimenti circolari spesso compaiono quando una formula prova a calcolare un valore usando il proprio risultato.

Per esempio, una formula come =A2+1 nella cella A2 crea un ciclo immediato perché la cella dipende da se stessa. Per risolvere, sposta l'input necessario in una cella separata e fai riferimento a quel valore.

Una volta rimossa la dipendenza, Excel può calcolare normalmente la formula.

Usa colonne di supporto per calcoli intermedi

I riferimenti circolari si verificano anche quando una singola formula cerca di gestire più passaggi contemporaneamente.

Per esempio, se due celle dipendono l'una dall'altra per calcolare un valore condiviso, Excel non ha un punto di partenza chiaro. Per risolvere, introduci una cella di supporto che esegua prima il calcolo intermedio.

Calcolando i valori condivisi in una cella di supporto separata, Excel può seguire un ordine chiaro:

1. La cella di supporto calcola per prima
2. Le formule dipendenti calcolano dopo

Questo approccio semplifica le formule ed elimina le dipendenze circolari.

Separa i riepiloghi dai calcoli

Totali e formule di riepilogo non dovrebbero essere mescolati nelle stesse celle da cui traggono i numeri. Quando un riepilogo preleva numeri da un intervallo che include se stesso, Excel crea un riferimento circolare. 

Per esempio, se A2 contiene un importo base di 1000 e B2 contiene un'aliquota del 10%, C2 mostra l'importo totale e D2 calcola una commissione.

Se C2 usa =A2+D2 e D2 usa =C2*$B$2, compare un riferimento circolare perché il totale dipende dalla commissione e la commissione dipende dal totale. 

Excel non può calcolare prima nessuno dei due.

Per risolvere, la commissione dovrebbe essere calcolata dal valore base, non dal totale. Quindi modifica D2 in =A2*$B$2 e lascia C2 come =A2+D2.

Usa celle separate per evitare riferimenti circolari. Immagine dell'autore.

Ora Excel può calcolare prima la commissione e poi aggiungerla al valore base. Il riferimento circolare scompare perché ogni formula ha un punto di partenza chiaro.

Abilitare il calcolo iterativo per logiche circolari intenzionali

Il calcolo iterativo andrebbe usato solo quando i riferimenti circolari sono elementi progettuali intenzionali, non errori accidentali. Questo metodo è adatto ai modelli finanziari e ad altri scenari in cui esistono circuiti di feedback legittimi e la logica circolare riflette il comportamento reale.

Per abilitare il calcolo iterativo in Excel per Windows:

1. Vai su File > Opzioni > Formule
2. In Opzioni di calcolo, seleziona Abilita calcolo iterativo
3. Fai clic su OK

Una volta abilitato, Excel ricalcola ripetutamente le formule coinvolte nei riferimenti circolari finché non si stabilizzano o raggiungono una condizione di arresto.

Impostazioni chiave dell'iterazione

Due impostazioni controllano il comportamento del calcolo iterativo:

• Numero massimo di iterazioni imposta quante volte Excel ricalcola le formule prima di fermarsi. Un valore di 100 iterazioni funziona bene per la maggior parte dei modelli aziendali e finanziari.
• Variazione massima definisce quanto deve essere piccola la differenza tra i risultati di calcolo prima che Excel smetta di ricalcolare. Un valore di 0,001 è un punto di partenza comune che bilancia accuratezza e prestazioni.

Aumentare una delle due impostazioni può migliorare la precisione ma potrebbe rallentare il ricalcolo in cartelle di lavoro grandi.

Quando usare questo metodo

Abilita il calcolo iterativo solo quando:

• Il riferimento circolare rappresenta una logica aziendale valida
• Il modello include circuiti di feedback legittimi (per esempio, interessi che dipendono dal debito, mentre il debito cambia in base agli interessi)
• Ti aspetti che i valori convergano piuttosto che oscillare all'infinito

Questo metodo non è una soluzione per i riferimenti circolari accidentali causati da errori di formula.

Avvertenze importanti

Prima di abilitare il calcolo iterativo, tieni a mente questi rischi:

• I riferimenti circolari accidentali potrebbero non attivare più avvisi, rendendo gli errori più difficili da individuare.
• I modelli grandi o complessi potrebbero ricalcolare più lentamente.
• Se i valori oscillano invece di stabilizzarsi, i risultati potrebbero non essere affidabili.

Per questi motivi, il calcolo iterativo dovrebbe essere sempre documentato esplicitamente.

Implementazione di un "interruttore" per modelli complessi

Un interruttore è una tecnica avanzata che ti permette di controllare quando la logica circolare è attiva. Invece di rimuovere un riferimento circolare, lo metti volutamente in pausa o lo consenti usando un valore di controllo all'interno della formula.

Questo approccio è più adatto a modellatori finanziari e power user che hanno bisogno di logica circolare in alcuni scenari ma non sempre.

Un interruttore in genere usa:

• Una cella di controllo (spesso impostata a 0 o 1)

• Una funzione IF che passa dalla logica circolare a quella non circolare

Il modello generale è questo:

=IF($A$1=1, circular_logic, non_circular_alternative)Offerto da

• Quando la cella di controllo è 0, la formula evita il riferimento circolare
• Quando la cella di controllo è 1, la logica circolare è consentita

Supponiamo di inserire i seguenti valori: 

• A1: Interruttore (0 o 1)

• A2: Saldo iniziale = 1000

• B2: Interessi

• C2: Saldo aggiornato

In B2, usa =IF($A$1=1, C2*10%, A2*10%) e in C2 inserisci =A2+B2

Ora:

• Quando A1 = 0, gli interessi sono calcolati dal saldo iniziale. Il riferimento circolare è disattivato ed Excel calcola normalmente.

• Quando A1 = 1, gli interessi sono calcolati dal saldo aggiornato. La logica circolare diventa attiva e crea un circuito di feedback intenzionale.

Uso di un interruttore. Immagine dell'autore.

Quando usare un interruttore

Gli interruttori sono utili per:

• Tenere la logica circolare disattivata durante la costruzione e la validazione delle formule
• Attivare e disattivare la logica circolare per isolare i problemi e verificare i risultati
• Mostrare il modello con la logica circolare disattivata per rendere più semplici da spiegare ipotesi e meccanismi

Rimuovere formule problematiche e ripartire da zero

Questa è l'opzione “nucleare”: usala quando gli altri metodi falliscono. È indicata per cartelle di lavoro con dipendenze senza speranza di districarsi, file ereditati con logica poco chiara o situazioni in cui la pressione del tempo richiede una soluzione rapida ma affidabile.

Invece di provare a districare formule rotte, azzeri il modello e lo ricostruisci in modo metodico. 

Vediamo come fare.

Passaggio 1: crea una copia di backup

Prima di apportare modifiche, crea un backup completo della cartella di lavoro. Per farlo:

• Apri la cartella di lavoro in Excel

• Vai su File > Salva con nome > Sfoglia e scegli una posizione diversa o mantieni la stessa cartella per salvare il file

• Rinomina il file, per esempio, Workbook_Backup.xlsx

• Fai clic su Salva

Crea una copia di backup di una cartella di lavoro. Immagine dell'autore.

Questo conserva le formule, la logica e i risultati originali, così potrai consultarli in seguito o tornare indietro se necessario.

Non tentare mai questo metodo sull'unica copia di un file.

Passaggio 2: documenta la struttura attuale

Successivamente, cattura come funziona attualmente la cartella di lavoro:

• Fai screenshot delle sezioni chiave
• Annota cosa calcola ciascun foglio
• Identifica input, output e riepiloghi

Questa documentazione ti aiuta a capire cosa va ricostruito e impedisce che durante la pulizia si perda logica importante.

Passaggio 3: sostituisci le formule con valori

Per interrompere tutte le dipendenze circolari in una volta, copia gli intervalli interessati e usa Incolla speciale. Click destro > Incolla speciale > Valori per sostituire le formule con i risultati calcolati.

Usa l'opzione Incolla speciale. Immagine dell'autore.

Questo rimuove immediatamente ogni riferimento circolare mantenendo i numeri visibili come base temporanea.

Passaggio 4: ricostruisci le formule in modo incrementale

Ricostruisci la cartella di lavoro sezione per sezione invece che tutta insieme:

1. Aggiungi le formule per un blocco logico
2. Premi F9 per forzare il ricalcolo
3. Verifica che non compaia alcun riferimento circolare
4. Passa alla sezione successiva

Passaggio 5: verifica i risultati con il backup

Man mano che ricostruisci ogni sezione, confronta i risultati con il file di backup. Potresti notare differenze che indicano:

• Input mancanti
• Logica di formula errata
• Errori di calcolo precedentemente nascosti

Rilevamento avanzato per riferimenti sfuggenti

Alcuni riferimenti circolari non emergono con gli strumenti standard di Excel. In questi casi, prova tecniche avanzate: 

Macro VBA per una scansione completa

Quando il tracciamento manuale è troppo lento, VBA può scansionare migliaia di celle in pochi secondi e identificare riferimenti circolari in tutta la cartella di lavoro.

L'approccio di base è questo:

• Premi Alt + F11 per aprire l'Editor di Visual Basic.
• Vai su Inserisci > Modulo.
• Incolla una macro per il rilevamento dei riferimenti circolari.
• Premi F5 per eseguirla.

Interfaccia VBA. Immagine dell'autore.

Suggerimento: Non devi scrivere il VBA da zero. Descrivi in linguaggio naturale cosa vuoi e lascia che ChatGPT generi una macro di rilevamento personalizzata.

Ecco un esempio di script 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 FunctionOfferto da

Usa macro VBA per identificare le celle con riferimenti circolari. Immagine dell'autore.

Questa macro controlla il foglio attivo alla ricerca di riferimenti circolari nelle formule. Se una formula fa riferimento a se stessa, direttamente o tramite altre celle, la macro evidenzia quelle celle così puoi vedere facilmente dove si trova il problema. Funziona anche se il calcolo iterativo è attivo.

La VBA ha evidenziato le celle con riferimenti circolari. Immagine dell'autore.

Indagine tra più fogli

I riferimenti circolari che si estendono su più fogli di lavoro sono più difficili da rilevare perché le frecce di tracciamento non attraversano le schede.

Potrebbe presentarsi così:

• Il Foglio A fa riferimento al Foglio B
• Il Foglio B fa riferimento al Foglio C
• Il Foglio C fa riferimento al Foglio A

Un modo per rilevarlo è mappare le dipendenze tra fogli. Per farlo, annota quali fogli fanno riferimento ad altri fogli e cerca cicli in quell'elenco.

Puoi anche usare Trova e sostituisci per individuare riferimenti tra fogli:

• Premi Ctrl + H

• Inserisci un nome di foglio seguito da ! (per esempio, Sheet1!)

• Fai clic su Trova tutto

Excel elencherà ogni formula che fa riferimento a quel foglio, così potrai tracciare le dipendenze tra schede.

Trova i fogli collegati in una cartella di lavoro. Immagine dell'autore.

Nota: Trova e sostituisci non rileva automaticamente i riferimenti circolari. Si limita a individuare le dipendenze tra fogli, che poi devi analizzare alla ricerca di cicli.

Risoluzione dei problemi con intervalli denominati

Gli intervalli denominati possono nascondere riferimenti circolari perché le formule non mostrano più indirizzi di cella diretti. Per esaminarli:

• Apri il “Gestione nomi” con Ctrl + F3
• Controlla la definizione di ogni intervallo denominato
• Cerca intervalli che includono celle che, nelle formule, fanno riferimento allo stesso nome

Controlla gli intervalli denominati. Immagine dell'autore.

Problemi con la logica condizionale

Alcuni riferimenti circolari compaiono solo in condizioni specifiche.

Per esempio:

=IF(A1>100, A2+10, 100)Offerto da

Quando A1 è sotto 100, Excel usa la seconda condizione e il riferimento circolare non esiste. Quando A1 supera 100, Excel valuta la prima condizione e prova a calcolare A2 usando A2 stessa, attivando un riferimento circolare.

Per rilevare questi problemi:

• Prova valori di input diversi per forzare tutti i percorsi condizionali
• Osserva se gli avvisi di riferimento circolare compaiono solo in certe condizioni

Per correggerli, sposta la logica circolare in celle di supporto separate e semplifica le istruzioni IF annidate in calcoli più chiari e passo-passo.

Come prevenire i riferimenti circolari in Excel

Alcune buone abitudini iniziali possono farti risparmiare ore di debug man mano che i file crescono o vengono condivisi con altri. 

Ecco quindi alcune best practice da seguire: 

Pianificazione della cartella di lavoro e progettazione delle formule

Una buona struttura previene la maggior parte dei riferimenti circolari sul nascere. Assicurati di fare quanto segue:

• Separa input, calcoli e output così che le formule vadano in un'unica direzione chiara.

• Usa celle o colonne di supporto invece di fare tutto in un'unica formula.

• I calcoli dovrebbero dipendere da valori precedenti, non dai propri risultati o da totali a valle.

• Tieni le celle di input lontane da totali e riepiloghi per evitare intervalli auto-referenti.

Operazioni di copia-incolla e gestione dei riferimenti

Molti riferimenti circolari vengono introdotti durante il copia-incolla. Ecco quindi cosa considerare: 

• Incolla prima una formula in una sola cella e controlla la Barra delle formule prima di copiarla su un intervallo.

• Usa riferimenti assoluti (per esempio, $A$1) per aliquote e assunzioni che non devono spostarsi.

• Non inserire formule di riepilogo dentro gli intervalli che calcolano.

• Nomi chiari rendono evidente a cosa fa riferimento una formula e riducono gli errori quando gli intervalli si espandono.

Documentazione e verifiche periodiche

Una documentazione leggera è di grande aiuto. Ecco perché devi tenere a mente quanto segue quando lavori:

• Aggiungi brevi commenti alle celle o mantieni un semplice foglio di documentazione.

• Rivedi le formule con gli strumenti di Verifica formule prima di presentazioni importanti o passaggi di consegne.

• Salva versioni dei file critici per poter risalire a quando sono stati introdotti i problemi.

• Nei file collaborativi, verifica regolarmente le modifiche per evitare che piccoli errori si diffondano.

Considerazioni finali

Se stai affrontando un riferimento circolare proprio ora, verifica se è accidentale o intenzionale. Questa decisione determina se devi ristrutturare le formule, abilitare l'iterazione o riprogettare parte del modello.

Non correre a modificare impostazioni o cercare soluzioni alternative finché non sei sicuro che la logica abbia senso.

Una volta risolto il problema, prenditi un momento per rendere il file più facile da usare in futuro. Aggiungi brevi note alle formule importanti, salva una copia pulita della cartella di lavoro e assicurati che chiunque altro la utilizzi capisca come dovrebbero funzionare i calcoli.