Verrouiller une cellule avec des références absolues

Il est très fréquent de devoir faire pointer de nombreuses formules vers une même valeur partagée : un taux de taxe, un taux de change, un objectif ou des frais fixes.

Cette valeur unique se trouve dans une cellule, et toutes les autres formules doivent continuer à y faire référence, quel que soit l'emplacement où elles sont copiées. Une référence absolue permet cela.

Une référence absolue utilise un signe dollar devant la colonne et la ligne, comme dans $A$1. Elle est entièrement verrouillée.

Copiez n'importe où dans la feuille une formule contenant $A$1 : cette partie pointera toujours vers la cellule A1. Seules les références non verrouillées de la formule se déplaceront.

Imaginez que le taux de taxe sur les ventes, 0.08, se trouve dans la cellule E1. Les totaux des commandes sont dans la colonne B. Pour ajouter la taxe à chaque commande, écrivez dans C2 :

=B2*$E$1

B2 est relative et suit donc chaque ligne, tandis que $E$1 est absolue et lit toujours le taux de taxe.

Recopiez la formule de C2 vers C2, C3, C4 et observez ce qui arrive à chaque partie :

• B2 devient B3, puis B4 (relative, elle se déplace)
• $E$1 reste $E$1 à chaque fois (absolue, elle reste fixe)

La ligne 3 contient donc =B3*$E$1 et la ligne 4 contient =B4*$E$1. Le taux de taxe n'est jamais perdu.

Oubliez les signes dollar et écrivez =B2*E1. Lorsqu’elle est recopiée vers le bas, E1 devient E2, puis E3, qui sont vides.

Votre colonne d’imposition se remplit de zéros. Les nombres semblent incorrects, mais la cause est simple : la référence du taux était relative alors qu’elle aurait dû être absolue.

Pour convertir rapidement E1 en $E$1, cliquez sur E1 dans la barre de formule et appuyez une fois sur F4. Excel insère les deux signes dollar à votre place.

C’est beaucoup plus rapide et moins sujet aux erreurs que de saisir les symboles manuellement, surtout dans les formules longues.

Supposons que $B$1 contienne un taux de conversion de USD vers EUR de 0,92 et que les montants en dollars descendent dans la colonne A. Convertissez chacun d’eux avec :

=A2*$B$1

Recopiez cette formule sur des centaines de lignes : chaque conversion utilise le même taux. Mettez à jour le taux dans B1 une seule fois et toute la colonne est recalculée instantanément.

Comme ses deux parties sont verrouillées, une référence absolue reste fixe, que vous la recopiiez vers le bas, vers la droite ou dans les deux directions.

Recopiez =B2*$E$1 vers la droite : elle devient =C2*$E$1. Le B devient C, mais $E$1 ne bouge toujours pas. Le verrouillage fonctionne dans toutes les directions.

Les références absolues sont également très utiles dans les fonctions. Pour trouver la part de chaque valeur par rapport à un total fixe situé dans B1 :

=A2/$B$1

Ou pour comparer chaque note à un objectif stocké dans D1 :

=IF(A2>=$D$1,"Pass","Fail")

La cellule verrouillée reste le point de référence sur chaque ligne.

Le véritable avantage d’une référence absolue est de disposer d’un point de contrôle unique. Comme chaque formule lit la même cellule verrouillée, la modification de cette cellule met toute la colonne à jour en une seule fois.

Faites passer le taux d’imposition de E1 de 0,08 à 0,09 : chaque formule =B2*$E$1 est recalculée instantanément. Inutile de modifier des dizaines de formules : une seule entrée les pilote toutes.

Les professionnels placent souvent leurs constantes, leurs taux et leurs objectifs dans une petite zone identifiée en haut de la feuille, puis les référencent de manière absolue.

Vos formules restent ainsi lisibles et vous pouvez modifier une seule entrée pour mettre à jour tout le rapport. C’est le signe dollar qui permet à chaque formule de rester dirigée vers cette entrée.

Vérifiez votre compréhension des références absolues.

Une référence absolue comme $A$1 verrouille à la fois la colonne et la ligne : elle pointe donc toujours vers la même cellule lorsqu’elle est recopiée.

• Utilisez-la pour les valeurs communes comme les taux, les frais et les objectifs
• Combinez-la avec des références relatives afin que seules les bonnes parties se déplacent
• Appuyez sur F4 pour ajouter instantanément les deux signes dollar

Ensuite, vous verrouillerez une seule partie à la fois avec des références mixtes.