Peut-on retrouver ce que visait une formule en #REF! ?

Non, pas depuis la formule : la référence a été remplacée. Seule une ancienne version du fichier la contient encore ; Comparer deux fichiers Excel aide à retrouver la différence.

Comment éviter les #REF! ?

Vider le contenu des cellules plutôt que supprimer lignes et colonnes utilisées, préférer RECHERCHEX ou INDEX/EQUIV aux numéros de colonne, et travailler dans des tableaux structurés, dont les références suivent les changements.

L'erreur #REF!

Une formule visait une cellule qui a disparu : une ligne supprimée, une colonne coupée, un onglet effacé. Excel remplace la référence par #REF!, et l'erreur se propage à tout ce qui en dépend.

Ce qu'elle veut dire

#REF! signifie que la référence n'est plus valide. Contrairement aux autres erreurs, elle est écrite dans la formule elle-même : =A1+#REF!. L'information de départ est perdue.

Toutes les formules qui utilisent le résultat affichent #REF! à leur tour : une seule suppression peut rougir un tableau entier.

D'où elle vient

Une ligne, une colonne ou un onglet supprimés alors qu'une formule les utilisait.
Un couper-coller par-dessus des cellules visées par des formules.
Une formule recopiée vers le haut ou la gauche, dont les références sortent de la feuille (au-dessus de la ligne 1).
RECHERCHEV avec un numéro de colonne trop grand : =RECHERCHEV(A2;B:D;5;FAUX) demande la 5e colonne d'une plage qui en a 3.
INDEX ou DECALER qui visent au-delà de la plage.
Un nom défini dont la cible a été supprimée : le nom vaut #REF! et toutes les formules qui l'emploient aussi.

La corriger

Tout de suite après la suppression : Ctrl+Z, puis supprimer autrement (vider le contenu plutôt que la ligne).
Plus tard : réécrire la référence dans la formule ; Excel ne peut pas deviner ce qu'elle visait.
Pour RECHERCHEV : vérifier le numéro de colonne, ou passer à RECHERCHEX, qui prend la colonne à rendre au lieu d'un numéro.
Pour les noms cassés : Formules, Gestionnaire de noms, et supprimer ceux qui valent #REF!.

Les outils qui aident

Audit des formules : chaque #REF! du classeur, onglet et cellule, avec sa formule.
Bilan de santé : les noms définis cassés, retirés en un clic.
Supprimer les liens externes : quand la référence cassée vise un autre classeur.

Questions fréquentes

Peut-on retrouver ce que visait une formule en #REF! ?: Non, pas depuis la formule : la référence a été remplacée. Seule une ancienne version du fichier la contient encore ; Comparer deux fichiers Excel aide à retrouver la différence.
Comment éviter les #REF! ?: Vider le contenu des cellules plutôt que supprimer lignes et colonnes utilisées, préférer RECHERCHEX ou INDEX/EQUIV aux numéros de colonne, et travailler dans des tableaux structurés, dont les références suivent les changements.