Comment trouver une référence circulaire dans Excel

Les références circulaires peuvent être assez délicates, c'est pourquoi il est important de savoir comment les trouver. Excel dispose d'un mécanisme intégré capable de détecter les références circulaires et d'empêcher les calculs de se dérouler dans une boucle sans fin. Vous avez juste besoin de savoir comment activer et utiliser cette fonctionnalité à votre avantage.

Dans cet article, nous allons vous montrer comment trouver des références circulaires dans Excel. De plus, nous expliquerons ce que sont exactement les références circulaires en premier lieu et pourquoi vous devriez les éviter.

Comment trouver une référence circulaire dans Microsoft Excel

Lorsque vous essayez d'entrer une formule dans votre classeur Excel et que vous rencontrez un problème, vous avez peut-être affaire à une référence circulaire. Cela se produit lorsque la formule essaie d'utiliser sa propre valeur pour effectuer des calculs. À ce stade, Excel vous enverra un message d'avertissement :

« Il existe une ou plusieurs références circulaires où une formule fait référence à sa propre cellule directement ou indirectement. Cela pourrait les amener à calculer de manière incorrecte.

Étant donné qu'une boucle sans fin peut continuer indéfiniment ou s'arrêter avant que la bonne réponse ne soit atteinte, il est préférable de rester à l'écart des références circulaires dans Excel. Non seulement cela, mais les références circulaires peuvent également ralentir considérablement l'ensemble du processus de calcul dans vos classeurs. Cependant, dans la plupart des cas, le plus gros problème avec les références circulaires est de les identifier.

Il existe trois types de références circulaires : involontaires, intentionnelles et cachées. La majorité des références circulaires ne sont pas intentionnelles car il faudrait quelqu'un qui maîtrise Excel pour créer une référence circulaire délibérée. Enfin, nous avons des références circulaires cachées. Alors que les références circulaires accidentelles sont faciles à trouver, Excel ne peut pas toujours détecter les références circulaires masquées, vous devrez donc utiliser d'autres méthodes.

Lorsque le message d'avertissement apparaît, vous pouvez soit cliquer sur le bouton "OK" ou "Aide". Ce dernier ne vous donnera que plus d'informations sur les références circulaires sans indiquer où elles se trouvent dans votre classeur. D'autre part, si vous choisissez "OK" ou si vous désactivez simplement le message, vous trouverez soit la dernière valeur calculée, soit zéro dans votre dernière cellule. Il est également important de noter que cette notification n'apparaîtra pas toujours. Par exemple, si vous continuez à créer plus de références circulaires, intentionnellement ou non, Excel cessera de vous avertir à ce sujet.

Très rarement, une formule contenant une référence circulaire peut être complétée avant que le mécanisme d'auto-calcul ne soit en marche. Dans ce cas, seule la dernière valeur réussie sera affichée en conséquence. En d'autres termes, une référence circulaire peut rendre le système insensible. C'est pourquoi l'identifier est l'étape la plus importante.

Afin de corriger une erreur de référence circulaire dans Excel, vous devez d'abord la trouver. Suivez les étapes ci-dessous pour savoir comment procéder :

1. Désactivez le message d'avertissement affiché par Excel.
2. Allez dans l'onglet "Formules" dans le menu du haut.
   
3. Accédez à l'onglet "Vérification des erreurs" et cliquez dessus.
   
4. Cliquez sur "Références circulaires" dans le menu déroulant. C'est là que toutes les références circulaires seront révélées.
   
5. Cliquez sur la valeur dans la liste contextuelle et vous serez directement dirigé vers cette référence circulaire.
   

Lorsque vous cliquez sur la cellule qui contient la référence circulaire, celle-ci s'affichera également dans votre barre d'adresse en bas de la feuille.

