Comment éviter les formules incorrectes

Comment éviter les formules incorrectes

Si Excel ne peut pas résoudre une formule que vous tentez de créer, vous pouvez recevoir un message d’erreur comme celui-ci :

Image de la boîte de dialogue « Problème avec cette formule » d’Excel

Malheureusement, cela signifie qu’Excel ne peut pas comprendre ce que vous essayez de faire. En pareil cas, il est recommandé de recommencer depuis le début.

Commencez par cliquer sur OK ou appuyez sur Échap pour fermer le message d’erreur.

Vous revenez ainsi à la cellule contenant la formule incorrecte (en mode d’édition). Excel met en évidence l’emplacement où il rencontre un problème. Si vous ne savez pas quoi faire et souhaitez recommencer depuis le début, vous pouvez appuyer de nouveau sur Échap ou cliquer sur le bouton Annuler dans la barre de formule pour quitter le mode d’édition.

Image du bouton Annuler dans la barre de formule

Si vous ne savez pas quoi faire à ce stade ou vous ignorez le type d’aide dont vous avez besoin, vous pouvez rechercher des questions semblables dans le forum de la Communauté Excel ou publier votre question.

Lien vers le Forum de la Communauté Excel

Pour avancer, vous pouvez consulter la liste de contrôle suivante qui décrit les étapes de dépannage permettant d’identifier les erreurs dans vos formules.

