Vous souhaitez recopier une formule Excel pour faire évoluer la colonne lorsqu’on glisse vers le bas, tout en gardant la ligne figée — et l’inverse quand on glisse vers la droite ? Voici des méthodes simples, robustes et performantes, sans macro.
Contexte et objectif
Point de départ : vous partez d’une formule telle que ='Sheet 1'!B9. En recopie verticale (glisser la poignée de recopie vers le bas), vous voulez obtenir C9, puis D9, E9… c’est‑à‑dire faire varier la colonne alors que la ligne 9 reste fixe. À l’inverse, en recopie horizontale (vers la droite), vous voulez obtenir B10, B11, B12… donc faire évoluer la ligne tout en gardant la colonne B.
Le réflexe classique, ajouter des $ (références absolues), ne suffit pas : le signe dollar bloque un axe, mais n’inverse pas la logique native d’Excel (qui incrémente la ligne quand on descend et la colonne quand on va à droite). Il faut donc piloter l’axe à faire bouger à l’aide d’un compteur.
Ce que fait vraiment le signe $ dans Excel
Le $ fige l’axe qu’il précède ; il ne dit pas à Excel lequel des deux axes doit changer pendant la recopie. D’où la règle pratique :
| Forme | Effet lors de la recopie | Utilité |
|---|---|---|
B9 | Ligne et colonne bougent (référence relative) | Recopie « normale » |
B$9 | La ligne est figée (9) ; la colonne bouge | Empêche l’incrément de ligne en recopie verticale |
$B9 | La colonne est figée (B) ; la ligne bouge | Empêche l’incrément de colonne en recopie horizontale |
$B$9 | Rien ne bouge (référence absolue) | Constantes, paramètres, bornes de plages… |
Conclusion : pour « inverser » la logique de recopie (faire changer la colonne quand on descend, ou la ligne quand on va à droite), on doit fabriquer un compteur et l’injecter dans la partie colonne ou ligne de la référence. Les fonctions qui font un compteur naturel sont LIGNE(A1) (renvoie 1, puis 2, 3… en descendant) et COLONNE(A1) (renvoie 1, puis 2, 3… en allant à droite). Sur Excel en anglais, ce sont ROW et COLUMN. Dans les formules ci‑dessous, les arguments sont séparés par ; (séparateur français) ; si votre Excel attend une virgule, remplacez ; par ,.
Méthode recommandée : INDEX + LIGNE/COLONNE (non volatile, performante)
Avantages : rapide sur de gros classeurs, fonctionne dans toutes les versions modernes, claire à relire. Idée : demander à INDEX d’aller chercher la ligne fixe et une colonne qui progresse (ou l’inverse).
Recopie vers le bas en faisant évoluer la colonne (ligne 9 figée)
Placez la formule suivante dans la première cellule de sortie (peu importe où), puis recopiez vers le bas :
=INDEX('Sheet 1'!$1:$1048576; 9; 2 + LIGNE(A1) - 1)
9= la ligne fixe souhaitée.2= la colonne de départ (B = 2, C = 3, D = 4…).LIGNE(A1)-1vaut 0, 1, 2, 3… quand on descend, ce qui produit B, C, D, E… au fil de la recopie.'Sheet 1'!$1:$1048576est une plage « universelle » (toutes les lignes et colonnes de la feuille) queINDEXparcourt rapidement.
Exemple : si vous partez de B9, utilisez 2. Si vous vouliez partir de D9, remplacez 2 par 4.
Recopie vers la droite en faisant évoluer la ligne (colonne B figée)
Placez la formule dans la première cellule de sortie puis recopiez vers la droite :
=INDEX('Sheet 1'!$B:$B; COLONNE(A1) + 8)
$B:$B= la colonne figée.COLONNE(A1)vaut 1, 2, 3, 4… en allant à droite.- On ajoute
8parce que la première ligne voulue est9(8+1).
Variantes compactes avec SEQUENCE (Excel 365/2021)
Si vous avez plusieurs valeurs à renvoyer d’un coup, les matrices dynamiques permettent des déversements (spill) en une seule formule :
- Verticaliser n colonnes à partir de B9 :
=INDEX('Sheet 1'!$1:$1048576; 9; SEQUENCE(n; 1; 2; 1)) - Horizontaliser n lignes à partir de B9 :
=INDEX('Sheet 1'!$B:$B; SEQUENCE(1; n; 9; 1))
Remplacez n par le nombre de valeurs souhaité.
Méthode ultra lisible : DECALER (OFFSET) — à utiliser en connaissance de cause
DECALER (OFFSET) exprime exactement ce que l’on veut : partir d’un point et se décaler d’un certain nombre de lignes/colonnes. Elle est cependant volatile (recalcul à chaque modification), ce qui peut ralentir un classeur lourd. Si la taille de votre modèle est raisonnable, c’est parfait.
Recopie verticale en faisant progresser la colonne
=DECALER('Sheet 1'!$B$9; 0; LIGNE(A1)-1)
0= pas de décalage en ligne ; la ligne 9 reste fixe.LIGNE(A1)-1= 0, 1, 2… : on se déplace de 0, puis 1, puis 2 colonnes à droite (B → C → D…).
Recopie horizontale en faisant progresser la ligne
=DECALER('Sheet 1'!$B$9; COLONNE(A1)-1; 0)
COLONNE(A1)-1= 0, 1, 2… : on se décale d’autant de lignes (9 → 10 → 11…).0= pas de décalage en colonne ; la colonne B reste fixe.
Méthode souple : INDIRECT + ADRESSE (string‑based)
INDIRECT évalue la référence contenue dans un texte. Associée à ADRESSE (ADDRESS), on fabrique les coordonnées désirées (sans $) puis on préfixe par le nom de la feuille. C’est très flexible, mais volatile elle aussi.
Recopie verticale en faisant varier la colonne
=INDIRECT("'Sheet 1'!" & ADRESSE(9; 2 + LIGNE(A1)-1; 4))
ADRESSE(9; 2; 4)retourneB9(4 = style A1 sans$).- En remplaçant
2par2 + LIGNE(A1)-1, on fait défilerB9,C9,D9…
Recopie horizontale en faisant varier la ligne
=INDIRECT("'Sheet 1'!" & ADRESSE(9 + COLONNE(A1)-1; 2; 4))
- La colonne
2(B) reste fixe, la ligne devient 9, 10, 11…
Astuce : si le nom de la feuille contient un espace, gardez bien les guillemets simples : 'Sheet 1'.
Transposer une plage horizontale en colonne : solutions « matrices dynamiques »
Si vous connaissez la plage à transposer (par exemple 'Sheet 1'!B9:D9), la solution la plus simple est la fonction de feuille :
=TRANSPOSE('Sheet 1'!B9:D9)
Elle « déborde » verticalement toutes les valeurs (Excel 365/2021). En français, vous pouvez aussi voir TRANSPOSER selon les versions.
Besoin d’un transposé dynamique sans écrire explicitement la borne D9 ? Avec Excel 365, on peut combiner :
- Choisir une tranche de colonnes et la transposer :
=TRANSPOSE(CHOISIRCOLS('Sheet 1'!9:9; SEQUENCE(n; 1; 2; 1)))
oùnest le nombre de colonnes à convertir (à partir de B).
Ces approches évitent toute recopie manuelle : une seule formule suffit.
Le cœur du mécanisme : un compteur qui avance « dans le bon sens »
Souvenez‑vous de ces deux compteurs :
| Compteur | Valeurs en recopie | Usage typique |
|---|---|---|
LIGNE(A1)-1 | 0, 1, 2, 3… en descendant | Faire évoluer la colonne quand on recopie vers le bas |
COLONNE(A1)-1 | 0, 1, 2, 3… en allant à droite | Faire évoluer la ligne quand on recopie vers la droite |
Ensuite, injectez‑les au bon endroit : troisième paramètre d’INDEX (colonne) dans le premier cas, deuxième paramètre (ligne) dans le second cas, ou dans les décalages de DECALER.
Pas‑à‑pas illustré
- Sur la feuille Sheet 1, supposez que les cellules
B9:D9contiennent Q1, Q2, Q3. - Sur une autre feuille ou plus bas, placez en
A2la formule :=INDEX('Sheet 1'!$1:$1048576; 9; 2 + LIGNE(A1) - 1). - Recopiez vers le bas sur 3 lignes. Vous obtenez
Q1,Q2,Q3(B9, C9, D9). - Pour l’inverse (faire varier la ligne), placez en
A2:=INDEX('Sheet 1'!$B:$B; COLONNE(A1) + 8), puis recopiez vers la droite.
Tableau récapitulatif des formules clés
| Objectif | Formule | Explication |
|---|---|---|
Comprendre le rôle du $ | =B$9, =$B9, =$B$9 | Le $ fige uniquement l’axe indiqué ; il ne force pas l’autre axe à évoluer. |
| Transposer une plage horizontale en colonne (Excel 365/2021) | =TRANSPOSE('Sheet 1'!B9:D9) | Renvoie verticalement B9:D9 par « débordement » (spill). |
| Changer la colonne en recopiant vers le bas (recommandé) | =INDEX('Sheet 1'!$1:$1048576; 9; 2 + LIGNE(A1)-1) | LIGNE(A1) vaut 1, 2, 3… en descendant ; ajouté à 1 (puisqu’on part de la colonne 2 = B), on obtient B, C, D… |
| Même logique avec INDIRECT/ADRESSE | =INDIRECT("'Sheet 1'!" & ADRESSE(9; 2 + LIGNE(A1)-1; 4)) | Construit la référence texte (B9, C9…) et l’évalue. Utile si l’on doit aussi recopie horizontalement derrière. |
| Changer la ligne en recopiant vers la droite (recommandé) | =INDEX('Sheet 1'!$B:$B; COLONNE(A1)+8) | COLONNE(A1) croît (1, 2, 3…) quand on va à droite ; ajouté à 8, on obtient les lignes 9, 10, 11… |
| Raccourci pratique | Touche F4 pendant l’édition | Fait défiler les modes : relative → absolue ligne → absolue colonne → absolue ligne+colonne. |
Compatibilité et noms des fonctions selon la langue
Les captures et formules ci‑dessus utilisent les noms français (LIGNE, COLONNE, DECALER, ADRESSE) avec un point‑virgule comme séparateur. En anglais, utilisez ROW, COLUMN, OFFSET, ADDRESS. Dans certaines éditions, la fonction TRANSPOSE peut s’afficher TRANSPOSER.
Cas d’usage fréquents
- Transformer un en‑tête horizontal en liste verticale pour créer une validation de données ou un menu déroulant :
=TRANSPOSE('Sheet 1'!B9:Z9)dans Excel 365. - Construire une plage de recherche « qui grandit » : utilisez
INDEXavecSEQUENCEpour alimenter une recherche sans recopie. - Assembler un tableau récapitulatif où chaque ligne correspond à une colonne différente d’une feuille source (très courant en pilotage de budgets trimestriels).
Bonnes pratiques de performance
- Privilégiez
INDEXquand c’est possible : non volatile et scalable. - Utilisez
DECALERouINDIRECTavec parcimonie si votre classeur est volumineux (ce sont des fonctions volatiles). - Transformez vos zones en Tableaux (Ctrl+T) : les références structurées se mettent à jour d’elles‑mêmes quand on ajoute des colonnes/lignes.
- Évitez les plages « géantes » inutiles si votre modèle grandit ; remplacez
$1:$1048576par une plage plus courte dès que vous connaissez l’étendue réelle.
Raccourcis et astuces d’édition
- F4 en édition de cellule cycle les états de référence (
A1 → $A$1 → A$1 → $A1). - Sur Mac, si F4 est mappée au contrôle du volume/luminosité, utilisez Fn+F4 ou remappez dans les préférences clavier.
- Rechercher/Remplacer (Ctrl+H) : pour ajouter des
$en masse (par exemple, remplacer=par=$dans une sélection prudente). Vérifiez toujours le résultat.
Déclinaisons prêtes à l’emploi
Adaptez ces gabarits en ne changeant que la ligne fixe et/ou la colonne de départ :
| But | Gabarit | À remplacer |
|---|---|---|
| Colonne qui avance en recopie verticale | =INDEX('Sheet 1'!$1:$1048576; ligneFixe; colDepart + LIGNE(A1) - 1) | ligneFixe (ex. 9), colDepart (B=2, C=3…) |
| Ligne qui avance en recopie horizontale | =INDEX('Sheet 1'!$ColFixe:$ColFixe; ligneDepart - 1 + COLONNE(A1)) | ColFixe (ex. B), ligneDepart (ex. 9) |
| Version DECALER (vertical) | =DECALER('Sheet 1'!$ColFixe$LigneFixe; 0; LIGNE(A1)-1) | Colonne et ligne de départ |
| Version DECALER (horizontal) | =DECALER('Sheet 1'!$ColFixe$LigneFixe; COLONNE(A1)-1; 0) | Colonne et ligne de départ |
| Version INDIRECT/ADRESSE (vertical) | =INDIRECT("'Sheet 1'!" & ADRESSE(ligneFixe; colDepart + LIGNE(A1)-1; 4)) | Idem |
| Transposer une plage connue | =TRANSPOSE('Sheet 1'!ColDébLigneFixe:ColFinLigneFixe) | Colonnes de début/fin et ligne fixe |
Avec les Tableaux structurés (Ctrl+T)
Si vos données sont dans un Tableau Excel, vous pouvez utiliser les en‑têtes pour viser la ligne des en‑têtes (headers) et transposer proprement :
- Transposer les entêtes :
=TRANSPOSE(Table1[#En-têtes])(Excel 365) - INDEX sur colonne fixe :
=INDEX(Table1[MaColonne]; COLONNE(A1)+8)pour faire défiler les lignes 9, 10, 11… d’une colonne de tableau (en adaptant l’offset).
Pièges courants et dépannage
#VALEUR!ou#REF!: vérifiez la cohérence de la feuille et des bornes. AvecINDEX, une colonne ou une ligne 0 (hors usage avancé) produit des erreurs.#SPILL!avecTRANSPOSE: la zone de débordement n’est pas libre. Videz‑la ou placez la formule ailleurs.- Noms de feuilles avec espaces : encadrez par des apostrophes :
'Sheet 1'. - Séparateur d’arguments : si votre Excel utilise la virgule, remplacez tous les
;par,. - Performance : remplacez
DECALER/INDIRECTparINDEXsi vous sentez un ralentissement.
Questions fréquentes
Pourquoi =$B$9 ne répond pas à mon besoin ?
Parce qu’une référence absolue fige à la fois la colonne et la ligne ; rien n’évolue en recopie. Vous avez besoin d’une référence dont un axe progresse, ce que l’on construit avec un compteur (LIGNE ou COLONNE) intégré à INDEX ou DECALER.
Puis‑je inverser la logique dans les deux sens à partir de la même cellule ?
Oui. Utilisez deux formules, l’une bâtie sur LIGNE(A1) (pour recopie verticale) et l’autre sur COLONNE(A1) (pour recopie horizontale). Ou, si vous êtes en 365, construisez une matrice avec SEQUENCE et déversez‑la d’un coup.
Que se passe‑t‑il si je dépasse la colonne Z (AA, AB…) ?
Aucun problème : les approches numériques (INDEX, DECALER, ADRESSE avec un numéro de colonne) gèrent toutes les colonnes au‑delà de Z ; vous n’avez pas à manipuler vous‑même les lettres.
INDIRECT casse‑t‑il quand j’insère des colonnes ?INDIRECT construit une adresse à partir de nombres (ex. colonne 2 = B) ; insérer une colonne avant B ne change pas la numérotation absolue : la colonne 2 reste B. En revanche, INDIRECT est volatile ; préférez INDEX si la performance est une priorité.
Exemples concrets supplémentaires
Créer un planning en convertissant un « ruban » horizontal en liste
Supposez une feuille « Sheet 1 » avec les dates en B9:AG9 (1er au 32 du mois). Dans une feuille « Planning », mettez :
=TRANSPOSE('Sheet 1'!B9:AG9)
Vous obtenez instantanément une liste verticale de dates, exploitable par RECHERCHEX (XLOOKUP) ou par un segment de pivot.
Construire un tableau récapitulatif par trimestre
Variables trimestrielles en 'Sheet 1'!B9:E9 (T1 à T4). Dans A2 : =INDEX('Sheet 1'!$1:$1048576; 9; 2 + LIGNE(A1)-1) et recopiez sur 4 lignes pour obtenir T1 → T4. Ajoutez une colonne d’année et utilisez Insérer > Tableau croisé dynamique pour synthétiser par année/trimestre.
Checklist rapide
- Vous voulez faire bouger la colonne en descendant ? Utilisez
INDEX(...; ligneFixe; colDépart + LIGNE(A1)-1)ouDECALER(...; 0; LIGNE(A1)-1). - Vous voulez faire bouger la ligne en allant à droite ? Utilisez
INDEX(ColFixe; COLONNE(A1)+offset)ouDECALER(...; COLONNE(A1)-1; 0). - Plage connue à transposer ?
TRANSPOSE(TRANSPOSER) est votre ami (Excel 365/2021). - Besoin d’une solution express mais votre classeur est léger ?
DECALERest la plus intuitive. - Performance et robustesse d’abord ? Préférez
INDEX.
Annexe : équivalents anglais et français des fonctions utilisées
| Français | Anglais | Rôle ici |
|---|---|---|
| LIGNE | ROW | Compteur vertical (1, 2, 3…) |
| COLONNE | COLUMN | Compteur horizontal (1, 2, 3…) |
| INDEX | INDEX | Récupère la valeur à la ligne/colonne demandée |
| DECALER | OFFSET | Se déplace depuis une cellule de base |
| ADRESSE | ADDRESS | Construit une adresse texte (ex. « B9 ») |
| INDIRECT | INDIRECT | Évalue une adresse texte en référence réelle |
| TRANSPOSE / TRANSPOSER | TRANSPOSE | Transpose les lignes en colonnes et inversement |
| SEQUENCE / SÉQUENCE | SEQUENCE | Génère des séquences 1..n pour déversement |
Synthèse opérationnelle
Pour faire évoluer la colonne en recopiant vers le bas depuis ='Sheet 1'!B9 : privilégiez =INDEX('Sheet 1'!$1:$1048576; 9; 2 + LIGNE(A1)-1) ou, si vous préférez une écriture « par le geste », =DECALER('Sheet 1'!$B$9; 0; LIGNE(A1)-1). Pour faire évoluer la ligne en recopiant vers la droite avec la colonne B figée : =INDEX('Sheet 1'!$B:$B; COLONNE(A1)+8) ou =DECALER('Sheet 1'!$B$9; COLONNE(A1)-1; 0). Lorsque la plage est connue et contiguë (ex. B9:D9) et que vous êtes sur Excel 365/2021, TRANSPOSE vous donnera le résultat attendu en une seule formule, sans recopie.
Rappel utile : les références absolues restent indispensables pour des bornes de recherche (RECHERCHEV/RECHERCHEX), des plages de tableaux, ou des constantes. Les matrices dynamiques (SEQUENCE, CHOISIRLIGNES/CHOISIRCOLS) peuvent générer la matrice finale sans recopie et simplifier fortement les modèles récents.
Note de localisation : si votre Excel utilise la virgule comme séparateur d’arguments, remplacez systématiquement ; par , dans les formules proposées. Les noms de fonctions peuvent apparaître en français ou en anglais selon votre installation.