Si vous avez besoin de plus d'aide avec la référence circulaire, il existe deux outils qui peuvent vous aider - tracer les précédents et tracer les personnes à charge. Le premier outil, tracer les précédents, affiche des lignes bleues dans le classeur Excel qui vous montrent quelles cellules affectent la cellule sur laquelle vous avez cliqué. Les traces dépendantes, en revanche, font le contraire. Ils tracent des lignes pour vous montrer quelles cellules sont affectées par la cellule sur laquelle vous avez cliqué. Ces deux fonctionnalités vous aident à trouver des références circulaires qu'Excel ne peut pas détecter. Gardez à l'esprit que ces traceurs ne vous montrent pas exactement où se trouve la référence circulaire, juste un indice pour vous aider à le trouver plus rapidement.

Si vous souhaitez activer les précédents de trace et les dépendants de trace, suivez les étapes ci-dessous :

1. Accédez à l'onglet "Formules" en haut de votre feuille de calcul.
   
2. Passez à la catégorie "Audit de formule".
   
3. Sélectionnez soit "Tracer les précédents" ou "Tracer les personnes à charge".
   

Vous ne pouvez en sélectionner qu'un à la fois. Un moyen plus rapide de le faire est d'utiliser ces raccourcis : "Alt + TUT" pour les précédents de trace, ou "Alt + TUD" pour les dépendants de la trace.

Certains utilisateurs d'Excel créent des références circulaires exprès pour effectuer des calculs itératifs. Mais ce n'est généralement pas une bonne idée d'incorporer des références circulaires dans vos feuilles.

FAQ supplémentaire

Comment supprimer une référence circulaire

Lorsque vous trouvez enfin la référence circulaire à l'origine de tous les problèmes dans votre classeur Excel, vous devez la supprimer. Bien qu'il n'y ait aucun moyen de le réparer automatiquement, vous pouvez déterminer quelle partie de la formule doit être supprimée ou remplacée. Vous saurez que vous avez résolu le problème lorsque vous cliquez sur la cellule et qu'il n'y a pas de balise "Référence circulaire" dans la barre d'adresse.

Les références circulaires ne peuvent être créées dans votre feuille Excel que si la fonction de calcul itératif est activée. Cette fonctionnalité est désactivée par défaut, il n'y a donc généralement rien à se reprocher. Cependant, si vous souhaitez vérifier si la fonctionnalité de calcul itératif a été activée d'une manière ou d'une autre, voici ce que vous devez faire :

1. Allez dans l'onglet "Fichier" dans le coin supérieur gauche de votre écran.

2. Passez à la section « Options » en bas du menu.

3. Choisissez "Formules" dans la barre latérale gauche de la fenêtre contextuelle.

4. Sous « Options de calcul », accédez à la case « Activer le calcul itératif » pour voir si elle est cochée.

5. Décochez-la pour désactiver les itérations.

6. Cliquez sur le bouton "OK".

Cette méthode peut être appliquée à Excel 2010, Excel 2013, Excel 2016 et Excel 2019. Si vous avez Excel 2007, vous trouverez la fonction de calcul itératif lorsque vous cliquez sur le bouton Office et accédez à "Options Excel". La section "Zone d'itération" sera dans l'onglet "Formules". Si vous avez Excel 2003 ou une version antérieure, vous devez aller dans "Menu" puis dans l'onglet "Outils". L'onglet "Calcul" sera dans la section "Options".

Localisez toutes les références circulaires dans votre classeur Excel

Dans la plupart des cas, les références circulaires sont créées par accident, mais elles peuvent être assez problématiques. Non seulement ils gâchent toute votre formule, mais ils peuvent également ralentir l'ensemble du processus de calcul. C'est pourquoi il est important de les trouver et de les remplacer au plus vite. Heureusement, Excel vous informera dès qu'un élément sera détecté. Vous pouvez également utiliser les précédents de trace et les dépendants de trace pour vous aider à découvrir la relation entre la référence circulaire et vos autres cellules.

Avez-vous déjà essayé de trouver une référence circulaire dans votre feuille Excel auparavant ? Comment avez-vous trouvé? Faites-nous savoir dans la section commentaires ci-dessous.