Excel lève diverses erreurs commençant par le signe dièse (#), telles que #VALEUR!, #REF!, #NOMBRE!, #N/A, #DIV/0!, #NOM? et #NUL!, pour indiquer qu’un élément de votre formule ne fonctionne pas correctement. Par exemple, l’erreur #VALEUR! résulte d’une mise en forme incorrecte ou de la présence de types de données non pris en charge dans les arguments. Vous pouvez également rencontrer l’erreur #REF! si une formule fait référence à des cellules supprimées ou remplacées par d’autres données. Les instructions de résolution des problèmes diffèrent pour chaque erreur.

Remarque : #### n’indique pas une erreur liée à une formule. Cela signifie simplement que la colonne n’est pas suffisamment large pour afficher l’ensemble du contenu de la cellule. Faites simplement glisser la colonne pour l’agrandir, ou accédez à Accueil > Format > Ajuster la largeur de colonne.

Image de Accueil > Format > Ajustement automatique de largeur de colonne

Parmi les rubriques suivantes, reportez-vous à celle qui correspond à l’erreur que vous rencontrez :

Dès que vous ouvrez une feuille de calcul contenant des formules qui font référence à des valeurs d’autres feuilles de calcul, vous êtes invité à mettre à jour les références ou à les conserver en l’état.

Boîte de dialogue signalant des liens rompus dans Excel

Excel affiche la boîte de dialogue ci-dessus pour vérifier si les formules de la feuille de calcul active pointent toujours vers la valeur mise à jour la plus récente, au cas où la valeur référencée aurait été modifiée. Vous pouvez choisir de mettre à jour les références ou d’ignorer cette étape si vous n’avez pas envie de procéder à la mise à jour. Même si vous choisissez de ne pas mettre à jour les références, vous pouvez toujours choisir de mettre à jour les liens de la feuille de calcul plus tard.

Vous pouvez également choisir de désactiver l’affichage de la boîte de dialogue au démarrage. Pour ce faire, accédez à Fichier > Options > Options avancées > Général et désactivez la case à cocher Confirmer la mise à jour automatique des liens. Dans Excel 2007, vous devez cliquer sur le Bouton Office > Options Excel. Bouton Office 2007

Image de l’option Confirmer la mise à jour automatique des liens

Important : Si c’est la première fois que vous avez affaire à des liens rompus dans les formules, si vous avez besoin d’un rappel sur la manière de corriger les liens rompus ou si vous ne savez pas s’il convient de mettre à jour les références, voir Contrôler la mise à jour des références externes (liens).

Si la formule n’affiche pas la valeur, procédez comme suit :

  • Assurez-vous qu’Excel est configuré de manière à afficher les formules dans votre feuille de calcul. Pour ce faire, cliquez sur l’onglet Formules. Dans le groupe Vérification des formules, cliquez sur Afficher les formules.

    Conseil : Vous pouvez également utiliser le raccourci clavier Ctrl+". Lorsque vous effectuez cette action, vos colonnes s’élargissent automatiquement de manière à afficher vos formules. Ne vous inquiétez pas, lorsque vous repassez en affichage normal, vos colonnes sont redimensionnées.

  • Si l’étape ci-dessus ne résout toujours pas votre problème, il est possible que la cellule soit mise en forme comme du texte. Vous pouvez cliquer avec le bouton droit sur la cellule et sélectionner Format de cellule > Standard (ou Ctrl+1), puis appuyer sur F2 > Entrée pour modifier le format.

  • Si vous avez une grande plage de cellules dans une colonne qui sont mises en forme comme du texte, vous pouvez sélectionner la plage, appliquer le format numérique de votre choix et accéder à Données > Convertir > Terminer. Cela permet d’appliquer la mise en forme à toutes les cellules sélectionnées.

    Image de la boîte de dialogue Données > Convertir

Lorsqu’une formule n’est pas calculée, vous devez vérifier si le calcul automatique est activé dans Excel. Les formules ne sont pas calculées si le calcul manuel est activé. Procédez comme suit pour vérifier si le calcul automatique est activé ou non :

  1. Cliquez sur l’onglet Fichier, cliquez sur Options, puis cliquez sur la catégorie Formules.

  2. Dans la section Mode de calcul, sous Calcul du classeur, vérifiez que l’option Automatique est sélectionnée.

    Image des options Calcul automatique et Calcul manuel

Pour plus d’informations sur les calculs, voir Modifier un recalcul, une itération ou une précision de formule.

La présence d’une référence circulaire signifie qu’une formule fait référence à la cellule dans laquelle elle se trouve. Les deux seules manières de résoudre le problème consistent à déplacer la formule dans une autre cellule ou à remplacer la syntaxe de la formule par une syntaxe qui permet d’éviter les références circulaires. Toutefois, dans certains scénarios, vous aurez besoin de références circulaires, car elles permettent à vos fonctions d’itérer (se répéter) tant qu’une condition précise n’est pas remplie. Dans ce cas, vous devez activer le calcul itératif.

Pour plus d’informations sur les références circulaires, voir Rechercher et corriger une référence circulaire.

Si votre entrée ne commence pas par un signe égal, il ne s’agit pas d’une formule et elle ne sera pas calculée. Il s’agit d’une erreur classique.

Par exemple, lorsque vous tapez SOMME(A1:A10), Excel affiche la chaîne de texte SOMME(A1:A10) au lieu d’un résultat de formule. Maintenant, si vous tapez 11/2, Excel affiche une date telle que 2-Nov ou 02/11/2009, et non le résultat de la division de 11 par 2.

Pour éviter ces résultats inattendus, faites toujours précéder la fonction d’un signe égal. Par exemple, tapez : =SOMME(A1:A10) et =11/2.

Lorsque vous utilisez une fonction dans une formule, chaque parenthèse ouvrante doit avoir une parenthèse fermante pour que la fonction s’exécute correctement. Vous devez donc vérifier que toutes les parenthèses font effectivement partie d’une paire. Par exemple, la formule =SI(B5<0);"Non valide";B5*1,05) ne fonctionnera pas, car elle contient deux parenthèses fermantes et une seule parenthèse ouvrante. La formule correcte se présente comme suit : =SI(B5<0;"Non valide";B5*1,05).

Les fonctions Excel utilisent des arguments dont vous devez spécifier la valeur pour qu’elles opèrent. Seules quelques fonctions (telles que PI ou AUJOURDHUI) ne prennent aucun argument. Vérifiez la syntaxe de la formule qui s’affiche lorsque vous commencez à saisir la fonction pour vous assurer qu’elle contient les arguments requis.

Par exemple, la fonction MAJUSCULE n’accepte qu’une seule chaîne de texte ou référence de cellule comme argument : =MAJUSCULE("bonjour") ou =MAJUSCULE(C2)

Remarque : Les arguments de la fonction sont répertoriés dans une barre d’outils flottante contenant les références aux fonctions. Celle-ci est située sous la formule et s’affiche au fur et à mesure que vous tapez cette dernière.

Capture d’écran de la barre d’outils des références aux fonctions
Barre d’outils des références aux fonctions

