Comprendre et corriger l'erreur #REF! dans Excel
L'erreur #REF! est le code qu'Excel affiche quand une formule pointe vers une cellule qui n'existe plus. Elle vient presque toujours de la suppression d'une ligne, d'une colonne ou d'une feuille utilisée par la formule. Excel réécrit alors celle-ci en remplaçant l'adresse disparue par #REF!, et l'adresse d'origine n'est conservée nulle part.
Que signifie l'erreur #REF! dans Excel
L'erreur #REF! signale qu'une formule cherche une adresse qui n'existe plus, et Excel l'inscrit noir sur blanc à l'intérieur de la formule. Une multiplication écrite =A5*2 devient =#REF!*2 dès que la cellule A5 disparaît, et le résultat cède la place au code d'erreur.
C'est ce détail qui la distingue des autres erreurs d'Excel. Le calcul n'a pas échoué, c'est l'adresse qui a été effacée, et rien dans le fichier ne garde la trace de ce qu'elle désignait. Le code reste donc inscrit dans la formule après un enregistrement et une réouverture, et aucune commande d'Excel ne le remplacera par la bonne adresse.
L'erreur se propage ensuite à tout ce qui dépend de la cellule touchée. Une formule aussi banale que =C1+1 bascule en #REF! si C1 est cassée, alors que sa propre référence reste parfaitement valide. Une seule suppression peut donc noircir une colonne entière de codes d'erreur.
#REF! vient d'apparaître, ton premier geste est Ctrl+Z : l'annulation rétablit la ligne supprimée et les formules avec elle, alors que plus rien ne retrouvera l'adresse une fois le fichier refermé.
Comment corriger l'erreur #REF! dans Excel
Corriger une erreur #REF! consiste à redonner une adresse valide à la formule, puisque Excel a effacé l'ancienne et ne peut pas la deviner. Annule tout de suite si la suppression est récente, sinon repère chaque formule cassée et réécris sa référence à la main.
- 1Commence par annuler la suppression avec Ctrl+Z si elle vient d'avoir lieu, c'est le seul moyen de récupérer les adresses d'origine sans les reconstituer toi-même.
- 2Pour lister les formules réellement cassées, ouvre la fenêtre Rechercher et remplacer avec Ctrl+F, tape `#REF!`, règle « Regarder dans » sur Formules puis clique sur le bouton Rechercher tout. L'option Valeurs remonterait en plus les cellules simplement contaminées, alors que Formules ne retient que celles à réparer.
- 3Clique sur chaque cellule remontée, puis dans la barre de formule, remplace le `#REF!` par la référence attendue et valide avec Entrée.
- 4Si la feuille référencée a été supprimée, la formule affiche quelque chose comme `=#REF!A1`. Recréer une feuille du même nom ne la répare pas, il faut réécrire la référence vers la nouvelle source.
Pourquoi l'erreur #REF! apparaît après avoir supprimé une ligne ou une colonne
Supprimer une ligne ne vide pas ses cellules, elle les retire de la feuille, et toute formule qui visait l'une d'elles se retrouve sans cible. Excel remplace alors son adresse par #REF!, sans faire la différence entre une suppression voulue et une fausse manœuvre.
La même suppression traite pourtant les plages tout autrement, et c'est là que ça devient sournois. Une formule =SOMME(A1:A10) posée sur dix montants ne casse pas quand tu retires la ligne 5, elle se réécrit toute seule en =SOMME(A1:A9) et rend 500 au lieu de 550. Aucun code d'erreur, aucun triangle vert, juste un total qui a maigri de la valeur disparue.
L'erreur visible est donc la moins dangereuse des deux. Un #REF! saute aux yeux et finit par être corrigé, alors qu'un total rétréci part dans un rapport sans que personne ne s'en aperçoive. Une colonne supprimée se comporte de la même façon, la référence directe casse et la plage se contente de rétrécir.
Pourquoi RECHERCHEV et INDEX renvoient l'erreur #REF!
Une suppression n'est pas la seule cause : une fonction qui va chercher en dehors de sa propre plage produit le même code. RECHERCHEV réclame un numéro de colonne, et demander la colonne 3 dans un tableau qui n'en compte que deux renvoie #REF! sur-le-champ.
INDEX se comporte pareil sur ses deux arguments de position. Demander la cinquième ligne d'une plage qui n'en compte que deux, ou sa quatrième colonne quand elle n'en a que deux, revient à pointer en dehors de la zone décrite. La formule est correctement écrite, c'est la coordonnée demandée qui n'existe nulle part.
INDIRECT complète la série en fabriquant une adresse à partir de texte. Dès que le texte assemblé ne forme pas une adresse valide, Excel n'a plus rien à pointer et bascule en #REF!. Le réflexe est proche dans les trois cas : vérifie que la coordonnée ou l'adresse demandée existe vraiment avant de chercher plus loin.
Quelle différence entre les erreurs #REF!, #VALEUR! et #N/A
Les trois codes signalent des problèmes de nature très différente, même s'ils prennent la même place dans la cellule. #REF! veut dire que l'adresse visée n'existe plus, donc que la formule a perdu sa cible. C'est un accident de structure du classeur, pas un problème de contenu.
#VALEUR! répond au cas inverse, la formule sait très bien où regarder mais trouve du texte là où elle attendait un nombre. #N/A vient des fonctions de recherche, qui ont parcouru toute la plage sans y trouver la valeur demandée. Dans ces deux cas les références sont intactes, seule la donnée pose problème.
La distinction change la réparation. Un #VALEUR! ou un #N/A se corrigent en nettoyant les données, alors qu'un #REF! oblige à reconstruire une référence que le fichier ne connaît plus. C'est aussi pourquoi un #N/A peut rester une information utile, là où un #REF! mérite toujours d'être réparé.
Comment éviter l'erreur #REF! quand tu supprimes des lignes
Le geste le plus sûr consiste à vider plutôt qu'à supprimer. Sélectionne les cellules concernées et appuie sur Suppr, le contenu part et la ligne reste en place, donc aucune formule ne perd son adresse. Ta feuille garde une ligne vide, ce qui coûte nettement moins cher qu'un total faux ou qu'une colonne de codes d'erreur.
Quand la ligne doit vraiment disparaître, désigne des colonnes plutôt que des adresses. Un total écrit =SOMME(Ventes[Montant]) sur un tableau Excel suit sa colonne quelle que soit sa taille. Une formule qui vise B3, elle, casse dès que la ligne 3 s'en va, tableau ou pas.
Le déplacement obéit enfin à une règle qui surprend souvent. Couper une cellule référencée avec Ctrl+X puis la coller ailleurs emmène la référence avec elle, et rien ne casse. Coller par-dessus une cellule référencée, en revanche, l'écrase et fait basculer en #REF! toutes les formules qui pointaient dessus.
#REF!. Excel garde en cache la dernière valeur reçue et continue de l'afficher, donc un classeur peut sembler juste tout en montrant des chiffres périmés.
Pour aller plus loin
Questions fréquentes sur l'erreur #REF!
Aucun bouton ne l'efface, parce que le #REF! n'est pas un problème d'affichage mais un morceau de ta formule. Tu dois rouvrir la formule et remettre à la place la référence qu'Excel a perdue. Si tu enveloppes le calcul dans SIERREUR, la cellule redevient propre à l'écran mais le résultat reste faux, ce qui est bien pire qu'une erreur visible. Garde SIERREUR pour les erreurs attendues et répare la référence pour celle-ci.
Non, la formule reste cassée même si tu recrées une feuille portant exactement le même nom. Au moment de la suppression, Excel a remplacé le nom de la feuille par #REF! à l'intérieur de la formule, qui ressemble alors à =#REF!A1. Il n'y a plus rien à raccrocher, donc tu dois réécrire toi-même la référence vers la nouvelle feuille. Seul un Ctrl+Z immédiat après la suppression rétablit la feuille et les formules d'un coup.
Oui, et c'est ce qui rend une seule suppression aussi coûteuse. Si une cellule affiche #REF! et qu'une autre l'utilise dans sa formule, la seconde affiche #REF! à son tour, même si sa propre référence est parfaitement correcte. Une colonne entière peut ainsi passer en erreur à cause d'une seule cellule cassée. C'est pourquoi il faut remonter à la cellule d'origine, une référence réparée éteint souvent toute la cascade.
Du mot à la pratique
Tu sais maintenant ce que le mot veut dire. Reste à s’en servir : la formule, le fichier, et le cours qui remet tout dans l’ordre.
Tout le lexiqueDes modèles Excel prêts à télécharger
Toutes les formules Excel, expliquées
Tout le cours Excel en PDF
