Les 10 erreurs FATALES qui détruisent vos données sur Excel (et comment les éviter)
Dans ce tutoriel, les dix erreurs dangereuses à ne JAMAIS faire sur Excel. Il s’agit d’erreurs que nous commettons tous, quel que soit notre niveau de maîtrise et qui peuvent littéralement détruire nos données, fausser nos analyses et compromettre des décisions stratégiques importantes.
Ces erreurs sont responsables de millions d'euros de pertes chaque année dans les entreprises, et pourtant elles sont facilement évitables quand on connaît les bonnes techniques.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les données de commandes d'une entreprise de e-commerce.
Ce tableau contient toutes les informations critiques d'une activité commerciale : identifiants de commandes, clients, produits, quantités, prix, TVA, dates, régions, vendeurs et statuts de livraison.
Chaque ligne représente une commande avec un chiffre d'affaires potentiel, et chaque erreur peut avoir des conséquences dramatiques sur les résultats financiers.
2. Les erreurs fatales
2.1. L'erreur fatale 1 : Négliger l'audit et le contrôle des formules en cascade
La première et dernière erreur fatale concerne l'absence d'audit systématique des formules interconnectées et de leurs dépendances.
Cette erreur est particulièrement vicieuse car elle peut créer des erreurs en cascade qui se propagent à travers tout le classeur sans être détectées.
Dans notre tableau de commandes, imaginons que nous ayons créé des formules complexes qui dépendent les unes des autres : calcul du montant HT, puis TTC, puis commission vendeur, puis marge bénéficiaire.
Si une erreur s'introduit dans le calcul du montant HT, elle contaminera automatiquement tous les calculs suivants.
Le problème majeur réside dans le fait que ces erreurs en cascade peuvent rester invisibles pendant des mois, faussant progressivement toutes nos analyses financières et nos prises de décision stratégiques.
Pour éviter cette erreur critique, nous devons mettre en place un système d'audit des formules.
Excel propose des outils puissants dans l'onglet « Formules » : « Repérer les antécédents » et « Repérer les dépendants ».
Ces fonctions nous permettent de visualiser graphiquement les relations entre nos cellules.
Nous sélectionnons d'abord la cellule contenant notre formule critique (par exemple le calcul de marge en colonne N) puis nous cliquons sur « Repérer les dépendants ».
Excel dessine des flèches bleues qui nous montrent toutes les cellules qui dépendent de cette formule.
De même, « Repérer les antécédents » nous montre toutes les cellules qui alimentent notre calcul.
Le raccourci [Ctrl]+["] nous fait basculer entre l'affichage des valeurs et l'affichage des formules, permettant une vérification rapide de la cohérence de nos calculs.
Cette fonction permet de détecter les formules qui auraient été accidentellement remplacées par des valeurs fixes.
2.2. L'erreur fatale 2 : Références relatives non sécurisées
La troisième erreur concerne l'utilisation de références relatives dans nos formules sans protection adéquate.
Cette erreur peut transformer des calculs corrects en résultats complètement faux après insertion ou suppression de lignes.
Créons une colonne « Montant_HT » en colonne K pour calculer le montant hors taxes de chaque commande.
Si nous saisissons la formule «=D2*E2» en cellule K2, cette formule fonctionnera correctement pour calculer le montant de la première commande.
Le problème survient lorsque nous insérons des lignes au-dessus de nos données.
Notre formule «=D2*E2» devient alors «=D15*E15» et ne correspond plus à la bonne ligne de données.
Cette erreur peut passer inaperçue pendant des mois et fausser tous nos calculs.
Pour éviter ce problème, nous utilisons des références mixtes ou absolues selon le contexte.
Dans notre cas, nous modifions notre formule pour utiliser des références de lignes absolues : «=$D2*$E2».
Cette syntaxe fixe la référence de ligne et empêche le décalage accidentel.
2.3. L'erreur fatale 3 : Formules avec des erreurs masquées
La quatrième erreur critique concerne la gestion des erreurs dans nos formules.
Les erreurs #VALEUR!, #DIV/0! ou #N/A peuvent contaminer nos calculs et rendre nos analyses complètement fausses.
Ajoutons une colonne « Montant_TTC » en colonne L avec la formule «=D2*E2*(1+F2)».
Cette formule calcule le montant toutes taxes comprises en multipliant la quantité par le prix unitaire puis en ajoutant la TVA.
Si l'une des cellules référencées contient une erreur ou une valeur non numérique, notre formule retournera une erreur.
Ces erreurs peuvent être particulièrement vicieuses car la fonction SOMME() les ignore silencieusement.
Si nous calculons le chiffre d'affaires total avec «=SOMME(L2:L9)» et que certaines cellules contiennent des erreurs, notre somme sera sous-évaluée sans que nous nous en rendions compte.
Pour éviter ce problème, nous utilisons systématiquement la fonction SIERREUR() dans nos formules critiques.
Nous modifions notre formule pour obtenir : «=SIERREUR(D2*E2*(1+F2);"Erreur de calcul")». Cette fonction retourne notre calcul normal si tout se passe bien, ou affiche « Erreur de calcul » en cas de problème.
Cette approche nous permet d'identifier rapidement les lignes problématiques et de les corriger avant qu'elles ne contaminent nos analyses.
Nous pouvons même créer une formule de contrôle global avec «=SOMME.SI(L2:L9;"Erreur de calcul";1)» pour compter le nombre d'erreurs dans notre tableau.
Le raccourci [Ctrl]+[H] nous permet d'ouvrir la fonction « Rechercher et remplacer » pour identifier et corriger rapidement toutes les erreurs dans notre feuille de calcul.
2.4. L'erreur fatale 4 : Doublons non détectés
La cinquième erreur concerne la présence de doublons dans nos données, particulièrement dangereuse pour les calculs de chiffre d'affaires et de commissions commerciales.
Un doublon peut nous faire compter deux fois la même vente et fausser complètement nos résultats.
Pour détecter les doublons dans notre colonne « ID_Commande », nous utilisons la mise en forme conditionnelle.
Nous sélectionnons la colonne A puis nous nous rendons dans l'onglet « Accueil ».
Nous cliquons sur « Mise en forme conditionnelle » puis sur « Règles de mise en surbrillance des cellules » et enfin sur « Valeurs en double ».
Excel nous propose automatiquement un format de mise en surbrillance pour les doublons. Nous validons avec « OK » et tous les identifiants de commandes dupliqués apparaissent maintenant surlignés en rouge.
La suppression des doublons s'effectue via l'onglet « Données » puis « Supprimer les doublons ». Cette fonction analyse notre tableau et supprime automatiquement les lignes en double selon les critères que nous définissons.
2.5. L'erreur fatale 5 : Calculs sur des données filtrées
La sixième erreur critique concerne l'utilisation de fonctions inadaptées sur des données filtrées.
Lorsque nous appliquons un filtre pour masquer certaines lignes, les fonctions SOMME() continuent de calculer sur toutes les données, y compris celles qui sont masquées.
Pour illustrer ce problème, nous appliquons un filtre sur notre tableau.
Nous sélectionnons nos données puis nous cliquons sur « Filtrer » dans l'onglet « Données ».
Nous filtrons ensuite la colonne « Statut » pour ne conserver que les commandes « Livrées » en décochant « Annulé » et « En cours ».
Si nous calculons maintenant le chiffre d'affaires total avec la formule «=SOMME(L2:L9)», cette fonction inclura toutes les commandes, même celles qui sont masquées par le filtre.
Notre calcul sera donc faux car il inclura les commandes annulées.
Pour corriger ce problème, nous utilisons la fonction SOUS.TOTAL() qui ignore automatiquement les lignes masquées par un filtre. Nous remplaçons notre formule par «=SOUS.TOTAL(109;L2:L9)». Le code 109 correspond à la fonction SOMME en ignorant les lignes masquées.
Le raccourci [Ctrl]+[Maj]+[L] nous permet d'activer ou désactiver rapidement les filtres sur notre tableau sélectionné.
2.6. L'erreur fatale 6 : Aucune protection des formules
La septième erreur concerne l'absence de protection de nos formules critiques.
Une modification accidentelle d'une formule peut avoir des conséquences dramatiques sur l'ensemble de nos calculs et analyses.
Dans notre exemple, les formules de calcul des montants TTC sont cruciales pour l'analyse financière.
Si quelqu'un remplace accidentellement la formule «=D2*E2*(1+F2)» par une valeur fixe comme « 3000 », cette erreur peut passer inaperçue et fausser tous nos rapports de ventes.
Pour protéger nos formules, nous commençons par sélectionner toutes les cellules contenant des données (A2:M9) puis nous utilisons le raccourci [Ctrl]+[1] pour ouvrir la boîte de dialogue « Format de cellule ».
Dans l'onglet « Protection », nous décochons « Verrouillées » pour ces cellules de données.
Nous sélectionnons ensuite uniquement les cellules contenant nos formules critiques (colonnes K, L et M).
Nous ouvrons à nouveau la boîte de dialogue « Format de cellule » et nous cochons « Verrouillées » pour ces cellules seulement.
Maintenant, nous nous rendons dans l'onglet « Révision » et nous cliquons sur « Protéger la feuille ».
Dans la boîte de dialogue qui s'ouvre, nous définissons un mot de passe robuste et nous cochons les actions autorisées pour les utilisateurs : « Sélectionner les cellules déverrouillées », « Trier », « Utiliser le filtre automatique ».
Cette protection permet aux utilisateurs de modifier les données mais empêche toute modification accidentelle des formules. Pour modifier une formule protégée, il faudra désactiver la protection avec le mot de passe.
2.7. L'erreur fatale 7 : Ignorer les avertissements Excel
La huitième erreur critique concerne l'habitude d'ignorer systématiquement les avertissements Excel, représentés par des petits triangles verts dans les coins des cellules.
Ces avertissements signalent des incohérences potentielles dans nos données ou nos formules.
Par exemple, si nous avons une série de formules identiques et qu'une cellule contient une formule différente, Excel affiche un triangle vert pour nous alerter.
Dans notre exemple, si nous modifions la formule en L5 pour écrire «=D5*E5*1,2» au lieu de «=D5*E5*(1+F5)», Excel détectera cette incohérence avec le triangle vert.
Cette différence peut être volontaire (taux de TVA différent) ou accidentelle (erreur de saisie).
Pour traiter ces avertissements, nous cliquons sur la cellule concernée puis sur le petit losange jaune qui apparaît.
Excel nous propose alors plusieurs options : « Copier la formule de la cellule du dessus », « Ignorer l'erreur » ou « Modifier dans la barre de formule ».
Nous devons traiter chaque avertissement individuellement en analysant s'il s'agit d'une erreur à corriger ou d'une exception volontaire. Pour les exceptions volontaires, nous cliquons sur « Ignorer l'erreur » pour faire disparaître définitivement l'avertissement.
Le raccourci [Ctrl]+[G] puis « Cellules » nous permet de sélectionner rapidement toutes les cellules contenant des erreurs pour les traiter en lot.
2.8. L'erreur fatale 8 : Pas de documentation des formules
La neuvième erreur concerne l'absence de documentation de nos formules complexes.
Un fichier Excel sans documentation devient rapidement un « fichier orphelin » que personne ne peut maintenir ou corriger en cas de problème.
Pour documenter nos formules, nous utilisons les commentaires Excel.
Nous cliquons avec le bouton droit sur la cellule L2 (qui contient notre formule de calcul TTC) puis nous sélectionnons « Nouveau commentaire ».
Dans la zone de texte qui apparaît, nous saisissons : « Calcul du montant TTC = Quantité × Prix unitaire × (1 + TVA) ».
Cette documentation permet à n'importe qui de comprendre rapidement la logique de nos calculs.
Pour les formules particulièrement complexes, nous créons un onglet dédié « Documentation » dans notre classeur.
Dans cet onglet, nous créons un tableau récapitulatif de toutes nos formules avec leurs explications détaillées.
Nous y mentionnons également les hypothèses de calcul, les sources de données et les éventuelles limitations de nos analyses.
Le raccourci [Maj]+[F2] nous permet d'ouvrir rapidement la fenêtre d'édition de formule pour analyser les références et la logique de calcul.
2.9. L'erreur fatale 9 : Aucune validation des données
La neuvième erreur critique concerne l'absence de validation des données saisies dans notre tableau.
Sans contrôle, nous risquons de saisir des données incohérentes qui fausseront tous nos calculs et analyses.
Prenons l'exemple de notre colonne « Prix_Unitaire ». Si nous saisissons par erreur « 250000 » au lieu de « 2500,00 », cette erreur faussera complètement nos calculs de chiffre d'affaires et nos analyses de rentabilité.
Pour éviter ce problème, nous sélectionnons d'abord la colonne E (Prix_Unitaire) en cliquant sur son en-tête.
Nous nous rendons ensuite dans l'onglet « Données » et nous cliquons sur « Validation des données ». Dans la fenêtre qui s'ouvre, nous sélectionnons « Décimal » comme type de données autorisées.
Nous définissons alors les critères de validation : « supérieur ou égal à » 0 et « inférieur ou égal à » 10000.
Cette validation empêchera la saisie de prix négatifs ou anormalement élevés.
Nous personnalisons également le message d'erreur en saisissant : « Veuillez saisir un prix entre 0 et 10000 euros ».
Pour la colonne « Quantité », nous appliquons la même méthode mais en sélectionnant « Nombre entier » avec des valeurs comprises entre 1 et 1000.
Cette validation évite les quantités négatives ou irréalistes.
La colonne « Statut » nécessite une validation par liste.
Nous sélectionnons cette colonne puis nous créons une liste déroulante avec les valeurs « Livré;En cours;Annulé ».
Cette approche garantit l'homogénéité des données et évite les erreurs de frappe.
2.10. L'erreur fatale 10 : Pas de sauvegarde automatique
La dixième et sans doute la plus catastrophique des erreurs est l'absence de sauvegarde fiable et automatique de vos fichiers Excel.
Cette négligence peut anéantir des heures, voire des jours entiers de travail, à la suite d'un plantage du logiciel, d'une fermeture accidentelle du fichier ou d'une panne informatique.
C'est l'erreur la plus redoutable car elle réduit à néant tous vos efforts en un instant.
Bien qu'Excel propose une fonction de récupération automatique, celle-ci n'est pas infaillible et se révèle souvent insuffisante.
Il n'est pas rare que les données ne puissent pas être correctement restaurées ou que des éléments complexes, comme les tableaux croisés dynamiques, se retrouvent corrompus lors de la récupération.
De plus, si vous enregistrez par mégarde une erreur dans votre fichier, la récupération automatique ne vous sera d'aucune aide.
Pour éliminer définitivement ce risque majeur, la solution la plus robuste et la plus fiable consiste à utiliser un outil spécialisé comme saveMyfiles.
Il s'agit d'un complément à installer directement dans Excel, conçu pour sécuriser votre travail de manière proactive.
Voici comment saveMyfiles résout ce problème :
- Sauvegardes automatiques et fiables : L'outil crée automatiquement des "clichés" (des copies de sauvegarde complètes) de vos fichiers à intervalles réguliers, que vous pouvez définir (de 5 à 60 minutes).
- Protection contre les erreurs : Contrairement à la fonction native d'Excel, saveMyfiles vous protège non seulement des plantages, mais aussi des erreurs que vous auriez enregistrées par inadvertance. Vous pouvez facilement restaurer une version antérieure saine de votre fichier.
- Gestion et historisation des versions : Le complément vous permet de conserver un historique de vos sauvegardes (par jour, semaine, ou mois) et de gérer simplement ces clichés depuis une interface dédiée pour restaurer la version de votre choix en un clic.
- Contrôle total : Vous pouvez choisir le dossier de destination des sauvegardes, activer ou désactiver la fonction à la volée et lancer des enregistrements manuels si besoin.
En adoptant cette solution, vous vous assurez de ne plus jamais perdre de données importantes sur Excel.
Pour découvrir et installer cet outil, vous pouvez consulter la page suivante : https://www.excelformation.fr/savemyfiles-no.html.