En outre, certaines fonctions, telles que SOMME, requièrent des arguments uniquement numériques, tandis que d’autres, telles que REMPLACER, requièrent une valeur de texte pour au moins l’un de leurs arguments. Si vous utilisez un type de données incorrect, les fonctions peuvent renvoyer des résultats inattendus ou une erreur #VALEUR!.

Si vous avez besoin rechercher rapidement la syntaxe d’une fonction particulière, consultez la liste des fonctions Excel (par catégorie).

N’entrez pas de nombres accompagnés de symboles dollar ($) ni de séparateurs de décimale (,) dans les formules. Les symboles dollar indiquent des références absolues et les virgules sont les séparateurs d’arguments. Au lieu de 1000$, entrez 1000 dans la formule.

Si vous utilisez des nombres mis en forme dans des arguments, le calcul produit des résultats inattendus et vous risquez d’obtenir l’erreur#NOMBRE!. Par exemple, si vous entrez la formule =ABS(-2134) pour trouver la valeur absolue de -2134, Excel renvoie l’erreur #NOMBRE!, car la fonction ABS n’accepte qu’un seul argument.

Remarque : Vous pouvez mettre en forme le résultat de la formule avec des séparateurs de décimale et des symboles monétaires après avoir entré la formule composée de nombres non mis en forme (constantes). Nous vous déconseillons d’entrer des constantes dans les formules, car il peut être difficile de les trouver si vous avez besoin de les mettre à jour ultérieurement. De plus, elles sont bien plus susceptibles de contenir des erreurs lors de leur saisie. Il est préférable de placer vos constantes dans des cellules où elles seront visibles et pourront être facilement référencées.

Votre formule peut ne pas renvoyer les résultats escomptés si le type de données de la cellule ne peut pas être utilisé dans des calculs. Par exemple, si vous entrez une formule simple =2+3 dans une cellule au format texte, Excel ne peut pas calculer les données que vous avez entrées. Tout ce qui apparaît dans la cellule est =2+3. Pour résoudre ce problème, modifiez le type de données Texte de la cellule en Général, comme ceci :

  1. Sélectionnez la cellule.

  2. Cliquez sur Accueil, cliquez sur la flèche en regard de Format de nombre (ou appuyez sur Ctrl+1), puis cliquez sur Standard.

  3. Appuyez sur F2 pour mettre la cellule en mode d’édition, puis appuyez sur Entrée pour accepter la formule.

Si vous entrez une date dans une cellule qui utilise le type de données Nombre, elle risque d’apparaître sous forme d’une valeur de date numérique au lieu d’une date. Pour afficher un nombre sous forme d’une date, sélectionnez le format Date dans la galerie Format de nombre.

Il est assez courant d’utiliser x comme opérateur de multiplication dans une formule, mais Excel n’accepte que l’astérisque (*). Si vous utilisez des constantes dans votre formule, Excel affiche un message d’erreur, et peut corriger la formule automatiquement pour vous en remplaçant le x par un astérisque (*).

Message invitant à remplacer x par * dans une multiplication
Message d’erreur lors de l’utilisation de x avec des constantes au lieu de * dans une multiplication

Toutefois, si vous utilisez des références de cellules, Excel renvoie une erreur #NOM?.

Erreur #NOM? lors de l’utilisation de x au lieu de * avec des références de cellules dans le cas d’une multiplication
Erreur #NOM? lors de l’utilisation de x au lieu de * avec des références de cellules

Si vous créez une formule qui contient du texte, placez le texte entre guillemets.

Par exemple, la formule ="Nous sommes aujourd’hui le " & TEXTE(AUJOURDHUI(),"jjjj, mmmm jj") associe le texte « Nous sommes aujourd’hui le » aux résultats des fonctions TEXTE et AUJOURDHUI, et renvoie quelque chose ressemblant à Nous sommes aujourd’hui le lundi 30 mai.

Notez que, dans la formule, "Nous sommes aujourd’hui le " contient un espace avant les guillemets de fin, ce qui permet d’ajouter l’espace nécessaire entre "Nous sommes aujourd’hui le" et "lundi 30 mai". Si vous ne placez pas le texte entre guillemets, la formule risque de générer l’erreur #NOM?.

Vous pouvez combiner (ou imbriquer) jusqu’à 64 niveaux de fonctions à l’intérieur d’une formule.

Par exemple, la formule =SI(RACINE(PI())<2,"Moins de deux !","Plus de deux !") comporte 3 niveaux de fonctions : la fonction PI est imbriquée dans la fonction RACINE, qui est à son tour imbriquée dans la fonction SI.

