Comment trouver des références circulaires dans Excel pour éviter les données erronées

Avez-vous vu l'erreur de référence circulaire sur une feuille Excel et vous vous demandez si la feuille de calcul est cassée ? Fais-moi confiance! Vous n'êtes pas le premier à faire face à cette erreur Excel. Des centaines et des milliers d'utilisateurs d'Excel rencontrent chaque jour des erreurs de référence circulaires. Ne pas s'inquiéter! Voici comment trouver des références circulaires dans Excel et les résoudre.

En tant qu'utilisateur passionné d'Excel, vous tapez rarement les mêmes données dans plusieurs cellules. Au lieu de cela, vous importez un ensemble de données à partir de la source et utilisez le référencement des cellules pour créer des formules. Ainsi, vous créez une feuille de calcul dynamique.

Lorsque quelqu'un modifie les valeurs de cellule des références de cellule, la feuille de calcul se met à jour automatiquement. C'est ce qu'on appelle l'automatisation de l'analyse des données et les analystes de données adorent jouer avec les références de cellules.

Cependant, le problème survient lorsque vous tapez intentionnellement ou accidentellement l'adresse de la cellule où vous effectuez actuellement un calcul. Ou, lorsque vous créez de nombreuses formules dans la feuille de calcul, une cellule contenant une formule apparaît indirectement dans une autre formule.

Dans de telles situations et dans de nombreuses autres situations similaires, vous obtenez l'erreur de référence circulaire dans Excel. Lisez la suite pour savoir ce qu'est une référence circulaire, ses raisons, pourquoi l'éviter et, surtout, comment trouver des références circulaires dans Excel pour se débarrasser définitivement de ces anomalies.

Qu'est-ce qu'une référence circulaire dans Excel ?

Excel rencontre une erreur de référence circulaire lorsqu'il constate qu'une ou plusieurs fonctions de la feuille de calcul font référence à l'adresse de cellule où leurs fonctions ont été créées.

Cela peut vous sembler déroutant, alors réfléchissez à la gravité de l'application Excel. En bref, vous ne voulez jamais mettre l'adresse de la cellule dans une formule où vous la créez. Regardez l'exemple suivant pour une clarification visuelle :

Vous essayez de multiplier 10 à partir de la cellule B1 et de mettre le résultat dans la cellule B1 elle-même.

Maintenant, dans le scénario ci-dessus, si vous appuyez simplement sur OK , Excel fermera l'erreur et affichera zéro comme résultat calculé. Ceci est juste un exemple simple.

Qu'est-ce qu'une référence circulaire dans Excel

Maintenant, imaginez qu'il y ait des centaines de formules dans une feuille de calcul de comptabilité, une feuille de calcul de planification financière, la paie des employés, une feuille de calcul de paiement des fournisseurs, etc.

Vous ferez confiance aux données erronées calculées par Excel en raison de l'erreur de référence circulaire et cela aura à son tour un impact sur votre entreprise ou votre prise de décision professionnelle.

De plus, le référencement circulaire peut réduire considérablement la vitesse des calculs dans l'application Excel. Dans les cas graves, l'erreur même peut également entraîner des performances extrêmement lentes sur le PC si elle n'est pas traitée immédiatement.

Toutefois, si vous travaillez sur des formules Excel avancées où votre modèle de données nécessite des entrées intermédiaires de la référence circulaire pour continuer avec une fonction longue, vous devrez peut-être l'utiliser. En fait, Excel vous permet d'utiliser cette méthode de calcul lorsque cela est nécessaire.

Par exemple, dans les unités de production d'énergie, comme les éoliennes, vous devez calculer l'énergie de sortie recyclée à partir d'une fraction de l'énergie d'entrée. Alternativement, dans une usine chimique, les ingénieurs utilisent la tige de sortie comme entrée pour la même chaudière ou une autre chaudière ou cuve de traitement. De tels calculs d'ingénierie de haut niveau nécessitent en effet des références circulaires pour des calculs itératifs.

Quand voyez-vous l'erreur de référence circulaire dans Excel ?

