Construire un tableau Excel qui ne se casse pas quand on ajoute une ligne

Vous ajoutez une commande en bas de votre suivi, et le total ne bouge pas. Vous triez par date, et les montants se retrouvent en face des mauvais clients. Vous filtrez, et la moitié des lignes disparaît sans raison. Alors vous refaites le tableau, et un mois plus tard ça recommence.

Ce n'est pas vous. C'est la façon dont le tableau a été construit. Un tableau se casse toujours au même endroit, pour les mêmes cinq raisons, et chacune se corrige au moment de la construction en quelques secondes. Ce guide ne répare pas un fichier déjà abîmé : il montre comment ne plus y revenir.

En clair

Un tableur ne comprend qu'une seule chose : des lignes qui se ressemblent toutes. Une ligne = une commande, une heure travaillée, un adhérent. Une colonne = une seule information sur cette ligne.

Dès que vous vous écartez de ça pour faire joli, pour gagner de la place ou pour mettre un total au milieu, le logiciel ne sait plus où commence et où finit votre tableau. C'est là que tout se dérègle.

La règle qui évite la moitié des dégâts

Trois phrases, et vous avez déjà écarté la majorité des pannes :

1. Une seule ligne d'en-tête, tout en haut, avec un nom par colonne.
2. Une ligne par chose suivie, sans ligne vide entre elles.
3. Une seule information par colonne.

Voici le même suivi de commandes, écrit des deux façons.

Le tableau qui casse :

Le même, qui tient :

Ce qui casse dans le premier, concrètement :

• La colonne « Commande » mélange un numéro et un montant. Vous ne pourrez jamais faire la somme des montants : pour le tableur, CMD-104 : 340 € est une phrase, pas un nombre.
• La colonne « Client / Ville » mélange deux informations. Le jour où vous voudrez voir uniquement Nantes, il n'y aura rien à filtrer.
• La ligne vide coupe le tableau en deux. Sélectionnez une cellule et triez : le tri s'arrête à la ligne vide et laisse la suite derrière. C'est exactement le moment où les montants se retrouvent en face des mauvais clients.
• La ligne de total est dans les données. Triez par montant, et elle part se ranger au milieu. Filtrez, et elle s'additionne à elle-même.
• 340 € et 1 250 € sont du texte, pas des nombres. Le total a été tapé à la main, donc il sera faux à la prochaine commande.

Dans le second, le montant est un nombre nu (1250, sans symbole ni espace). L'euro s'ajoute par la mise en forme, pas en le tapant : sélectionnez la colonne, puis Accueil > Format de nombre > Monétaire. L'affichage est identique, mais la cellule reste un nombre calculable.

Les cellules fusionnées, le piège le plus coûteux

C'est le réflexe numéro un quand on veut un titre propre au-dessus de trois colonnes : on sélectionne, on clique sur Fusionner et centrer, c'est joli.

Et à partir de là, trois choses ne marchent plus :

• Le tri refuse de démarrer. Excel affiche « Cette opération exige que les cellules fusionnées soient identiques », et il n'y a pas d'échappatoire.
• Le filtre ne voit que la première ligne du bloc fusionné. Les autres lignes du groupe sont considérées comme vides et disparaissent du filtre.
• Les formules se trompent de cible. Une cellule fusionnée sur trois colonnes ne contient réellement une valeur que dans la première : les deux autres sont vides pour le calcul, même si l'écran montre le contraire.

L'alternative qui donne exactement le même rendu, sans aucun des dégâts : sélectionnez les cellules concernées, faites un clic droit puis Format de cellule, onglet Alignement, et dans la liste Horizontal choisissez Centré sur plusieurs colonnes. Le texte est centré visuellement sur la plage, mais chaque cellule reste une cellule à part entière.

Divergence honnête : cette option existe dans Excel. LibreOffice Calc et Google Sheets ne la proposent pas. Si vous travaillez avec eux, le conseil devient : ne fusionnez rien dans la zone de données, et mettez votre titre décoratif au-dessus du tableau, dans une cellule isolée, pas dedans.

Sortez les totaux des données

Un total posé juste sous la dernière ligne finit toujours par être avalé : vous ajoutez une commande, elle se glisse sous le total, et le total ne la compte pas.

Deux façons de s'en sortir, par ordre de solidité :

1. La ligne des totaux du tableau nommé (voir la section suivante) : elle se décale toute seule vers le bas quand vous ajoutez une ligne, sans jamais être confondue avec les données.
2. Un total posé ailleurs, au-dessus du tableau ou sur une autre feuille, qui pointe sur la colonne entière plutôt que sur une plage figée.

Dans les deux cas, le total est calculé, jamais tapé. Un total tapé à la main est un total faux qui attend son heure.

Le tableau nommé, la fonctionnalité que presque personne n'utilise

