Figer des cellules dans Excel repose sur un mécanisme simple en apparence : le signe dollar ($) placé devant la lettre de colonne, le numéro de ligne, ou les deux. La différence entre une référence relative, absolue et mixte détermine pourtant le comportement de chaque formule dès qu’elle est recopiée. Comprendre précisément ce qui bouge et ce qui reste fixe évite la majorité des erreurs de calcul dans un tableau.
Comportement des références Excel lors d’une recopie : tableau comparatif
Le point de départ, c’est la recopie. Quand vous glissez une formule vers le bas ou vers la droite, Excel ajuste automatiquement les références relatives. Il laisse intactes les références verrouillées par un dollar. Voici ce que donne chaque type de référence quand une formule est recopiée depuis la cellule D2.
A lire également : Automatiser des tâches répétitives grâce aux macros dans Excel
| Type de référence | Syntaxe dans D2 | Recopie vers D3 (bas) | Recopie vers E2 (droite) | Élément figé |
|---|---|---|---|---|
| Relative | A2 | A3 | B2 | Aucun |
| Absolue | $A$2 | $A$2 | $A$2 | Colonne et ligne |
| Mixte (ligne figée) | A$2 | A$2 | B$2 | Ligne uniquement |
| Mixte (colonne figée) | $A2 | $A3 | $A2 | Colonne uniquement |
Ce tableau résume la logique complète. Le dollar fige l’élément qu’il précède, rien d’autre. Si vous retenez cette règle, le reste en découle.
A lire en complément : Enlever des doublons sur Excel : différence entre filtre avancé et suppression
Référence absolue Excel : quand figer colonne et ligne simultanément
La référence absolue (type $A$2) sert à pointer vers une cellule unique qui ne doit jamais changer, quelle que soit la direction de la recopie. Le cas classique : appliquer un taux de TVA stocké dans une cellule à toute une colonne de prix.
Prenons un tableau de prix HT en colonne B et un taux de TVA en cellule E1. La formule en C2 serait =B2*$E$1. En la recopiant vers C3, C4, etc., B2 glisse naturellement vers B3, B4 (c’est le comportement relatif souhaité), tandis que $E$1 reste verrouillé sur la cellule du taux.
Sans le double dollar, la référence E1 deviendrait E2 dès la deuxième ligne, pointant vers une cellule probablement vide. Le résultat : une colonne de zéros ou d’erreurs.
Basculer rapidement avec la touche F4
Dans la barre de formule, cliquez sur la référence à modifier et appuyez sur F4 pour basculer entre les quatre types de références. Chaque pression fait défiler le cycle : A2 → $A$2 → A$2 → $A2 → A2. Sur de nombreux ordinateurs portables récents, la rangée F est pré-assignée aux fonctions multimédia (luminosité, volume). Il faut alors utiliser Fn + F4 pour accéder au raccourci Excel.
Référence mixte dans Excel : figer la ligne ou la colonne séparément
La référence mixte est la moins utilisée, alors qu’elle résout un problème fréquent : les tableaux à double entrée. Deux variantes existent.
- $A2 : la colonne A est figée, la ligne 2 s’adapte lors d’une recopie vers le bas. Utile quand la formule doit toujours lire une colonne fixe (par exemple, une colonne de quantités) tout en descendant ligne par ligne.
- A$2 : la ligne 2 est figée, la colonne A s’adapte lors d’une recopie vers la droite. Utile quand la formule doit toujours lire une ligne fixe (par exemple, une ligne de coefficients) tout en parcourant les colonnes.
- Combiner les deux dans une même formule permet de créer un tableau croisé complet avec une seule formule recopiée en bloc, sans aucune correction manuelle.
Cas concret : table de multiplication à double entrée
Supposons les valeurs 1 à 10 en ligne 1 (B1 à K1) et en colonne A (A2 à A11). En B2, la formule =$A2*B$1 fonctionne pour l’ensemble du tableau. $A2 verrouille la colonne A (les multiplicandes à gauche) tout en laissant la ligne libre. B$1 verrouille la ligne 1 (les multiplicateurs en haut) tout en laissant la colonne libre.
Recopiez cette unique formule sur toute la plage B2:K11 : chaque cellule affiche le bon produit. Aucune référence absolue complète n’est nécessaire ici, et une référence purement relative produirait un décalage immédiat.
Références structurées dans les Tables Excel : l’alternative aux dollars
Les versions récentes d’Excel (Microsoft 365, Excel 2021 et suivantes) encouragent l’utilisation des Tables Excel créées avec Ctrl+T. Une fois les données converties en Table, les formules n’affichent plus des adresses comme B2 ou $B$2, mais des références structurées du type Ventes[@Montant] ou Ventes[Montant].
Ces références se comportent comme des références relatives intelligentes : elles s’adaptent automatiquement quand vous insérez, supprimez ou triez des lignes. Le risque de décalage lié à un dollar oublié disparaît.
En revanche, les références structurées ne couvrent pas tous les cas. Dès que vous travaillez en dehors d’une Table ou que vous construisez un tableau croisé personnalisé, les références mixtes et absolues restent le seul levier de contrôle.
Erreurs fréquentes lors du verrouillage de cellules Excel
La plupart des formules cassées après recopie viennent de trois situations récurrentes.
- Oublier le dollar sur une cellule de paramètre (taux, coefficient, seuil) qui doit rester fixe. Le symptôme : des résultats corrects sur la première ligne, puis des valeurs aberrantes en dessous.
- Placer un double dollar ($A$2) là où une référence mixte suffirait. Dans un tableau à double entrée, une référence absolue empêche tout ajustement et produit la même valeur dans chaque cellule.
- Confondre la direction du verrouillage. $A2 fige la colonne, A$2 fige la ligne. Inverser les deux donne un résultat visuellement similaire sur la première cellule, mais divergent dès la recopie.
Le réflexe le plus fiable reste de tester la formule sur deux cellules adjacentes (une vers le bas, une vers la droite) avant de recopier sur l’ensemble du tableau. Si les deux résultats sont corrects, le verrouillage est bon.
Le signe dollar dans Excel n’a rien d’un détail cosmétique. Choisir entre référence relative, absolue ou mixte, c’est décider ce qui bouge et ce qui reste fixe dans chaque formule. La prochaine fois qu’une recopie produit des résultats incohérents, vérifiez d’abord les dollars : dans la grande majorité des cas, le problème se trouve là.