De toute évidence, la fenêtre contextuelle de référence circulaire n'est pas un message d'erreur. C'est un moyen pour Excel de vous informer qu'il a rencontré une référence circulaire dans une ou plusieurs formules et que cela peut influencer son algorithme de calcul et ses performances.

Pour éviter tout dommage causé par les références circulaires dans les formules Excel, vous devez savoir quand elles apparaissent et quand elles n'apparaissent pas. Retrouvez ci-dessous les instances :

• Dans une feuille de calcul ou un classeur ouvert, vous créez une formule et entrez la référence circulaire pour la première fois.
• Vous ou quelqu'un avez supprimé toutes les références circulaires du fichier Excel et l'avez enregistré. Maintenant, encore une fois, vous ou un autre utilisateur entrez une référence circulaire dans le même fichier.
• Vous avez fermé tous les classeurs ouverts. Maintenant, ouvrez un classeur différent, puis tapez une formule contenant un référencement circulaire.

Quand voyez-vous l'erreur de référence circulaire dans Excel

• Vous venez d'ouvrir un classeur qui contenait auparavant une ou plusieurs références circulaires. Vous verrez l'avertissement.
• Une fois qu'Excel vous a montré le message d'avertissement de référence circulaire une fois et que vous avez cliqué sur OK , il n'affichera plus la même fenêtre contextuelle dans la même session, même si vous continuez à créer davantage de formules référencées circulaires.

Ainsi, vous devez rechercher attentivement les instances ci-dessus pour détecter les avertissements de référence circulaire et les résoudre si vous ne recherchez pas de calculs itératifs.

Types de références circulaires dans Excel

Si vous voulez apprendre à trouver des références circulaires dans Excel, vous devez comprendre leurs types. Parce que trouver et éliminer les références circulaires dans Excel n'est pas une tâche automatisée. Trouvez ci-dessous les types courants de cette erreur :

Un exemple d'utilisation de référence circulaire

• Les références circulaires intentionnelles se produisent lorsque vous les utilisez intentionnellement à des fins de calcul itératif. Par exemple, vous devez saisir un horodatage sur une autre cellule lorsque quelqu'un entre une valeur dans une cellule référencée à l'aide de fonctions volatiles et logiques telles que IF, OR, SUMIF, etc.

Référence circulaire involontaire dans Excel

• Référencement circulaire involontaire lorsque vous essayez de travailler plus rapidement sur une formule Excel et que vous vous référez par erreur à la cellule contenant la formule. Par exemple, vous devez appliquer la fonction SUM de la cellule A1 à A9 et récupérer le résultat dans la cellule A10 . Mais par erreur, vous tapez la formule comme celle-ci sur la cellule A10 :

=SOMME(A2:A10)

• Les références circulaires masquées sont celles qui n'affichent aucun message d'avertissement lorsque vous créez une formule ou ouvrez la feuille de calcul pour la première fois. Cela se produit parce que quelqu'un a autorisé le calcul itératif sur le fichier Excel.

Un exemple de référence circulaire indirecte

• Les références circulaires indirectes sont également populaires et se produisent lorsque vous créez une nouvelle formule dans n'importe quelle cellule, par exemple, la cellule A1 , et cette nouvelle formule utiliserait une autre fonction dans une autre cellule, comme la cellule B1 , qui utilise déjà la cellule A1 comme référence .

Maintenant que vous avez compris les bases des références circulaires dans Excel, découvrons ci-dessous comment trouver des références circulaires dans Excel :

Comment trouver des références circulaires dans Excel : à partir de la barre d'état

Si la fonctionnalité de calcul itératif est désactivée, vous pouvez voir les notifications suivantes concernant les références circulaires dans la barre d'état Excel :

• Références circulaires : C3  (ou toute autre référence de cellule) lorsque vous êtes sur une feuille de calcul contenant une formule circulaire dans le classeur.

Affichage des références circulaires dans la barre d'état Excel

• Vous ne verrez des références circulaires que si vous êtes sur une feuille de calcul qui n'en contient pas mais que le classeur contient quelques-unes de ces formules circulaires.

Comment trouver des références circulaires dans Excel : Utilisation de la vérification des erreurs

C'est le meilleur moyen de vérifier les références circulaires dans un classeur ou une feuille de calcul. Voici les étapes que vous devez connaître :