C'est la réponse la plus directe au titre de ce guide, et elle tient en un raccourci.

Cliquez n'importe où dans vos données, puis Accueil > Mettre sous forme de tableau (ou Insertion > Tableau). Excel vous demande de confirmer la plage et si la première ligne contient les en-têtes : dites oui. Le raccourci clavier de la version française est Ctrl+L.

Ce que vous venez de gagner, et qui est la raison d'être de ce guide :

• La plage grandit toute seule. Tapez sur la ligne juste en dessous : elle est absorbée dans le tableau. Les formules, les filtres, les couleurs et les graphiques qui s'appuient dessus suivent sans que vous touchiez à rien.
• Les formules se recopient seules vers le bas dans les nouvelles lignes.
• Les en-têtes restent visibles quand vous faites défiler, sans avoir à figer quoi que ce soit.
• Les formules deviennent lisibles. Nommez votre tableau (onglet Création de tableau qui apparaît quand le curseur est dedans, champ Nom du tableau, par exemple Commandes, sans espace). Votre somme s'écrit alors :

=SOMME(Commandes[Montant])

Cette formule ne dit pas « additionne de la ligne 2 à la ligne 47 ». Elle dit « additionne la colonne Montant des commandes ». Elle reste juste à la ligne 48, à la 400e, et le jour où vous insérez une colonne au milieu.

• La ligne des totaux s'active dans ce même onglet Création de tableau, case Ligne des totaux. Elle se place sous la dernière ligne et se décale à chaque ajout.

Chez les autres : LibreOffice Calc n'a pas cette fonctionnalité, son équivalent le plus proche est Données > Définir la plage, qui fait grandir la plage automatiquement mais ne donne ni les noms de colonnes dans les formules ni la ligne de totaux. Google Sheets a ajouté récemment une fonction de tableau comparable ; si votre menu Format ne propose rien de tel, la solution de repli qui marche partout est d'écrire vos formules sur la colonne entière, par exemple =SOMME(D:D), en gardant le total hors de cette colonne.

Les dates et les nombres qui n'en sont pas

Trois pannes très banales, et le même signal d'alerte pour les repérer.

Le signal : regardez l'alignement. Sans y toucher, un tableur colle les nombres et les dates à droite de la cellule, et le texte à gauche. Une date alignée à gauche n'est pas une date, c'est une phrase qui ressemble à une date. Elle ne se triera pas dans l'ordre chronologique et aucun calcul de délai ne marchera dessus. C'est le test le plus rapide qui existe, et il ne coûte rien.

Cas 1, la date qui n'en est pas. Typiquement une date saisie au format américain, ou collée depuis un site. Correctif : sélectionnez la colonne, Données > Convertir, cliquez Suivant deux fois, puis à la troisième étape choisissez Date et l'ordre réel des chiffres (JMA pour jour-mois-année). Validez : les dates basculent à droite.

Cas 2, le nombre stocké en texte. Il vient d'un copier-coller, d'un export, ou d'une apostrophe tapée par erreur devant le chiffre. Souvent un petit triangle vert apparaît dans le coin de la cellule. Deux vérifications utiles : le séparateur décimal doit être la virgule en configuration française (12,50 et non 12.50), et il ne doit pas y avoir d'espace parasite. Les fonctions qui réparent, en français :

=CNUM(B2)                  transforme un texte en nombre
=SUPPRESPACE(B2)           enlève les espaces en trop
=DATEVAL(B2)               transforme un texte en vraie date

Cas 3, les accents perdus en rouvrant un fichier CSV (Comma-Separated Values, un tableau enregistré en texte brut). Vous rouvrez l'export et les « é » sont devenus des suites de caractères illisibles. Ce n'est pas le fichier qui est abîmé, c'est la façon dont il est relu. La marche à suivre est détaillée dans la fiche Accents cassés en ouvrant un fichier CSV. Le principe à retenir dès la construction : si ce tableau doit vivre, gardez-le au format Excel, et ne passez par le CSV que pour échanger avec un autre logiciel.

Et si c'est votre formule qui s'affiche telle quelle au lieu de calculer, c'est une quatrième variante du même problème : la cellule est au format Texte. Voir Ma formule s'affiche en texte au lieu du résultat.

La formule de recherche, expliquée une fois pour toutes

Tout le monde la copie, presque personne ne sait pourquoi elle rend parfois n'importe quoi.

Le besoin : dans votre suivi de commandes vous avez un code client, et vous voulez afficher le nom qui va avec, tiré d'une autre feuille. En français, avec le point-virgule comme séparateur :

=RECHERCHEV(B2;Clients;2;FAUX)

À lire ainsi : cherche la valeur de B2, dans le tableau nommé Clients, et renvoie ce qui se trouve dans sa 2e colonne, en exigeant une correspondance exacte.

