Recopier une formule Excel : avec ou sans le signe dollar

Ami des uns, ennemi des autres, le signe « dollar » ($) simplifie la recopie des formules sous Excel. Vous le détestez vraiment ? Découvrez la fonction « Définir un nom » ! Vous voulez comprendre et maîtriser le symbole $ pour le placer à bon escient dans une formule ? GO !

Activ’Formation-express | Recopier des formules


>>>L’indispensable à savoir

Sous Excel, toute cellule est identifiée par une lettre de colonne (ou plusieurs) et un numéro de ligne.

Dans une formule,

– la cellule A1 renvoie Excel à l’intersection de la colonne A et de la ligne (comme dans le jeu « touché coulé » ou « bataille navale ») ;

– et BZ456 correspond à la cellule située à l’intersection de la colonne BZ et de la ligne 456.

Par défaut, quand on clique dans une cellule, le nom de la référence est visible dans la zone Nom (située à gauche de la barre de formule).

Excel reference cellule>>>Quand peut-on se passer du signe $ ?

On peut se passer du signe $ toutes les fois où la formule à recopier suit la logique : « ce qui est vrai dans la formule au-dessus ou à droite est vrai pour les cellules ou colonnes adjacentes ».

Exemples, dans une formule en ligne :

– la plage de cellule B3:E3 devient B4:E4 puis B5:E5, etc., car on additionne les totaux ligne après ligne ;

– et, dans une formule en colonne B3:B5 devient C3:C5 puis D3:D5, etc., car on additionne les totaux colonne après colonne.

On dit alors que les cellules s’incrémentent (B3 devient B4 puis B5) et qu’elles sont relatives.

excel recopier formule sans signe dollar

>>>Quand utiliser le signe $ ?

On utilise le signe $ (dollar) pour empêcher l’incrémentation totale ou partielle de la référence d’une cellule.

Blocage total de la référence d’une cellule

Dans cet exemple, les prix unitaires d’achat des produits doivent être multipliés par un taux fixe afin d’obtenir le prix de vente. Les cellules « prix unitaires » s’incrémentent (B4 devient B5 puis B6).

Quand un signe $ est placé devant la lettre de la colonne et le numéro de la ligne, la cellule est figée.

excel avec cellule figee $

Blocage partiel de la référence d’une cellule (en colonne ou en ligne)

Dans certains calculs, la recopie de la formule va devoir bloquer soit la référence à la ligne, soit la référence à la colonne, mais pas les deux.

Pour guider Excel, le signe $ sera placé soit devant la lettre pour bloquer la colonne (ex. : $A1), soit devant le numéro de la ligne pour bloquer la ligne (ex. A$1).

excel blocage semi absoluSur l’image ci-dessous, la formule inscrite en B3 utilise les références mixtes : le prix se trouve toujours en colonne A et le coefficient multiplicateur est toujours sur la ligne 2.

Le signe $ a donc été ajouté, dans la formule principale, devant le numéro de la colonne A ou le numéro la ligne 2 pour simplifier sa recopie.

Lors de la recopie vers le bas, puis vers la droite, 30*3 affiche bien 90€.

excel blocage partiel

Le schéma de « construction » à retenir

$A$1: la cellule est totalement figée (c’est une référence absolue).

$A1 : la colonne est bloquée, mais pas la ligne (c’est une référence semi-absolue en colonne).

A$1 : la ligne est bloquée mais pas la colonne (c’est une référence semi-absolue en ligne).

Dans la barre de formule, le signe $ peut être ajouté au clavier, ou à l’aide de la touche de fonction [F4]. Mode opératoire : sélectionner, dans la barre de formule, la référence de cellule concernée puis appuyer sur la touche de fonction [F4] : une pression A1 devient $A$1 ; deux pressions donne A$1 ; trois pressions donne $A1 et quatre pressions libère la cellule qui redevient A1.

>>>Peut-on remplacer le signe $ par une autre fonctionnalité ? Oui ! Mais…

Sous Excel (toute version), la fonction Nom (ou nommer) peut remplacer le signe $ quand il fige totalement la référence de la cellule (ou des cellules). Exemples : A1 devient $A$1 ; ou bien, A1:B4 devient $A$1:$B$4.

Méthodologie : sélectionner la cellule (ou la plage de cellules), saisir le nom à affecter à la cellule dans la zone Nom située à gauche de la Barre de formule, appuyer sur la touche [Entrée] du clavier. Ou bien, utiliser dans l’onglet FORMULES du ruban, l’icône Définir un nom (situées dans le groupe Noms définis).

Et le tour est joué ! Ici, dans toute formule à venir, le mot « taux » sera visible quand il fera référence à la cellule B1.

nommer une cellule sous Excel

Pour aller plus loin

Recopier une formule de RECHERCHE (V ou H) – Lire

Pourcentages de répartition et d’évolution sous Excel sans prise de tête – Lire