#REF! signifie qu'une formule fait référence à une cellule qui n'est pas là. La cause habituelle est une ligne, une colonne ou une feuille supprimée : quand la colonne C est supprimée, Excel réécrit =B2*C2 en =B2*#REF!, et le résultat est #REF! à partir de ce moment. Appuyez sur Ctrl+Z (Cmd+Z sur un Mac) juste après la suppression pour récupérer la colonne et la formule. Le nom de l'erreur est le même dans un Excel français et dans un Excel anglais ; le tableau affiche les formules sous leur forme anglaise, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =RECHERCHEV(E2;A2:C6;3;FAUX).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! La formule fait référence à une cellule qui n’existe pas.Dans Excel en français : =B2*#REF!La colonne des quantités a été supprimée puis retapée, mais la formule indique toujours #REF! : Excel ne répare jamais une référence une fois qu'elle a disparu. Cliquez sur D2, remplacez #REF! par C2 et appuyez sur Entrée. Toute la colonne suit, et D2 affiche 12.
Comment #REF! arrive dans une formule
Excel écrit #REF! dans une formule chaque fois qu'une cellule utilisée par la formule disparaît :
| Vous avez fait ceci | =B2*C2 en D2 devient |
|---|---|
| Supprimé la colonne C | =B2*#REF! |
| Supprimé la ligne 2 | la formule est supprimée avec sa ligne ; les formules d'autres lignes qui pointaient vers la ligne 2 reçoivent #REF! |
| Supprimé la feuille à laquelle une formule fait référence | =#REF!B2*2 (pour une formule comme =Prices!B2*2) |
| Coupé une cellule et l'avez collée sur une cellule utilisée par la formule | #REF! à la place de la référence écrasée |
Supprimer des cellules à l'intérieur d'une plage ne pose pas de problème : =SOMME(B2:D2) devient =SOMME(B2:C2) quand la colonne C est supprimée. Supprimer la première ou la dernière cellule d'une plage ne fait que la réduire. =SOMME(B2:D2) est donc plus sûre que =B2+C2+D2, qui devient =B2+#REF!+C2.
Pourquoi RECHERCHEV renvoie #REF!
Le troisième argument de RECHERCHEV compte les colonnes à l'intérieur de la plage du tableau. S'il dépasse le nombre de colonnes de la plage, le résultat est #REF!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! La formule fait référence à une cellule qui n’existe pas.Dans Excel en français : =RECHERCHEV(E2;A2:C6;4;FAUX)A2:C6 a trois colonnes, donc 4 n'existe pas. Remplacez le 4 par 3 et F2 affiche 25. Cela arrive surtout après la suppression d'une colonne du tableau de recherche : la plage se réduit, le numéro de colonne écrit en dur, non. RECHERCHEX ou INDEX avec EQUIV évitent ce piège parce qu'elles désignent directement la colonne de retour, comme =RECHERCHEX(E2;A2:A6;C2:C6). Voir RECHERCHEV pour ses autres arguments.
#REF! avec INDEX et DECALER
INDEX renvoie #REF! quand le numéro de ligne ou de colonne sort de sa plage, et DECALER quand il remonte au-dessus de la ligne 1 ou recule avant la colonne A.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! La formule fait référence à une cellule qui n’existe pas.Dans Excel en français : =INDEX(A2:A6;6)A2:A6 contient cinq notes, donc INDEX(A2:A6;6) donne #REF! alors que INDEX(A2:A6;3) renvoie 95. La ligne 0 n'existe pas, donc DECALER(A2;-2;0) donne #REF!, et DECALER(A2;4;0) tombe sur A6 : 81. Quand la position vient d'une autre formule (un EQUIV, un NB), vérifiez d'abord cette formule. Plus de détails sur la page INDEX.
INDIRECT donne aussi #REF! quand son texte n'est pas une adresse valide (=INDIRECT("ZZZ1"), car la dernière colonne est XFD) ou pointe vers un classeur fermé.
#REF! en copiant une formule
Une référence relative se déplace avec la formule. Copiez-la assez loin vers le haut ou sur le côté et la référence sort de la feuille :
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
La même chose arrive quand une formule copiée dans une autre feuille ou un autre classeur pointe vers des cellules qui n'y existent pas. Bloquez avec $ les cellules qui ne doivent pas bouger (=$B$2*2), ou copiez le texte de la formule depuis la barre de formule plutôt que la cellule. Les références absolues expliquent le $.
Trouver et supprimer chaque #REF! d'un classeur
- Appuyez sur Ctrl+F (Cmd+F sur un Mac), tapez
#REF!, ouvrez Options, réglez Regarder dans sur Formules et cliquez sur Rechercher tout. La liste montre chaque formule qui contient une référence cassée. - Pour en corriger beaucoup d'un coup, utilisez Ctrl+H (Contrôle+H sur un Mac) : recherchez
#REF!et remplacez-le par la bonne référence, mais seulement si chaque occurrence doit recevoir la même cellule. - Vérifiez Formules > Gestionnaire de noms : un nom dont la colonne Fait référence à affiche
#REF!casse chaque formule qui l'utilise. - Si les données supprimées sont perdues et que la formule n'est plus utile, sélectionnez les cellules et remplacez les formules par leurs valeurs (Copier, puis Accueil > Coller > Valeurs). Les valeurs d'erreur restent des erreurs, donc supprimez ensuite ces cellules.
Corriger une recherche qui renvoie #REF!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
À vous : =VLOOKUP(E2,A2:C6,4,FALSE) a renvoyé #REF!. Écrivez en F2 une recherche qui fonctionne et renvoie le stock du produit de E2.
Toute recherche qui renvoie 60 ici et suit les données est acceptée : RECHERCHEV avec la colonne 3, =RECHERCHEX(E2;A2:A6;C2:C6) ou =INDEX(C2:C6;EQUIV(E2;A2:A6;0)).
Questions fréquentes
Que signifie #REF! dans Excel ?
La formule pointe vers une cellule qui n'existe pas. Le plus souvent, une ligne, une colonne ou une feuille utilisée par la formule a été supprimée, et Excel a remplacé la référence par #REF!, donc =B2*C2 est devenue =B2*#REF!. RECHERCHEV et INDEX renvoient aussi #REF! quand le numéro de colonne ou de ligne dépasse la plage.
Comment corriger #REF! après avoir supprimé une colonne ?
Appuyez tout de suite sur Ctrl+Z (Cmd+Z sur un Mac) pour annuler la suppression. S'il est trop tard, cliquez sur la formule et remplacez #REF! par la cellule qu'elle doit utiliser, puis recopiez de nouveau la formule vers le bas.
Pourquoi RECHERCHEV renvoie-t-elle #REF! ?
Le numéro de colonne est supérieur au nombre de colonnes de la plage du tableau. =RECHERCHEV(E2;A2:C6;4;FAUX) demande la 4e colonne d'une plage de 3 colonnes. Utilisez 3, ou élargissez la plage à A2:D6.
Comment trouver toutes les erreurs #REF! d'un classeur ?
Appuyez sur Ctrl+F (Cmd+F sur un Mac), cherchez #REF!, réglez Regarder dans sur Formules et cliquez sur Rechercher tout. Excel liste chaque formule qui contient une référence cassée. Vérifiez aussi Formules > Gestionnaire de noms : des noms peuvent pointer vers #REF! après une suppression.
Comment éviter #REF! en supprimant des lignes ou des colonnes ?
Faites référence à des plages plutôt qu'à des cellules isolées. =SOMME(B2:D2) se réduit à =SOMME(B2:C2) quand la colonne C ou D est supprimée, alors que =B2+C2+D2 devient =B2+#REF!+C2. Les recherches qui désignent leur colonne de retour, comme =RECHERCHEX(E2;A2:A6;C2:C6), résistent à l'insertion de colonnes et à la suppression de colonnes qu'elles n'utilisent pas.
