Dans ce tutoriel, je vais vous montrer comment corriger 5 erreurs très courantes que nous faisons TOUS dans Excel, souvent sans même nous rendre compte qu'elles compliquent nos fichiers.
Nous allons partir d'un tableau de ventes volontairement mal construit et l'améliorer.
Nous verrons donc que le problème, c'est que ces erreurs n'empêchent pas forcément Excel de fonctionner. Elles deviennent surtout gênantes lorsque nous voulons filtrer nos données, ajouter des lignes, modifier un paramètre ou créer une analyse.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les ventes d'une petite entreprise de matériel informatique :
À première vue, notre fichier fonctionne. Pourtant, nous allons voir qu'il contient déjà plusieurs pièges classiques.
2. Insérer des lignes vides dans une base
Commençons par la première erreur, que nous retrouvons très souvent dans les fichiers Excel : insérer des lignes vides au milieu d'une base de données pour séparer visuellement les informations.
Si nous regardons dans notre tableau de départ, nous voyons qu’après les ventes du 05 septembre, nous avons laissé une ligne complètement vide pour séparer la première semaine de la suivante.
À première vue, cette ligne vide peut sembler pratique. Elle crée une séparation visuelle entre les deux semaines et rend le tableau un peu plus aéré.
Le problème, c'est que nous raisonnons ici comme si Excel était une feuille de papier.
Or, pour pouvoir trier, filtrer, créer des formules ou construire un tableau croisé dynamique, Excel a besoin d'une base de données correctement organisée.
Dans notre tableau, chaque ligne doit représenter une vente et chaque colonne doit représenter une information précise concernant cette vente.
La ligne vide ne correspond à aucune vente. Elle n'a donc rien à faire au milieu de notre base.
Pour observer l'un des problèmes qu'elle provoque, nous sélectionnons la cellule A9, qui contient la première date, puis nous utilisons le raccourci [Ctrl]+[A].
Excel sélectionne la zone de données continue autour de notre cellule. La ligne vide marque alors une séparation dans cette zone : les ventes situées en dessous peuvent ne plus être considérées comme faisant partie du même bloc de données selon la manipulation utilisée.
Nous retrouvons le même problème avec certains raccourcis de navigation. Si nous nous plaçons dans A7 et utilisons [Ctrl]+[↓], Excel se déplace jusqu'à la dernière cellule renseignée avant la rupture provoquée par la ligne vide, au lieu de parcourir naturellement toute notre base.
Et ce principe ne concerne pas uniquement les lignes totalement vides.
Nous aurions également pu être tentés d'écrire au milieu du tableau quelque chose comme « SEMAINE 2 » ou « TOTAL SEMAINE 1 ».
Le problème serait exactement le même : cette ligne ne représenterait plus une vente et casserait la structure régulière de notre base.
Pour corriger notre fichier, nous cliquons donc sur le numéro de la ligne vide situé à gauche de la feuille, puis nous effectuons un clic droit et choisissons « Supprimer ».
Les deux blocs de ventes se retrouvent maintenant l'un à la suite de l'autre.
Et c'est exactement ce que nous recherchons.
Si nous avons besoin de connaître la semaine correspondant à chaque vente, nous ne créons pas une séparation manuelle. Nous pouvons ajouter une véritable colonne « Semaine » dans notre base, ou utiliser ensuite un tableau croisé dynamique pour regrouper les dates.
De la même manière, si nous voulons simplement rendre le tableau plus lisible, nous pouvons utiliser une mise en forme avec des lignes alternées plutôt que d'insérer des espaces.
Retenons donc cette règle pour toute la suite : dans une base Excel, nous évitons les lignes vides et les lignes de présentation au milieu des données. Une ligne correspond à un enregistrement et une colonne à une information.
Nous avons maintenant une base continue. Nous pouvons passer à une deuxième habitude qui paraît pratique pour la présentation, mais qui peut également nous poser pas mal de problèmes : les cellules fusionnées.
3. Fusionner des cellules
Deuxième erreur : les cellules fusionnées.
Si nous regardons la cellule A6, nous voyons que celle-ci contient le texte « VENTES SEPTEMBRE 2026 », mais que le contenu est fusionné sur toute la largeur du tableau :
Le résultat est propre, mais essayons maintenant de déplacer la colonne « Commercial » après la colonne « Région » :
Impossible car Excel refuse de déplacer une place contenant des cellules fusionnées…
Dans une base de données, nous évitons complètement les cellules fusionnées.
Pour un titre, nous disposons même d'une alternative : nous sélectionnons A2 et appuyons sur [Ctrl]+[1]. Dans l'onglet « Alignement », nous choisissons « Centré sur plusieurs colonnes ».
Visuellement, notre titre reste centré, mais A2 à G2 restent bien des cellules indépendantes.
Nous pouvons maintenant déplacer sans problème la colonne B.
4. Éviter les données incohérentes
Regardons maintenant notre colonne « Région ».
Nous pouvons voir que la région Sud est saisie de deux manières différentes : « Sud » et « SUD ».
Ici, cette incohérence ne porte pas forcément à conséquence dans la mesure où la plupart des fonctions Excel de tiennent pas compte des différences de casse, c’est-à-dire des minuscules et des majuscules.
En revanche, nous voyons également dans cet exemple, que nous pouvons retrouver « Ile-de-France », « Île de France », « IDF ».
Là c’est beaucoup gênant car pour Excel il s’agit de valeurs totalement différentes !
La solution à ce problème consiste à empêcher ces variations directement dès la saisie.
Pour cela, nous pouvons utiliser une liste de référence en saisissant les régions autorisées :
Puis, nous allons utiliser ces cellules comme liste de référence.
Pour cela, nous sélectionnons les cellules de la colonne « Région », puis nous nous rendons dans « Données » > « Validation des données ».
Dans la zone « Autoriser », nous choisissons « Liste », puis dans « Source » nous indiquons :
=$I$9:$J$11
Nous validons avec « OK ».
Lorsque nous sélectionnons maintenant une cellule de la colonne Région, une petite flèche apparaît. Nous pouvons choisir la région au lieu de la saisir manuellement.
Attention toutefois : dans un véritable fichier amené à recevoir de nouvelles lignes, nous éviterons de limiter la validation à 3 cellules.
Nous verrons justement à la fin comment un tableau structuré permet de mieux gérer l'évolution de notre base.
Nous pouvons également placer toutes nos listes dans une feuille « Paramètres ». Cela évite d'avoir des données techniques à droite du tableau principal.
5. Ne pas inscrire les paramètres directement dans les formules
Ajoutons maintenant une colonne H intitulée « Prime ».
Imaginons que les commerciaux touchent une prime égale à 5 % du chiffre d'affaires. Dans H7, nous pourrions écrire :
=G7*5%
Nous recopions cette formule et tout fonctionne.
Pourtant, le « 5 % » est ce que nous appelons ici une valeur saisie en dur dans la formule.
Si le taux passe demain à 6 %, nous devons modifier nos formules.
Sur ce petit tableau, ce n'est pas très grave, mais dans un classeur contenant plusieurs feuilles et plusieurs calculs utilisant le même taux, nous pouvons facilement en oublier une.
Nous allons donc saisir « Taux de prime » dans J12 et « 5 % » dans J13.
Notre formule devient :
=G7*$J$13
Les signes « $ » sont importants. Ils transforment J13 en référence absolue : lorsque nous recopions la formule vers le bas, G7 devient G8, G9 puis G10, tandis que $J$13 reste toujours $J$13.
Pour ajouter rapidement ces signes, nous plaçons le curseur sur J13 dans la formule et appuyons sur [F4].
Maintenant, nous remplaçons 5 % par 6 % en J13 et toutes nos primes sont immédiatement recalculées.
Le principe à retenir est simple : lorsqu'une valeur peut évoluer, comme un taux, un objectif, une remise ou un coefficient, nous préférons généralement la stocker dans une cellule clairement identifiée plutôt que de la cacher au milieu d'une formule.
De plus cela permet de comprendre d’un seul coups d’œil comment sont calculées les primes, sans devoir forcément déchiffrer la formule utilisées.
6. L'erreur qui entretient les précédentes : ne pas utiliser de tableau structuré
Terminons par une amélioration qui va rendre notre base beaucoup plus simple à faire évoluer.
Pour l'instant, nos données sont stockées dans une plage classique A8.
Nous sélectionnons une cellule de cette plage puis nous utilisons le raccourci [Ctrl]+[L].
Excel détecte automatiquement la zone. Nous vérifions que « Mon tableau comporte des en-têtes » est coché puis nous validons avec « OK ».
Notre plage devient un tableau structuré.
Nous nous rendons dans l'onglet « Création de tableau » et, dans « Nom du tableau », nous remplaçons le nom proposé par Excel par « Ventes ».
Regardons maintenant notre formule de chiffre d'affaires. Excel peut utiliser les noms des colonnes plutôt que leurs coordonnées :
=[@Quantité]*[@[Prix unitaire]]
Le symbole « @ » signifie simplement que nous utilisons la valeur située sur la ligne courante.
Mais le principal intérêt apparaît lorsque nous ajoutons une nouvelle vente sous notre dernière ligne.
Nous saisissons une date dans la première cellule située juste sous le tableau. Excel étend automatiquement celui-ci et recopie les formules des colonnes calculées.
Nous pouvons également calculer le chiffre d'affaires total avec :
=SOMME(Ventes[CA])
Contrairement à une formule comme :
=SOMME(G9:G18)
Nous n'avons pas besoin de modifier la plage lorsque de nouvelles ventes sont ajoutées. La référence « Ventes[CA] » désigne toujours l'intégralité de la colonne CA du tableau.
Et voilà pourquoi le tableau structuré est généralement le meilleur point de départ pour une véritable base de données Excel.
