- Quand on recopie une formule, Excel décale les références : B4 devient B5, puis B6. C’est la référence relative, utile pour les lignes d’un tableau.
- Le signe dollar fige la colonne, la ligne ou les deux : $B$1 reste B1 partout, B$2 fige la ligne seulement, $A3 fige la colonne seulement.
- La touche F4 ajoute les dollars en faisant défiler les quatre combinaisons ; c’est le geste à retenir de cet épisode.
- L’oubli du dollar produit des résultats faux sans message d’erreur : zéros, montants démesurés ou #VALEUR! sur les lignes suivantes.
La recopie est la grande force d’Excel : on écrit une formule une fois et on l’étend à des milliers de lignes. Mais elle repose sur un mécanisme qu’il faut maîtriser, sans quoi les résultats sont faux sans que rien ne le signale. Ce mécanisme tient en un symbole, le dollar, et vous le tapez avec une seule touche : F4.
Ce que vous saurez faire à la fin de l’épisode
- expliquer ce qui change dans une formule quand on la recopie ;
- choisir entre référence relative, absolue et mixte selon le besoin ;
- appliquer un taux unique (TVA, remise, coefficient) à tout un tableau ;
- construire une grille à double entrée avec une seule formule ;
- diagnostiquer un tableau dont les résultats « dérivent » après recopie.
La référence relative : par défaut, tout bouge
Quand vous écrivez =B4*C4 dans la cellule D4, Excel ne mémorise pas « la cellule B4 », il mémorise « la cellule située deux colonnes à gauche, sur la même ligne ». Recopiée en D5, la formule devient =B5*C5 ; recopiée en E4, elle devient =C4*D4. On appelle cela une référence relative.
C’est exactement ce qu’on veut pour un tableau de lignes : chaque ligne calcule à partir de ses propres valeurs. Mais ce n’est pas ce qu’on veut quand une des valeurs est commune à toutes les lignes.
Le cas : appliquer un taux de TVA unique
Atlas Négoce prépare le prix TTC de cinq équipements. Le taux de TVA, 20 %, est saisi une seule fois, en B1. Les prix hors taxes sont en colonne B, à partir de la ligne 4. On veut calculer la TVA en colonne C.
Figure 1 : en C4, =B4*$B$1. Le prix (repère 2) est relatif, le taux (repère 1) est figé par les dollars.
La formule de C4 est =B4*$B$1. Recopiée vers le bas, elle devient :
C5 : =B5*$B$1C6 : =B6*$B$1- et ainsi de suite.
B4 se décale d’une ligne à chaque recopie, $B$1 reste immobile. Le résultat est celui attendu : TVA de 1 700,00 pour l’ordinateur portable (8 500 × 20 %), 330,00 pour l’écran, 580,00 pour l’imprimante, 240,00 pour l’onduleur et 156,00 pour le routeur. Si le taux passe un jour à 10 % pour un produit, on le change à un seul endroit et tout se recalcule.
L’erreur à connaître : oublier le dollar
Voyons ce qui se passe si l’on écrit =B4*B1 (sans dollars) en C4 et qu’on recopie.
Figure 2 : sans dollar, le « taux » glisse d’une ligne à chaque recopie.
La première ligne est correcte (1 700,00), parce qu’elle a été saisie à la main. Mais dès la deuxième :
| Ligne | Formule obtenue | Ce qu’elle calcule | Résultat |
|---|---|---|---|
| C5 | =B5*B2 |
1 650 × cellule vide | 0,00 |
| C6 | =B6*B3 |
2 900 × texte « Prix HT » | #VALEUR! |
| C7 | =B7*B4 |
1 200 × 8 500 | 10 200 000,00 |
| C8 | =B8*B5 |
780 × 1 650 | 1 287 000,00 |
Remarquez les quatre comportements différents : un zéro (cellule vide), une erreur (texte), puis deux montants démesurés. Dans un grand tableau, le risque est que seules certaines lignes soient visiblement fausses et que les autres passent pour plausibles. D’où le réflexe de contrôle : cliquez sur une cellule de la dernière ligne recopiée et lisez sa formule.
Les quatre formes d’une adresse
| Écriture | Nom | Colonne | Ligne | Quand l’utiliser |
|---|---|---|---|---|
B1 |
Relative | bouge | bouge | Calcul propre à chaque ligne |
$B$1 |
Absolue | fixe | fixe | Taux, coefficient, total de référence |
B$1 |
Mixte (ligne figée) | bouge | fixe | En-tête de colonne qu’on recopie vers le bas |
$B1 |
Mixte (colonne figée) | fixe | bouge | Libellé de ligne qu’on recopie vers la droite |
Le dollar se lit comme « ne pas toucher à ce qui le suit ». $B$1 : ni la colonne B ni la ligne 1 ne bougent.
Le geste à retenir : F4. En saisissant la formule, juste après avoir cliqué sur la cellule du taux, appuyez sur F4 : B1 devient $B$1. Un deuxième appui donne B$1, un troisième $B1, un quatrième revient à B1. Vous n’avez jamais besoin de taper un dollar au clavier.
Les références mixtes : une grille avec une seule formule
Les références mixtes sont idéales pour les tableaux à double entrée. Atlas Négoce veut une grille donnant la TVA selon le taux (en lignes : 5 %, 10 %, 15 %, 20 % ; ce sont des taux d’essai, le CGI 2026 ne retenant que 20 % et 10 %) et la base hors taxes (en colonnes : 1 000, 5 000, 10 000 et 50 000 MAD).
Figure 3 : la grille entière est calculée par la formule de la première cellule, recopiée vers la droite puis vers le bas.
En B3, on saisit =B$2*$A3 :
B$2: la ligne 2 est figée (c’est la ligne des bases), la colonne bouge. Recopiée vers la droite, la formule pointe surC$2,D$2,E$2; recopiée vers le bas, elle reste sur la ligne 2.$A3: la colonne A est figée (c’est la colonne des taux), la ligne bouge. Recopiée vers le bas, elle pointe sur$A4,$A5,$A6; recopiée vers la droite, elle reste sur la colonne A.
Une formule, seize cellules correctes : 50,00 pour 5 % de 1 000, jusqu’à 10 000,00 pour 20 % de 50 000. En C4 (cellule sélectionnée sur la figure), la formule est =C$2*$A4, c’est-à-dire 5 000 × 10 % = 500,00.
C’est la même logique qui servira pour des tableaux de bord (mois en colonnes, comptes en lignes), des grilles d’amortissement ou des tables de sensibilité.
Faire référence à une cellule d’une autre feuille
Dans un vrai dossier (épisode 1), le taux de TVA ne se trouve pas sur la feuille de calcul mais sur l’onglet Paramètres. La formule s’écrit alors =B4*Paramètres!$B$5 : le nom de la feuille, un point d’exclamation, puis l’adresse (figée). Pour la saisir sans erreur, tapez =B4*, cliquez sur l’onglet Paramètres, cliquez sur la cellule du taux, appuyez sur F4, puis Entrée. Si le nom de la feuille contient des espaces, Excel ajoute des apostrophes ('Taux et barèmes'!$B$5). À l’épisode 10, nous irons plus loin en donnant un nom au taux (TauxTVA), pour écrire simplement =B4*TauxTVA.
Copier ou couper : ce que deviennent les références
Il existe un autre piège, souvent confondu avec le précédent :
- Copier-coller (
Ctrl + C,Ctrl + V) d’une formule : les références relatives sont décalées comme pour une recopie. - Couper-coller (
Ctrl + X,Ctrl + V) d’une cellule : la cellule déplacée garde ses formules intactes, et les formules qui pointaient vers elle suivent. Rien n’est décalé. - Insérer ou supprimer une ligne : les références sont ajustées automatiquement. Supprimer la ligne d’une cellule référencée produit
#REF!dans les formules qui l’utilisaient.
À vous de jouer
Ouvrez le classeur d’exercice.
- Onglet TVA : en
C4, saisissez=B4*puis cliquez surB1et appuyez surF4. Validez, recopiez jusqu’enC8. EnD4, saisissez=B4+C4, recopiez. - Contrôle : la somme des TVA doit valoir 20 % de la somme des prix hors taxes.
- Onglet Erreur : cliquez sur
C7et lisez sa formule. Pourquoi donne-t-elle 10 200 000,00 ? Corrigez les cellulesC5:C8avec$B$1. - Onglet Grille : saisissez
=B$2*$A3enB3, recopiez vers la droite jusqu’enE3, puis vers le bas jusqu’enE6. Vérifiez la celluleE6.
Correction. Prix HT : 8 500 + 1 650 + 2 900 + 1 200 + 780 = 15 030,00 ; TVA : 1 700 + 330 + 580 + 240 + 156 = 3 006,00, soit bien 20 % de 15 030. Sur l’onglet Erreur, C7 contient =B7*B4 : elle multiplie 1 200 par le prix de l’ordinateur (8 500), d’où 10 200 000 ; il fallait =B7*$B$1. Sur l’onglet Grille, E6 vaut 10 000,00 (20 % de 50 000), et chaque ligne de la grille est proportionnelle au taux de la colonne A.
À retenir
- Recopier, c’est décaler les références. Le dollar fige ce qu’on ne veut pas voir bouger.
$B$1fige tout,B$1fige la ligne,$B1fige la colonne ;F4bascule entre les quatre formes.- Un taux, un coefficient ou un total de référence se saisit une seule fois et se désigne en absolu.
- Pour un tableau à double entrée : ligne figée pour la tête de colonnes, colonne figée pour la tête de lignes.
- Un résultat de recopie se contrôle en lisant la formule de la dernière ligne.
Demain, épisode 5 : les fonctions de base (SOMME, MOYENNE, MIN, MAX, NB), et la question qui fâche les comptables, l’arrondi au centime près.
Questions fréquentes
Comment saisir le signe dollar rapidement ?
Placez le curseur sur l’adresse dans la barre de formule ou dans la cellule en cours de saisie, puis appuyez sur F4 (sur certains ordinateurs portables, Fn + F4). Chaque appui fait passer de B1 à $B$1, puis B$1, puis $B1, puis revient à B1.
Quand faut-il utiliser une référence absolue ?
Dès qu’une formule recopiée doit toujours pointer sur la même cellule : un taux de TVA, un coefficient, un total servant de dénominateur pour un pourcentage, la plage d’un tableau de correspondance dans une fonction de recherche.
Pourquoi ne pas mettre des dollars partout par précaution ?
Parce qu’une formule entièrement figée ne s’adapte plus quand on la recopie : on obtient la même valeur sur toutes les lignes. Le bon réflexe est de se demander, pour chaque adresse, si elle doit suivre la recopie ou rester fixe.
La référence absolue protège-t-elle si j’insère une ligne ?
Non, c’est une autre question. Le dollar n’agit que lors de la recopie ; l’insertion ou la suppression de lignes ajuste toujours les références vers les cellules déplacées, qu’elles soient absolues ou relatives.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.