• Ouvrez une feuille de calcul que vous soupçonnez de contenir des formules circulaires.
• Cliquez sur le menu Formules du ruban Excel.

Utilisez la vérification des erreurs pour savoir comment trouver des références circulaires dans Excel

• Dans le groupe de commandes Vérification des formules , cliquez sur la liste déroulante Vérification des erreurs  .
• Passez le curseur sur l' option Références circulaires pour voir toutes les instances disponibles dans toutes les feuilles de calcul.
• Sélectionnez chaque instance et Excel identifiera automatiquement ces cellules.

Comment trouver des références circulaires dans Excel : à partir du suivi de formule

Si vous ne trouvez pas la méthode de vérification des erreurs suffisamment efficace pour détecter un grand nombre de formules avec des références circulaires, vous pouvez essayer ces étapes pour utiliser la méthode de suivi des formules :

• Sélectionnez une cellule référencée ou la cellule contenant la formule.
• Accédez à l' onglet Formules du menu ruban d'Excel, puis cliquez sur Tracer les précédents .
• Excel mettra en surbrillance les cellules qui affectent le résultat de la formule sur la cellule actuelle avec des flèches.

Utiliser le suivi de formule pour Comment trouver des références circulaires dans Excel

• Vous pouvez également cliquer sur Tracer les personnes à charge pour découvrir les cellules impactées par la valeur de la référence de cellule sélectionnée.
• Si Excel n'affiche aucune erreur après avoir cliqué sur les commandes ci-dessus, il n'y a pas de références circulaires dans votre feuille de calcul.
• Répétez les étapes sur toutes les feuilles de calcul pour vérifier l'ensemble du classeur.

Cette méthode est particulièrement importante lorsque les fonctionnalités de calcul itératif sont actives sur la feuille de calcul Excel.

Comment trouver des références circulaires dans Excel : Utilisation des options Excel

• Cliquez sur l' onglet Fichier du ruban Excel, puis ouvrez Options Excel dans la barre latérale qui s'affiche.
• Dans la boîte de dialogue Options Excel , sélectionnez le menu Formules dans la barre latérale, puis décochez la case Activer le calcul itératif .

Comment désactiver le calcul itératif dans Excel

• Cliquez sur OK pour enregistrer les modifications.
• Maintenant, le classeur ou la feuille de calcul vous montrera automatiquement l'avertissement de référence circulaire.
• S'il ne s'affiche pas instantanément, enregistrez le classeur, fermez-le et rouvrez-le pour voir l'avertissement.
• Une fois que vous voyez l'avertissement, vérifiez la barre d'état Excel pour savoir quelle cellule se trouve dans la référence circulaire.

Comment résoudre les références circulaires dans Excel

Le moyen le plus sûr de corriger une ou plusieurs erreurs de référence circulaire sur Excel consiste à modifier manuellement chaque formule qui la contient. De cette façon, vous pouvez résoudre les références circulaires accidentelles et indirectes dans Excel.

Comment résoudre les références circulaires dans Excel

Un autre moyen simple consiste à supprimer la formule de la cellule actuelle et à la placer ailleurs dans la même feuille de calcul.

Comment trouver des références circulaires dans Google Sheets (méthode bonus)

Comme Excel, Google Sheets n'autorise aucune référence circulaire dans une ou plusieurs formules si vous n'avez pas activé la fonction de calcul itératif. Par conséquent, Google Sheets affichera immédiatement le #REF ! erreur sur la cellule qui contient une formule circulaire.

Google Sheets Comment trouver des références circulaires

Par conséquent, vous pouvez simplement sélectionner toutes les cellules contenant le #REF ! avertissement pour rechercher et résoudre des références circulaires sur Google Sheets.

Références circulaires Excel : derniers mots

Donc, maintenant vous connaissez tous les secrets pour trouver des références circulaires dans Excel. Utilisez la méthode que vous aimez et abandonnez toutes les références circulaires pour vous assurer de ne pas être confus par des données non fiables provenant d'un rapport Excel ou d'un tableau de bord mathématique.

Ensuite, les  meilleurs modèles de calendrier Excel pour chaque année  et les  meilleurs modèles de budget Excel .