Ce qui se passe quand vous recopiez une formule
Dans Excel, une référence écrite sans signe dollar est relative : elle est mémorisée comme une position par rapport à la cellule qui la contient. Recopier =B4*C4 de D4 vers D5 donne =B5*C5. La documentation Microsoft détaille le mécanisme : copiée deux cellules vers le bas et vers la droite, A1 devient C3, A$1 devient C$1, $A1 devient $A3 et $A$1 reste $A$1.
Le problème apparaît quand une partie de la formule ne devait pas bouger : un taux unique, une cellule de total, une plage de recherche. Si cette référence est restée relative, la copie l'emmène avec elle, et le symptôme est discret. Une plage décalée d'une ligne renvoie encore un nombre ; un total divisé par la mauvaise cellule affiche encore un pourcentage.
Une relecture en quatre passes, dans cet ordre
L'ordre compte : on part du plus large vers le plus fin, au lieu d'examiner un classeur entier cellule par cellule.
L'objectif n'est pas de tout vérifier, mais de regarder les endroits où un décalage peut se cacher sans produire d'erreur visible.
- Les extrémités : lisez dans la barre de formule la première et la dernière cellule de chaque colonne de formules. Les bornes de plage se cassent souvent là.
- La vue d'ensemble : activez Afficher les formules, dans l'onglet Formules. La colonne devient lisible d'un coup d'œil, et une référence qui dépasse ou s'arrête trop tôt se repère.
- Les antécédents : sur une cellule suspecte, utilisez Repérer les antécédents. Les flèches bleues désignent des cellules sans erreur, les rouges une cellule à l'origine d'une erreur ; un nouveau clic affiche le niveau suivant.
- Le durcissement : revenez sur les références qui devaient rester fixes, sélectionnez-les dans la barre de formule et basculez entre les types de référence avec F4.
Choisir le type de référence avant de copier
Une partie de la relecture disparaît si le choix est fait au moment d'écrire la formule. Le repère est simple : demandez-vous ce qui doit suivre la copie, et dans quel sens.
Le tableau se lit en pensant à la direction de la recopie : vers le bas, vers la droite, ou les deux.
| Écriture | Comportement à la copie | Cas d'usage typique |
|---|---|---|
| B4 (relative) | La colonne et la ligne suivent la copie | Un calcul ligne par ligne sur les données voisines |
| $B$4 (absolue) | Rien ne bouge, dans aucun sens | Un paramètre unique du classeur, un total de référence |
| B$4 (ligne figée) | La colonne suit, la ligne reste | Une ligne d'en-têtes ou de coefficients, recopiée vers le bas |
| $B4 (colonne figée) | La ligne suit, la colonne reste | Une colonne de libellés ou de bases, recopiée vers la droite |
Mise en situation fictive : le tableau de marge d'un atelier imaginaire
Exemple entièrement fictif, construit pour l'entraînement : n'y saisissez aucune information confidentielle ni donnée personnelle. Un classeur imaginaire suit vingt références d'un atelier fictif, avec les quantités en colonne B, les prix d'achat en C et les prix de vente en D. La cellule G1 contient un taux de charges fixé pour l'exercice. En E4, la formule =(D4-C4)*B4-B4*G1 est juste. Recopiée jusqu'en ligne 23, G1 devient G2 en ligne 5, G3 en ligne 6, puis descend dans des cellules vides : la soustraction porte sur zéro et les marges affichées deviennent trop élevées, de façon régulière et crédible.
Déroulez la relecture sur ce fichier fictif. La dernière cellule, E23, renvoie à G20 au lieu de G1 : le décalage est confirmé. L'affichage des formules montre la colonne G qui défile ligne après ligne, et Repérer les antécédents sur E6 trace une flèche vers une cellule vide, ce que la valeur affichée ne laissait pas deviner. Correction : écrire $G$1, puis recopier de nouveau sur toute la plage.
Les erreurs fréquentes au moment de la relecture
Les décalages ne résistent pas à une méthode ; ils résistent aux habitudes de relecture. Voici celles qui laissent passer le plus de références déplacées.
Une remarque sur le contrôle d'incohérence : Excel compare une formule au motif de ses voisines et signale celle qui s'en écarte, par exemple A4*B2 dans une série A1*B1, A2*B2, A3*B3. Si une seule recopie a appliqué le même décalage à toute la colonne, le motif reste homogène et rien n'est signalé. Ce type de relecture fait partie de ce que l'on travaille dans la formation Maîtriser Microsoft Excel.
| Situation | Erreur fréquente | Action |
|---|---|---|
| Les résultats semblent plausibles | Conclure que les formules sont bonnes | Relire les formules : première et dernière cellule de chaque colonne |
| Aucune erreur affichée | Juger tout contrôle inutile | Vérifier les antécédents des cellules clés |
| Un paramètre isolé dans une cellule | Le référencer sans dollar, puis recopier | Figer la référence avec F4 avant toute recopie |
| Une plage de recherche recopiée | Ne contrôler que la première ligne | Vérifier les deux bornes sur la dernière ligne recopiée |
| Correction faite sur une seule cellule | Laisser le reste de la colonne en place | Recopier la formule corrigée, puis refaire la passe des extrémités |
Questions fréquentes
Comment savoir rapidement si une référence s'est décalée ?
Lisez dans la barre de formule la première et la dernière cellule de la colonne, puis comparez-les. Si une référence qui devait rester fixe a changé de ligne ou de colonne, le décalage est confirmé.
À quoi sert Repérer les antécédents ?
Cette commande de l'onglet Formules affiche des flèches depuis les cellules qui alimentent la formule sélectionnée, en bleu pour les cellules sans erreur et en rouge pour celles à l'origine d'une erreur. Un nouveau clic affiche le niveau suivant.
Le contrôle des formules incohérentes suffit-il ?
Non : Excel signale une formule lorsqu'elle ne suit pas le motif de ses voisines, ce qui vise la cellule modifiée isolément. Un décalage appliqué uniformément par une seule recopie produit un motif homogène, donc aucun signal.
Quand faut-il figer une référence avec le signe dollar ?
Dès qu'une partie de la formule désigne un élément qui ne doit pas suivre la copie : paramètre unique, total, plage de recherche, ligne d'en-têtes. Sélectionnez la référence dans la barre de formule et basculez entre les écritures avec F4.
Repères et limites
Cet article porte sur la relecture des références après une recopie de formule. Les libellés de commandes et le comportement des touches peuvent différer selon la version d'Excel et le système utilisé ; la documentation Microsoft reste la référence sur ce point. La méthode décrite ne remplace ni une validation métier des chiffres ni un contrôle par une seconde personne.
Sources et ressources
Donnez une suite concrète à votre projet.
Consultez le parcours « Maîtriser Microsoft Excel » et ses modalités pour préparer votre échange avec notre équipe.
Demander le programme par e-mailVotre messagerie s’ouvre avec la formation et l’article préremplis. Envoyez ensuite votre demande à romaingilles@hotmail.com.
Consulter le programme →