Vous écrivez une formule, vous la tirez vers le bas, et les résultats sont faux. Le plus souvent, la cause est une confusion entre référence relative et référence absolue. Beaucoup d’utilisateurs parlent de « valeur relative » et de « valeur absolue », mais Excel raisonne en réalité sur des références de cellules. Cet article explique ce que chacune fait, quand l’utiliser et comment éviter les pièges classiques.
La référence relative : le comportement par défaut
Une référence relative s’écrit sans symbole particulier : A1, B2, C10. Elle s’ajuste en fonction de l’endroit où la formule est copiée.
Exemple concret : calculer un montant par ligne
En D2, saisissez =B2*C2, puis tirez la poignée de recopie jusqu’en D4. Chaque ligne calcule son propre total (42 €, 48 €, 47,20 €). La relativité est ici notre alliée : chaque ligne doit utiliser ses propres données.
La référence absolue : verrouiller une cellule avec le signe $
Une référence absolue ne bouge jamais, quelle que soit la destination de la copie. On la reconnaît à ses deux signes dollar : $A$1. Le premier verrouille la colonne, le second verrouille la ligne.
Exemple concret : appliquer un taux de TVA unique
Prenons l’exemple du montant hors taxe en D2:D6 et un taux de TVA de 20 % saisi en C6.
Si vous écrivez en E2 =D2*C6 puis que vous tirez vers le bas, voici ce qui se passe :
- en E3, Excel écrit
=7 ;D3*C - en E4, il écrit
=8.D4*C
Les cellules E3, E4… sont vides : Excel les lit comme 0.
La bonne formule est =D2*$C$6). Le taux reste verrouillé sur C6 pour toutes les lignes, tout le tableau se met à jour d’un coup. Voilà l’intérêt principal de la référence absolue : centraliser un paramètre (taux, coefficient, objectif, devise) dans une seule cellule plutôt que de l’écrire en dur dans chaque formule.
La référence mixte : verrouiller seulement la ligne ou la colonne
C’est la notion la moins connue, et pourtant l’une des plus puissantes. Une référence mixte ne fige qu’un des deux axes :
$B2: la colonne est fixe, la ligne varie ;C$1: la ligne est fixe, la colonne varie.
Exemple concret :
La formule saisie dans la cellule C2 est :
=$B2*(1+C$1)
Formule à saisir dans C2, puis à recopier vers la droite et vers le bas.
$B2
Référence mixte : colonne B bloquée
Le symbole $ devant B empêche la colonne de changer. Le numéro de ligne reste relatif et peut changer lors de la recopie verticale.
C$1
Référence mixte : ligne 1 bloquée
Le symbole $ devant 1 empêche la ligne de changer. La colonne reste relative et peut changer lors de la recopie horizontale.
Comment la formule fonctionne-t-elle ?
Prenons le prix HT de l’ordinateur, qui est de 500, et le taux de TVA de 7 %.
La formule en C2 est :
=$B2*(1+C$1)
Le prix TTC est donc de 535.
Lorsque tu recopies la formule, Excel adapte automatiquement les références selon le déplacement.
Ce mécanisme sert aussi pour une grille tarifaire croisée, un tableau de commissions par vendeur et par trimestre, ou un échéancier de prêt.
Le raccourci à connaître : F4
Inutile de taper les signes $ à la main. Placez le curseur sur une référence dans la barre de formule, puis appuyez sur F4 (Windows) ou ⌘ + T (Mac). Chaque pression fait défiler quatre états :
A1 → $A$1 → A$1 → $A1 → A1
Selon votre clavier, il peut falloir ajouter la touche Fn pour activer F4.
Récapitulatif : laquelle choisir ?
| Type | Syntaxe | Se comporte comme | Usage typique |
|---|---|---|---|
| Relative | A1 | S’adapte dans les deux sens | Calculs ligne par ligne |
| Absolue | $A$1 | Ne bouge jamais | Taux, coefficient, total de référence |
| Mixte (colonne fixe) | $A1 | Fixe en colonne, libre en ligne | Tableaux croisés |
| Mixte (ligne fixe) | A$1 | Fixe en ligne, libre en colonne | Tableaux croisés |
Pour décider, posez-vous une question simple avant de recopier : « Quand je déplace cette formule, cette cellule doit-elle suivre ou rester en place ? » Si elle doit suivre, laissez en relatif. Si elle doit rester, verrouillez avec $.
Les erreurs les plus courantes
1. Oublier le verrouillage. C’est le cas du taux de TVA évoqué plus haut. Contrôlez toujours la dernière ligne de votre recopie : si le résultat est incohérent, cliquez dessus et regardez les adresses dans la barre de formule.
2. Tout verrouiller par réflexe. Écrire $B$2*$C$2 dans une colonne de totaux donne le même résultat sur chaque ligne, puisque tout est figé sur la première ligne.
3. Verrouiller le mauvais axe. Avec une référence mixte, $A2 et A$2 ne produisent pas du tout le même effet. Si votre tableau croisé affiche des valeurs décalées, c’est souvent là qu’il faut regarder.
4. Déplacer une cellule avec couper-coller. Les références de formule qui pointent vers elle sont mises à jour. Avec copier-coller, ce n’est pas le cas pour les références absolues, qui continuent de désigner l’ancienne cellule. Gardez cette différence en tête quand vous réorganisez une feuille.
Pour aller plus loin : nommer ses cellules
Une référence absolue comme $E$1 n’est pas très parlante pour quelqu’un qui relit votre fichier. Vous pouvez donner un nom à la cellule : sélectionnez E1, tapez TVA dans la zone de nom (à gauche de la barre de formule) et validez. Votre formule devient =B2*(1+TVA). Un nom défini est absolu par défaut, il se recopie sans souci et rend le classeur bien plus lisible.
À retenir
- La référence relative (
A1) suit la formule quand on la recopie. - La référence absolue (
$A$1) reste fixée sur une cellule précise. - La référence mixte (
$A1ouA$1) ne fige que la colonne ou la ligne. - F4 (ou ⌘ + T sur Mac) permet de passer d’un type à l’autre sans quitter le clavier.
- Un paramètre qui sert dans plusieurs formules doit être saisi une seule fois dans une cellule dédiée, puis appelé en absolu.
Retrouvez la vidéo explicative ci-dessous :
téléchargez le fichier Excel de démonstration pour reproduire l’exemple et vous entraîner à utiliser les références mixtes.