comment résoudre le problème avec sheetgo

Comment trouver les références circulaires dans Excel (et les corriger)

Excel propose un ensemble vaste et polyvalent de formules qui vont des calculs de base aux plus avancés. Cependant, plus la complexité augmente, plus vous risquez de rencontrer un problème dans vos données.

Si vous avez rencontré ce message d'erreur, "Il existe une ou plusieurs références circulaires où une formule fait référence à sa propre cellule, directement ou indirectement. Cela risque d'entraîner un calcul incorrect. Essayez de supprimer ou de modifier ces références, ou de déplacer les formules dans d'autres cellules.", c'est probablement que vous avez essayé d'appliquer une formule à sa propre cellule.

À la fin de cet article, vous saurez localiser et supprimer efficacement une référence circulaire dans Excel pour résoudre rapidement tout problème que vous pourriez rencontrer dans vos données.

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

Selon Microsoft, une référence circulaire se produit lorsque "votre formule tente de se calculer elle-même". En termes plus simples, la formule fait référence à la cellule même où elle se trouve, ce qui crée une boucle de références qui fait tourner la formule sans fin. Les exemples ci-dessous montrent à quoi ressemble une référence circulaire dans Excel.

Exemples de référence circulaire

Si vous sélectionnez une cellule et tapez le signe "=" suivi de cette même cellule, vous créez une référence circulaire. Par exemple, "=E1".

Comment trouver une référence circulaire dans Excel — cellule E1 avec la formule =E1 saisie

Comme vous pouvez le voir ci-dessous, Microsoft Excel affiche le message d'erreur de référence circulaire. Cliquez sur "OK" pour continuer.

Référence circulaire dans Excel — avertissement de Microsoft Excel indiquant une ou plusieurs références circulaires, avec les boutons OK et Aide

Essayons maintenant une formule moins basique, une Fonction IF qui inclut aussi la cellule de la formule. Par exemple, "=IF(E1=2, "YES")".

Référence circulaire dans Excel — cellule E1 contenant une formule IF qui fait référence à E1 elle-même

Dès que vous appuyez sur "Entrée", vous obtenez le résultat "0" au lieu du message d'erreur. C'est tout de même un signe que quelque chose ne va pas dans la formule. Voyons maintenant comment trouver les références circulaires dans Excel pour les corriger.

Comment trouver les références circulaires dans Excel ?

Trouver une référence circulaire est plus simple que vous ne le pensez. Il suffit d'utiliser la fonctionnalité "Références circulaires" de l'onglet Formules. Elle vous aide à localiser toute référence circulaire dans votre feuille de calcul, pour ensuite la supprimer ou la corriger, si possible.

1. Allez dans Formules > Vérification des erreurs > Références circulaires. Les cellules contenant une référence circulaire apparaissent sous forme de liste à côté du menu.

Trouver une référence circulaire dans Excel — menu Vérification des erreurs de l'onglet Formules avec Références circulaires pointant vers $E$1

2. La référence circulaire s'affiche aussi en bas de la feuille active, avec la cellule où elle se trouve. Ici, "E1".

Trouver une référence circulaire dans Excel — barre d'état en bas de la feuille affichant Références circulaires : E1

Notez que vous pouvez avoir des références circulaires dans d'autres feuilles que la feuille active, par exemple lorsqu'une formule fait référence à une autre feuille. Dans ce cas, Excel n'affiche pas la cellule, seulement "Références circulaires".

Si la fonctionnalité "Références circulaires" ne fonctionne pas, vous devrez peut-être désactiver l'option "Calcul itératif". Bien qu'elle soit généralement désactivée par défaut, vous pouvez le vérifier dans Fichier > Options > Formules.

Référence circulaire dans Excel — page Formules des Options Excel avec Activer le calcul itératif décoché et Nb maximal d'itérations réglé sur 100

