Erreur #REF! dans Excel : pourquoi elle apparaît et comment la réparer

1 4 minutes de lecture

Mains utilisant une calculatrice devant un clavier d'ordinateur

En bref :

#REF! signifie que la formule pointe vers une cellule, une ligne, une colonne ou une feuille qui n’existe plus ou qui est inaccessible.
Si l’erreur vient d’apparaître, faites Ctrl + Z pour annuler la suppression responsable.
Sinon, retrouvez les formules touchées avec Ctrl + F (recherche de « #REF! » dans les formules) et remplacez la référence cassée.

Vous avez supprimé une colonne, et plusieurs cellules affichent soudain #REF! ? C’est l’une des erreurs Excel les plus courantes, et heureusement l’une des plus faciles à comprendre. Dans le message « référence non valide », Excel vous dit simplement qu’il ne trouve plus ce que la formule lui demande. Voyons les causes, puis la méthode pour réparer.

Les causes de #REF! en un tableau

Cause	Exemple	Correction
Ligne, colonne ou feuille supprimée	`=B2+C2` après suppression de la colonne C devient `=B2+#REF!`	Annuler avec `Ctrl` + `Z` ou réécrire la référence
Copie d’une formule hors du tableur	Formule relative copiée plus haut que la ligne 1	Utiliser des références absolues (`$A$1`) ou recopier autrement
RECHERCHEV avec un indice trop grand	`=RECHERCHEV(A2;A:C;4;FAUX)` sur 3 colonnes	Corriger le numéro de colonne
INDEX hors de la plage	`=INDEX(A1:A10;12)`	Ajuster le numéro de ligne ou de colonne
INDIRECT vers un classeur fermé	Référence à un autre fichier non ouvert	Ouvrir le classeur ou changer de méthode
Liaison cassée	Classeur source déplacé, renommé ou supprimé	Données > Modifier les liens

Retrouver toutes les cellules en #REF!

Dans un grand classeur, inutile de parcourir les cellules à la main :

Appuyez sur Ctrl + F, puis cliquez sur Options.
Saisissez #REF! dans « Rechercher » et choisissez Formules dans « Regarder dans ». Dans « Dans », sélectionnez Classeur pour fouiller toutes les feuilles.
Cliquez sur Rechercher tout : la liste donne chaque cellule concernée.
Pour n’afficher que les erreurs d’une feuille, utilisez F5 > Cellules… > Formules, en ne cochant que Erreurs.

Pensez aussi à vérifier le Gestionnaire de noms (onglet Formules) : un nom défini qui pointe vers une plage supprimée affiche lui aussi #REF! et fait échouer toutes les formules qui l’utilisent.

Après la suppression d’une ligne ou d’une colonne

C’est le scénario classique. Excel ne peut pas deviner ce que vous vouliez : il remplace la référence disparue par #REF!. Si vous avez fermé le fichier sans enregistrer depuis, rouvrez la dernière version enregistrée ou l’historique des versions (Fichier > Informations > Historique des versions, si le classeur est sur OneDrive).

Si l’annulation n’est plus possible, éditez la formule : sélectionnez la cellule, appuyez sur F2, supprimez la partie #REF! et sélectionnez à la souris la bonne cellule. Faites ensuite glisser la poignée de recopie pour corriger les cellules voisines. Pour modifier plusieurs formules identiques, Ctrl + H permet de remplacer #REF! par la bonne référence.

Avec RECHERCHEV et INDEX

Avec RECHERCHEV, le troisième argument (le numéro de colonne) est compté à partir de la première colonne de la plage. Si votre plage fait trois colonnes et que vous demandez la quatrième, Excel renvoie #REF!. Même logique pour INDEX quand le numéro de ligne ou de colonne dépasse la plage. Comptez les colonnes de la plage, ou passez à RECHERCHEX, qui désigne directement la colonne de résultat plutôt qu’un numéro et résiste donc mieux aux insertions de colonnes.

Si la valeur cherchée n’existe pas, l’erreur est un #N/A, pas un #REF! : voir l’erreur #N/A dans Excel.

Liaisons vers d’autres classeurs

Une formule comme ='C:\Budget\[2025.xlsx]Feuil1'!B4 casse si le fichier source change de nom ou d’emplacement. Ouvrez Données > Modifier les liens, sélectionnez la source cassée et utilisez Changer la source pour pointer vers le bon fichier. Pour INDIRECT, la source doit rester ouverte : sinon, remplacez-la par une référence directe.

Prévenir : tableaux structurés et noms

Transformez vos données en tableau (Ctrl + T) : les formules utilisent des noms de colonnes (Tableau1[Montant]) qui s’adaptent aux ajouts de lignes.
Définissez des noms pour les plages importantes, et gardez les références vers d’autres feuilles dans un onglet de paramètres.
Avant de supprimer une colonne, utilisez Formules > Repérer les dépendants pour voir ce qui s’appuie sur elle.
Au lieu de supprimer, masquez la colonne : les formules continuent de fonctionner.

SIERREUR : masquer n’est pas corriger

=SIERREUR(formule;"") fait disparaître le message, mais pas le problème : le résultat reste faux ou vide. Réparez d’abord la référence, puis n’utilisez SIERREUR que pour les erreurs attendues, par exemple une valeur absente d’une recherche. Pour #DIV/0!, voir comment l’éviter ou le masquer.

Cas pratique : un budget cassé

Sophie gère un budget avec une colonne « Janvier » à « Décembre » et une colonne « Total ». En supprimant la colonne « Avril » inutilisée, elle voit le total de la synthèse passer en #REF!, car la formule additionnait directement Avril!C4. Elle annule avec Ctrl + Z, masque la colonne à la place, puis remplace son total par une formule SOMME sur une plage continue, qui s’ajuste automatiquement.

À lire aussi : les erreurs Excel et leur signification, l’erreur #VALEUR! et la cellule qui affiche #####.