Corriger une référence circulaire dans Excel : comment ça marche
Une référence circulaire est une formule qui renvoie à sa propre cellule, comme =SOMME(B2:B6) en B6. Excel vous avertit et ne peut pas calculer le résultat. Retirez cette cellule de la formule.
Essayez gratuitement les premiers exercicesDans la leçon Comprendre les messages d'erreur
Qu'est-ce qu'une référence circulaire ?
Une référence circulaire est une formule qui fait référence à sa propre cellule, directement ou par un détour. Si vous tapez =SOMME(B2:B6) en B6, Excel doit compter B6 pour calculer B6. C'est un cercle sans fin : le résultat dépend de lui-même.
Excel affiche alors un avertissement et indique Références circulaires dans la barre d'état en bas, avec la cellule. La cellule elle-même affiche en général 0 ou le dernier résultat, et ce n'est pas juste. Ce n'est pas un message d'erreur avec un dièse, comme #REF!, mais c'est une erreur qu'il faut toujours corriger.
Quand une référence circulaire apparaît-elle ?
- Vous placez un total sous une colonne et incluez la cellule du total dans la plage : =SOMME(B2:B6) en B6.
- En créant une formule, vous faites glisser la plage juste une ligne trop loin.
- Deux cellules font référence l'une à l'autre : A1 contient =B1*2 et B1 contient =A1+5.
- Vous placez une part du total dans la même colonne que les nombres que le total additionne.
Corriger une référence circulaire, étape par étape
B6 contient =SOMME(B2:B5). La plage s'arrête au-dessus de la cellule du total, en B5. Ainsi, B6 ne se compte plus lui-même. Modifiez les nombres ou la formule et regardez ce qui se passe.
Voici comment procéder :
- Cliquez sur OK dans l'avertissement et regardez la barre d'état en bas : elle indique Références circulaires avec la cellule, comme B6.
- Ou choisissez Formules > Vérification des erreurs > Références circulaires : Excel montre les cellules du cercle, et vous y allez d'un clic.
- Cliquez sur la cellule et lisez la formule. Sa propre cellule est-elle dans la plage, comme B6 dans =SOMME(B2:B6) ?
- Faites s'arrêter la plage au-dessus de la cellule du total et appuyez sur Entrée.
=SOMME(B2:B5)
Comment éviter un cercle
Le total au-dessus de la liste
B1 contient =SOMME(B3:B5). Si le total est en haut, faites commencer la plage sous la cellule du total. =SOMME(B1:B5) compterait B1 lui-même.
Une part dans une colonne à part
C2 contient =B2/$B$5. La part se trouve dans une colonne à part. Si vous la mettiez en colonne B, dans la plage du total, le total dépendrait de la part et la part du total : un cercle.
Là où ça coince souvent
- Une référence circulaire peut aussi faire un détour : A1 renvoie à B1, et B1 renvoie à A1.
- Ne fermez pas simplement l'avertissement : la cellule affiche alors 0 ou un ancien résultat, qui est faux.
Entraînez-vous dans la leçon Comprendre les messages d'erreur. Les premiers exercices sont gratuits.
Questions fréquentes
Une référence circulaire est-elle parfois voulue ?
Très rarement, dans des modèles financiers complexes. Il faut alors activer le calcul itératif dans les options d'Excel. Dans une feuille ordinaire, c'est toujours une erreur.