Erreur #REF! dans Excel : d'où elle vient et comment la réparer
#REF! est l’erreur la plus brutale d’Excel, et la seule qui soit irréversible. Les autres décrivent un calcul impossible : #DIV/0! divise par zéro, #N/A ne trouve pas la valeur. #REF! dit autre chose : la cellule que je devais lire n’existe plus, et je ne sais pas laquelle c’était.
L’information est perdue. Excel ne peut pas la deviner à votre place. D’où l’importance de comprendre ce qui la provoque.
Ce qui la déclenche vraiment
La suppression d’une ligne, d’une colonne ou d’un onglet. C’est la cause n°1. Vous supprimez la colonne D parce qu’elle semblait inutile ; toutes les formules qui la lisaient affichent #REF!. Le piège : elles peuvent se trouver dans un autre onglet, et vous ne les voyez pas au moment de la suppression.
Un couper-coller qui écrase une zone référencée. Un Ctrl+X puis Ctrl+V par-dessus des cellules dont dépendaient des formules produit le même effet qu’une suppression.
Un RECHERCHEV dont l’index dépasse le tableau. RECHERCHEV(A2;Tableau;8;FAUX) sur un tableau de 6 colonnes renvoie #REF!. Cela arrive typiquement après avoir supprimé une colonne à l’intérieur de la plage : le tableau rétrécit, l’index reste écrit en dur.
Un INDEX ou un DECALER hors limites. INDEX(plage;25) sur une plage de 20 lignes : même résultat.
La suppression d’un classeur source. Les liaisons vers un fichier externe déplacé, renommé ou supprimé se transforment en #REF! dès la mise à jour.
Une macro qui supprime des lignes. Un Rows.Delete dans une boucle est la version industrielle du problème : la macro tourne, et cent formules cassent d’un coup.
Les retrouver toutes en trente secondes
Inutile de parcourir les onglets à l’œil. Ctrl+F, puis Options :
- Rechercher :
#REF! - Dans : Classeur
- Regarder dans : Formules
Cliquez sur Rechercher tout. Vous obtenez la liste complète, onglet par onglet, cellule par cellule. C’est la seule façon fiable de mesurer l’étendue des dégâts — car #REF! se propage : une cellule cassée contamine tout ce qui en dépend, et vous pouvez avoir trois cent formules en erreur pour une seule suppression.
Le raccourci F5 → Cellules → Formules → Erreurs sélectionne les erreurs de l’onglet actif, ce qui est pratique pour un nettoyage feuille par feuille.
Réparer
Dans la minute qui suit : Ctrl+Z. C’est la seule réparation réellement automatique. Si vous venez de supprimer quelque chose et que les #REF! apparaissent, annulez immédiatement. Ne sauvegardez surtout pas avant.
Après coup, il faut reconstruire la référence à la main. Ouvrez une version antérieure du fichier (l’historique OneDrive/SharePoint, une sauvegarde, ou une pièce jointe d’e-mail) et lisez la formule d’origine : elle vous dit ce qui a été supprimé.
Un point important : ne remplacez jamais les #REF! par des zéros pour « nettoyer » l’affichage. Vous ne supprimez pas le problème, vous le rendez invisible — et le total faux devient plausible. Un fichier qui affiche une erreur est infiniment moins dangereux qu’un fichier qui affiche un chiffre erroné.
Empêcher que ça recommence
Les tableaux structurés (Ctrl+L) sont la meilleure protection. Une formule qui référence Ventes[Montant] continue de fonctionner quand on ajoute ou déplace des colonnes, parce qu’elle pointe sur un nom, pas sur une position.
Remplacez RECHERCHEV par RECHERCHEX. RECHERCHEX désigne directement la colonne de résultat, sans numéro d’index à compter. La cause la plus fréquente de #REF! disparaît. (Attention : RECHERCHEX demande Excel 2021, 2024 ou Microsoft 365.)
Masquez au lieu de supprimer. Une colonne masquée ne casse rien. Si elle est réellement inutile, supprimez-la — mais après avoir vérifié avec Ctrl+F que personne ne la lit.
Protégez les feuilles de calcul. Verrouiller les cellules de formules empêche les suppressions accidentelles par des utilisateurs qui ne connaissent pas la mécanique du fichier. C’est ce que je fais systématiquement sur les fichiers destinés à plusieurs personnes.
Quand le #REF! est un symptôme
Un fichier qui casse chaque fois que quelqu’un y touche n’a pas un problème de formule : il a un problème de conception. Références en dur, données mélangées aux calculs, aucune protection, aucune séparation entre la saisie et l’affichage — dans ce cas, réparer les #REF! ne fait que gagner du temps avant le prochain.
C’est souvent le même fichier qui est aussi devenu lent et instable : les deux symptômes ont la même cause.
Votre fichier affiche des #REF! et vous ne savez pas lesquels sont graves ? Envoyez-le-moi en diagnostic : je vous dis ce qui est cassé, ce qui est réparable, et ce que ça coûte — à prix fixe.