Dans "Mode de calcul", la case "Activer le calcul itératif" doit être décochée. Si vous activez cette fonctionnalité, Excel recalcule la formule autant de fois que nécessaire, ce qui nuira forcément au bon fonctionnement de votre classeur. Vous pouvez toutefois indiquer combien de fois Excel doit recalculer dans "Nb maximal d'itérations".

Maintenant que vous savez identifier une référence circulaire dans Excel, voyons comment supprimer les références circulaires de votre fichier Excel.

Comment supprimer les références circulaires dans Excel ?

Malheureusement, aucun bouton ni aucune fonctionnalité ne permet de supprimer toutes les références circulaires d'Excel en une seule fois. Si vos données sont volumineuses et complexes, vous ne trouverez pas toujours une référence circulaire dans Excel aussi facilement que dans l'exemple précédent. Pour cela, vous devez repérer les relations entre les formules et les cellules. Voici comment repérer ces relations pour corriger les références circulaires.

1. Allez dans Formules > Repérer les antécédents. Vous voyez ainsi quelle cellule fournit les données à la formule. Ci-dessous, la flèche bleue montre que la cellule F1, la cellule antécédente, fait partie de la formule de la cellule E1.

Corriger une référence circulaire dans Excel — flèche Repérer les antécédents entre les cellules E1 et F1 dans l'onglet Formules

2. Sélectionnez "Repérer les dépendants" pour afficher les cellules contenant la formule qui fait référence à la cellule sélectionnée. Ici, E1 dépend de F1.

Corriger une référence circulaire dans Excel — Repérer les dépendants sélectionné avec une flèche bleue reliant E1 et F1

3. Pour savoir exactement comment chaque cellule fait référence à une autre dans la formule, sélectionnez "Afficher les formules" dans l'onglet "Formules". Vous pouvez maintenant identifier les problèmes de formule.

Corriger une référence circulaire dans Excel — affichage Afficher les formules avec =(E1*2/F1) en colonne E et =100*E1 en colonne F

4. Comme vous pouvez le voir dans les formules, ces deux cellules dépendent entièrement l'une de l'autre, ce qui crée cette référence circulaire. Vous pouvez maintenant repérer les références de cellule à modifier dans la formule pour briser ce cycle. Le plus courant consiste à transformer au moins une des cellules dépendantes en valeur statique (si possible). Dans cet exemple, je peux remplacer ma référence E1 par la valeur statique "5" pour éviter une référence circulaire.

Corriger une référence circulaire dans Excel — formule de E1 remplacée par =(5*2/F1), qui renvoie 0,05 en colonne E et 200 en colonne F

5. Pour continuer à travailler normalement sur votre feuille de calcul, cliquez sur "Supprimer les flèches" dans le même onglet "Formules". Vous pouvez les supprimer toutes à la fois ou selon le type de repérage, comme ci-dessous.

Corriger une référence circulaire dans Excel — menu Supprimer les flèches avec Supprimer les flèches des antécédents et Supprimer les flèches des dépendants

En quelques clics, vous avez vu comment repérer les relations entre cellules dans une référence circulaire et comment la corriger en remplaçant une référence de cellule par une valeur statique.

A noter : Dans de nombreux cas, vous créez volontairement une référence circulaire dans votre classeur Excel. Il est alors préférable de limiter le nombre de références circulaires dans une même feuille afin de réduire les temps de chargement.

Conclusion

Si vous voulez éviter qu'Excel entre dans une boucle infinie et ralentisse tous les calculs, bien comprendre ce qu'est une référence circulaire et comment la corriger est aussi essentiel qu'utile.

Cet article vous a présenté le concept de "référence circulaire". Vous avez aussi vu comment trouver et supprimer les références circulaires dans Excel en quelques étapes. Envie d'en savoir plus sur les formules dans Excel ? Découvrez quelques-uns de nos derniers articles de blog :

Ces boucles sont plus difficiles à repérer lorsque vos formules récupèrent des valeurs d'autres feuilles et classeurs. Si vous copiez des données entre des fichiers Excel à la main, les automatisations IA de Sheetgo peuvent connecter ces fichiers et les mettre à jour automatiquement selon un calendrier.