Lorsque vous tapez une référence à des valeurs ou des cellules d’une autre feuille de calcul et que le nom de cette feuille contient un caractère non alphabétique (par exemple, un espace), placez une apostrophe (') de part et d’autre du nom.

Par exemple, pour renvoyer la valeur de la cellule D3 dans une feuille de calcul nommée « Données trimestrielles » dans votre classeur, tapez : =’Données trimestrielles’!D3. Si vous ne placez pas le nom de feuille entre guillemets, la formule risque de générer l’erreur #NOM?.

Vous pouvez également cliquer sur les valeurs ou cellules dans une autre feuille pour y faire référence dans votre formule. Excel place automatiquement les noms de feuilles entre des guillemets.

Lorsque vous tapez une référence à des valeurs ou des cellules dans un autre classeur, placez le nom du classeur entre des crochets ([]), suivi du nom de la feuille de calcul contenant les valeurs ou cellules.

Par exemple, pour inclure une référence aux cellules A1 à A8 dans la feuille Ventes du classeur Operations T2 ouvert dans Excel, tapez : =[Operations T2.xlsx]Ventes!A1:A8. Sans les crochets, la formule affiche l’erreur #REF!.

Si le classeur n’est pas ouvert dans Excel, tapez le chemin d’accès complet au fichier.

Par exemple, =LIGNES('C:\Mes documents\[Operations T2.xlsx T2]Ventes'!A1:A8).

Remarque : Si le chemin d’accès complet contient des espaces, utilisez des guillemets simples (au début du chemin d’accès et après le nom de la feuille de calcul, avant le point d’exclamation).

Conseil : La méthode la plus simple pour obtenir le chemin d’accès à l’autre classeur consiste à ouvrir ce dernier. Ensuite, dans votre classeur d’origine, tapez =, utilisez le raccourci Alt+Tab pour passer à l’autre classeur, puis sélectionnez une cellule dans la feuille de votre choix. Fermez ensuite le classeur source. Votre formule est mise à jour automatiquement de manière à afficher le chemin d’accès complet et le nom de la feuille, conjointement avec la syntaxe requise. Vous pouvez même copier-coller le chemin d’accès et l’utiliser là où vous en avez besoin.

Si vous divisez une cellule par une autre contenant la valeur zéro (0) ou aucune valeur, une erreur #DIV/0! est générée.

Pour éviter cette erreur, vous pouvez entrer votre formule directement et tester l’existence d’un dénominateur, comme suit :

=SI(B1,A1/B1,0)

Cela signifie SI(B1 existe, diviser A1 par B1, sinon renvoyer 0).

Vérifiez toujours si vous disposez de toutes les formules qui font référence à des données dans des cellules, des plages, des noms définis, des feuilles de calcul ou des classeurs, avant de supprimer quoi que ce soit. Vous pouvez ensuite remplacer ces formules par leurs résultats avant de supprimer les données référencées.

Si vous ne pouvez pas remplacer les formules par leurs résultats, consultez les informations suivantes sur les erreurs et solutions possibles :

  • Si une formule fait référence à des cellules qui ont été supprimées ou remplacées par d’autres données et renvoie une erreur #REF!, sélectionnez la cellule contenant l’erreur #REF!. Dans la barre de formule, sélectionnez #REF! et supprimez-la. Tapez de nouveau la plage de la formule.

  • Si un nom défini est manquant, et qu’une formule faisant référence à ce nom renvoie une erreur #NOM?, définissez un nouveau nom qui fait référence à la plage de votre choix ou modifiez la formule pour faire référence directement à la plage de cellules (par exemple, A2:D8).

  • Si une feuille de calcul est manquante, toute formule y faisant référence renvoie une erreur #REF!. Ce problème ne peut pas être résolu malheureusement. Vous ne pouvez pas récupérer les feuilles de calcul supprimées.

  • Si un classeur est manquant, toute formule y faisant référence reste inchangée jusqu’à ce que vous mettiez à jour la formule.

    Par exemple, si votre formule est =[Classeur1.xlsx]Feuil1'!A1 et que Classeur1.xlsx n’existe plus, les valeurs référencées dans ce classeur restent disponibles. Toutefois, si vous modifiez et enregistrez une formule qui fait référence à ce classeur, Excel affiche la boîte de dialogue Mettre à jour les valeurs et vous invite à entrer un nom de fichier. Cliquez sur Annuler, puis vérifier que ces données ne sont pas égarées en remplaçant les formules qui font référence au classeur manquant par leurs résultats.

Parfois, lorsque vous copiez le contenu d’une cellule, vous voulez coller uniquement la valeur et non la formule sous-jacente qui s’affiche dans la barre de formule.

Par exemple, il se peut que vous vouliez copier la valeur résultant d’une formule vers une cellule figurant dans une autre feuille de calcul. Ou peut-être voulez-vous supprimer les valeurs que vous avez utilisées dans une formule après avoir copié la valeur qui en résulte vers une autre cellule figurant dans la même feuille de calcul. Ces deux actions ont pour effet qu’une erreur de référence de cellule non valide (#REF!) s’affiche dans la cellule de destination, car les cellules contenant les valeurs que vous avez utilisées dans la formule ne peuvent plus être référencées.

Vous pouvez éviter cette erreur en collant dans les cellules de destination les valeurs résultant des formules sans la formule proprement dite.

  1. Dans une feuille de calcul, sélectionnez les cellules contenant les valeurs résultant d’une formule, que vous voulez copier.

  2. Dans l’onglet Accueil, dans le groupe Presse-papiers, cliquez sur Copier Image du bouton .

    Image du ruban Excel

    Raccourci clavier : appuyez sur Ctrl+C.

  3. Sélectionnez la cellule supérieure gauche de la zone de collage.

    Conseil : Pour déplacer ou copier une sélection vers une autre feuille de calcul ou un autre classeur, cliquez sur un autre onglet ou basculez vers un autre classeur, puis sélectionnez la cellule supérieure gauche de la zone de collage.

  4. Dans l’onglet Accueil, dans le groupe Presse-papiers, cliquez sur Coller Image du bouton , puis cliquez sur Coller des valeurs. Vous pouvez également appuyer sur Alt > L > V > V > Entrée sur Windows ou sur Option > Cmd > V > V > Entrée sur Mac.

Pour comprendre la manière dont une formule complexe ou imbriquée calcule le résultat final, vous pouvez évaluer cette formule.

  1. Sélectionnez la formule à évaluer.

  2. Cliquez sur Formules > Évaluer la formule.

    Groupe Vérification des formules dans l’onglet Formules

  3. Cliquez sur Évaluer pour examiner la valeur de la référence soulignée. Le résultat de l’évaluation apparaît en italique.

    Boîte de dialogue Évaluer la formule

  4. Si la partie soulignée de la formule est une référence à une autre formule, cliquez sur Pas à pas détaillé pour afficher l’autre formule dans la zone Évaluation. Cliquez sur Pas à pas sortant pour revenir à la cellule précédente et la formule.

    Le bouton Pas à pas détaillé n’est pas disponible lorsque la référence apparaît pour la deuxième fois dans la formule, ou si la formule fait référence à une cellule d’un autre classeur.

  5. Poursuivez jusqu’à ce que toutes les parties de la formule soient évaluées.

    L’outil Évaluer la formule ne vous indiquera pas nécessairement la raison pour laquelle votre formule est incorrecte, mais il peut vous aider à identifier l’emplacement concerné. Cela peut être un outil très utile dans les grandes formules dans lesquelles il pourrait être autrement difficile de détecter le problème.

    Remarques : 

    • Certaines parties des fonctions SI et CHOISIR ne sont pas évaluées et l’erreur #N/A peut apparaître dans la zone Évaluation.

    • Les références vides apparaissent sous la forme de valeurs zéro (0) dans la zone Évaluation.

    • Certaines fonctions sont recalculées chaque fois que la feuille de calcul est modifiée. En raison de celles-ci (notamment les fonctions ALEA, ZONES, INDEX, DECALER, CELLULE, INDIRECT, LIGNES, COLONNES, MAINTENANT, AUJOURD’HUI et ALEA.ENTRE.BORNES), la boîte de dialogue Évaluer la formule peut afficher des résultats différents des résultats réels dans la cellule de la feuille de calcul.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Voir aussi

Vue d’ensemble des formules dans Excel

Détecter les erreurs dans les formules

Fonctions Excel (par ordre alphabétique)

Fonctions Excel (par catégorie)

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×