Ceux qui créent des tableaux remplis de formules, faisant appel à d'autres formules dans d'autres cellules, ont forcément rencontré un jour le problème de la référence circulaire.

Qu'est-ce qu'une Référence circulaire en Excel

La référence circulaire est une erreur qui apparaît quand une formule fait référence à elle-même, directement (ex : la cellule A1 a pour formule la somme de A1 à B3) ou indirectement par le biais d'une autre formule y faisant référence (ex : la cellule A1 a pour formule la somme de A2 à B3 alors que la cellule B3 est égale à A1 divisée par 2).

La référence circulaire entraîne normalement un calcul itératif qui peut se répéter à l'infini et ainsi bloquer la résolution de la formule. Par défaut, les calculs itératifs sont désactivés dans Excel pour éviter ce genre de soucis et le blocage d'un fichier.

Lors de l'apparition d'une référence circulaire, Excel vous envoie un message d'erreur:

Après le clic sur OK, il affiche une flèche bleue à double tête pour vous indiquer quelles sont les cellules en cause, afin de pouvoir corriger le problème (sous 2003, Excel ouvre la barre d'outils de la référence circulaire).

Référence circulaire

Comment corriger une référence circulaire en Excel?

Il y a deux solutions à ce problème:

1. Supprimer ou corriger la formule de la cellule qui pose problème. C'est le plus simple et le plus radical. Enfin, si vous avez un petit tableau avec un seul onglet, la flèche bleue vous guidera rapidement. Si la cellule se trouve sur un autre onglet, parmi une table de 2000 lignes, autant se faire aider par le logiciel.

   Pour cela, rendez-vous dans l'onglet "Formules puis dans le groupe d'Audit de formules, vérifiez les erreurs :

   

   Verif circulaire

   Vous récupérez ainsi rapidement l'info de la cellule concernée.

   Une autre astuce est aussi de regarder la barre d'état qui vous indique aussi les cellules concernées.

    

   

2. Autoriser les calculs itératifs dans les options d'Excel. Dans la partie Formules des Options, vous pouvez activer les calculs itératifs (rappel: ils sont désactivés par défaut) et définir le nombre maximal d'itérations. A la fin de ces itérations, le calcul sera figé.
   

   calculs_itératifs

   Dans la zone "Écart maximal", vous pouvez définir quel sera l'écart max dans le calcul. Bien sûr, plus cet écart est petit, plus le calcul sera long.

    

Mon conseil perso est le suivant: limitez-vous plutôt à la première solution. Jouer avec les itérations peut parfois être problématique ou bloquer Excel pendant de longues minutes qui lui seront nécessaires pour le calcul. Vous êtes maintenant incollables sur les références circulaires alors plus de raison de laisser des pièges à utilisateurs dans vos fichiers.

Bons calculs !