Le quatrième argument est tout le sujet. FAUX veut dire « exactement cette valeur ». Si vous mettez VRAI, ou si vous l'oubliez, la fonction accepte l'à-peu-près et vous renvoie la ligne la plus proche, sans le dire. C'est de là que viennent les tableaux qui affichent un nom plausible mais faux. Mettez toujours FAUX.

Quand elle ne trouve rien, elle affiche #N/A. Ce n'est pas un bug, c'est une réponse : ce code client n'existe pas dans votre liste. Ne le masquez pas bêtement, mais rendez-le lisible :

=SIERREUR(RECHERCHEV(B2;Clients;2;FAUX);"Code client inconnu")

Si votre version d'Excel propose RECHERCHEX, elle est plus simple et ne casse pas quand on insère une colonne :

=RECHERCHEX(B2;Clients[Code];Clients[Nom];"Code client inconnu")

Compatibilité : RECHERCHEV et SIERREUR existent partout, y compris dans LibreOffice Calc et Google Sheets, sous ces noms français exacts. RECHERCHEX est plus récente : elle manque dans les vieilles versions d'Excel et dans les vieilles versions de LibreOffice. Si votre tableau doit circuler, restez sur RECHERCHEV.

Attention si vous recopiez une formule trouvée sur un site anglophone : elle emploie la virgule comme séparateur et les noms anglais (VLOOKUP, IFERROR, FALSE). Collée telle quelle dans un tableur en français, elle ne fonctionnera pas.

Où ça casse, et quoi regarder

flowchart TD
    A["Mon tableau se dérègle"] --> B{"Le tri ou le filtre<br/>refuse de partir ?"}
    B -- Oui --> B1["Chercher les cellules fusionnées<br/>et les lignes vides dans les données"]
    B -- Non --> C{"Le total ignore<br/>les nouvelles lignes ?"}
    C -- Oui --> C1["Passer en tableau nommé<br/>et sortir le total des données"]
    C -- Non --> D{"Une colonne refuse<br/>de s'additionner ?"}
    D -- Oui --> D1["Regarder l'alignement :<br/>à gauche = c'est du texte"]
    D -- Non --> E{"Une recherche renvoie<br/>un mauvais nom ?"}
    E -- Oui --> E1["Le 4e argument doit être FAUX"]
    E -- Non --> F["Vérifier une ligne d'en-tête unique<br/>et une info par colonne"]

Erreurs fréquentes

• Fusionner les cellules d'en-tête pour faire propre. C'est le geste qui coûte le plus cher, parce qu'il casse le tri et le filtre pour toujours, et qu'on ne fait jamais le lien entre les deux. Utilisez Centré sur plusieurs colonnes.
• Taper le symbole euro ou l'unité dans la cellule. 340 € est du texte. Tapez 340 et laissez le format d'affichage poser l'euro.
• Laisser une ligne vide pour aérer entre deux groupes de lignes. Pour le tableur, la ligne vide est la fin du tableau. Pour aérer, augmentez la hauteur de ligne ou changez la couleur de fond.
• Poser le total juste sous la dernière ligne. La ligne suivante que vous ajouterez passera sous lui, donc hors du calcul.
• Écrire deux informations dans une colonne (le nom et la ville, la date et le statut). Séparer après coup est pénible ; séparer au départ ne coûte rien.

À retenir

• Une ligne d'en-tête, une ligne par chose suivie, une information par colonne. Le reste découle de là.
• Jamais de cellule fusionnée dans les données : Centré sur plusieurs colonnes donne le même rendu sans rien casser.
• Mettre sous forme de tableau (Ctrl+L) est ce qui fait grandir la plage toute seule. C'est le geste unique qui répond au titre de ce guide.
• L'alignement trahit tout : à gauche, c'est du texte, même si ça ressemble à une date ou à un nombre.
• En français, les arguments se séparent par un point-virgule, et RECHERCHEV se termine toujours par FAUX.

Ce que ça change pour vous

• Vous arrêtez de refaire le tableau tous les mois : il encaisse les ajouts sans intervention.
• Les chiffres que vous transmettez deviennent vérifiables, parce que les totaux sont calculés et non recopiés.
• Le jour où quelqu'un d'autre reprend le fichier, il le comprend sans vous appeler.

Mini-exercice

Ouvrez le tableau que vous refaites le plus souvent. Trois vérifications, cinq minutes :

1. Cliquez dans une cellule de données et lancez un tri sur une colonne. S'il refuse de partir, vous avez des cellules fusionnées : trouvez-les et défusionnez-les.
2. Regardez votre colonne de montants et votre colonne de dates. Alignées à gauche ? Alors ce ne sont pas des nombres ni des dates, et aucun de vos calculs n'est fiable.
3. Cliquez dans les données, faites Accueil > Mettre sous forme de tableau, puis ajoutez une ligne juste en dessous. Si le total suit tout seul, vous venez de régler le problème pour de bon.