Erreur #REF!
L'erreur #REF! apparaît quand une formule contient une référence de cellule invalide, généralement après la suppression d'une ligne, d'une colonne ou d'une feuille pointée par cette formule. Pour la corriger, repère la référence invalide dans la formule et remplace-la par la bonne adresse.
À quoi sert un erreur #ref! dans Excel ?
L'erreur #REF! est le signal d'alarme qu'Excel t'envoie quand une formule pointe vers une cellule qui n'existe plus ou n'existe plus là où elle était. C'est une rupture de lien : la formule cherche une donnée, ne la trouve pas à l'adresse indiquée, et affiche #REF! pour te dire que quelque chose a bougé.
Tu vas rencontrer cette erreur principalement après avoir supprimé une ligne, une colonne ou une feuille référencée dans une formule, ou après avoir déplacé des données en cassant une référence. C'est l'une des erreurs les plus visibles parce qu'elle se propage : une cellule en #REF! contamine toutes les formules qui en dépendent.
Erreur #REF! : exemples concrets
Exemple 1 : Chef de projet : supprimer une ligne de budget et casser toutes les formules
Tu es chef de projet dans une agence de communication. Tu gères un budget de campagne dans Excel avec une ligne par poste de dépense (création, médias, événementiel...) et une ligne de total en bas qui fait la somme de tout.
Ton client annule un poste entier. Tu sélectionnes la ligne concernée et tu la supprimes. Résultat : ta formule SOMME en bas affiche #REF! au lieu du total. Excel ne peut plus trouver la cellule qu'il cherchait, parce qu'elle a disparu avec la ligne supprimée.
Si tu avais utilisé une plage nommée ou une référence au tableau structuré, Excel aurait automatiquement ajusté la formule à la suppression. À défaut, la solution immédiate est de corriger la formule à la main en sélectionnant la bonne plage, ou d'utiliser Ctrl+Z pour annuler et choisir une autre approche (vider la ligne plutôt que la supprimer, par exemple).
Exemple 2 : Comptable : déplacer une feuille et perdre les liens entre onglets
Tu es comptable dans une entreprise de services. Tu as un classeur avec un onglet par mois et un onglet récapitulatif annuel qui tire ses données de chaque mois via des formules comme ='Janvier'!B5.
Ton collègue réorganise le classeur, renomme l'onglet "Janvier" en "Janvier 2025" et supprime l'ancien. Tous les liens de ton onglet récapitulatif partent en #REF! parce que la feuille "Janvier" n'existe plus sous ce nom.
Pour diagnostiquer rapidement quelles cellules sont en erreur, utilise Ctrl+G (Atteindre) > Cellules avec des erreurs pour les sélectionner toutes en une fois. Tu peux ensuite corriger les formules en les ajustant au nouveau nom d'onglet, ou utiliser la fonction INDIRECT() si tu veux rendre tes formules indépendantes du nom exact des feuilles.
- #1 Ctrl+G (Atteindre) puis Cellules > Formules > Erreurs sélectionne toutes les cellules en erreur dans la feuille active. Pratique pour repérer rapidement toutes les occurrences de #REF! d'un coup.
- #2 Pour éviter le #REF! lors de suppressions, préfère vider le contenu d'une ligne ou colonne (touche Suppr) plutôt que de la supprimer entièrement. Les formules qui pointent dessus afficheront 0 ou vide, mais pas #REF!.
- #3 L'outil Audit de formules (onglet Formules > Repérer les dépendants) te montre visuellement quelles cellules dépendent de la cellule sélectionnée. Tu peux ainsi anticiper l'impact d'une suppression avant de la faire.
Les erreurs courantes avec le erreur #ref!
✕ Supprimer une ligne ou colonne référencée dans une formule
C'est la cause la plus courante de #REF!. Quand une formule fait référence à la cellule B5 et que tu supprimes la ligne 5, la cellule B5 disparaît physiquement. Excel ne peut pas deviner quelle autre cellule tu voulais pointer et affiche #REF! à la place de la référence invalide.
Le problème est particulièrement pernicieux quand la suppression est faite sur un autre onglet. Tu supprimes une ligne dans l'onglet "Données", et c'est dans l'onglet "Rapport" que #REF! apparaît. Le lien entre les deux n'est pas immédiatement visible.
Solution : Avant de supprimer une ligne ou colonne, utilise Formules > Repérer les dépendants pour voir si des formules pointent dessus. Si c'est le cas, mets à jour les formules avant de supprimer, ou vide le contenu plutôt que de supprimer la ligne entière.
✕ Copier une formule avec une référence relative qui sort de la feuille
Quand tu copies une formule vers le haut ou la gauche, les références relatives se décalent dans la même direction. Si ta formule est en B2 et fait référence à B1, en la copiant en B1 elle essaierait de pointer sur B0, qui n'existe pas. Excel affiche alors #REF!.
C'est moins fréquent que la suppression, mais ça arrive régulièrement quand on copie des formules vers des plages proches des bords du tableau.
Solution : Vérifie les références dans ta formule avant de la copier. Si une référence doit rester fixe (par exemple un en-tête ou une valeur de référence en haut), utilise une référence absolue ($B$1) pour qu'elle ne se décale pas lors de la copie.
Questions fréquentes sur le erreur #ref!
Clique sur la cellule qui affiche #REF! et regarde la formule dans la barre de formule. Tu verras apparaître le mot #REF! à l'intérieur de la formule, à l'endroit où la référence est cassée. Remplace ce #REF! par la bonne référence de cellule. Si l'erreur vient d'une feuille supprimée, tu devras soit recréer la feuille, soit réécrire la formule pour pointer vers la nouvelle source de données.
Quand tu supprimes une ligne, toutes les formules qui pointaient vers des cellules de cette ligne perdent leur cible. Excel ne peut pas deviner vers quelle autre cellule rediriger la formule et affiche #REF! pour signaler que la référence n'est plus valide. Pour éviter ça, préfère vider le contenu des cellules (touche Suppr) plutôt que de supprimer la ligne, ou mets à jour les formules avant la suppression.
Oui, c'est l'un des aspects les plus problématiques de cette erreur. Si une cellule A affiche #REF! et qu'une autre cellule B utilise A dans sa formule, B affichera aussi #REF! même si sa propre référence est correcte. Une seule erreur à la source peut contaminer tout un tableau. C'est pourquoi il vaut mieux corriger l'erreur à sa source plutôt que de l'encapsuler dans SIERREUR() sans comprendre la cause.
Termes liés
Envie d'aller plus loin ?
Explore les 293 termes de notre lexique ou découvre nos formules Excel.