On a tous vécu ce moment : un tableau Excel construit en colonnes, avec des formules qui calculent des totaux, des moyennes ou des écarts, et la demande de le présenter en lignes pour un rapport. Le réflexe, c’est le collage spécial avec l’option Transposer. Sauf que le collage spécial ne conserve pas fidèlement les formules : il recolle les références relatives en les décalant, ce qui casse la logique de calcul.
Pour inverser un tableau Excel en gardant toutes vos formules intactes, il faut passer par la fonction TRANSPOSE ou par les nouvelles fonctions dynamiques.
Pourquoi le collage spécial casse vos formules lors de l’inversion
Quand on copie une plage contenant des formules et qu’on utilise Collage spécial > Transposer, Excel ne transpose pas les références de la même façon qu’il transpose les valeurs. Les références relatives sont recalculées selon leur nouvelle position dans la grille, pas selon la logique d’origine du tableau.
Concrètement, une formule qui pointait vers la cellule juste au-dessus (référence relative en ligne) va pointer vers la cellule à gauche après transposition. Si la structure du tableau source n’est pas symétrique, les résultats deviennent faux sans message d’erreur visible.
On se retrouve avec un tableau qui a l’air correct visuellement, mais dont les calculs sont décalés. C’est le piège principal, parce qu’il n’y a pas d’alerte. Il faut vérifier cellule par cellule, ce qui annule le gain de temps espéré.
Fonction TRANSPOSE dans Excel 365 : formules dynamiques sans Ctrl+Maj+Entrée
La fonction TRANSPOSE existe depuis longtemps, mais son comportement a changé radicalement avec Excel 365 et Excel 2021. Sur ces versions, TRANSPOSE s’appuie sur le moteur de formules à remplissage automatique (dynamic arrays). Une seule formule saisie dans une cellule de départ déborde automatiquement sur toute la plage transposée.
En pratique, on sélectionne une cellule vide, on tape =TRANSPOSE(B2:F10) et on valide avec Entrée. Pas besoin de présélectionner la zone cible, pas besoin de Ctrl+Maj+Entrée. Excel dimensionne la plage de sortie tout seul.
Ce que ça change pour les formules
Les formules transposées via cette méthode restent dynamiques. Si on modifie une valeur dans le tableau source, le tableau transposé se met à jour automatiquement. C’est le seul moyen fiable d’inverser un tableau Excel en gardant le lien vivant avec les données d’origine.
Mais il y a une contrainte à connaître : les plages à débordement ne fonctionnent pas dans les tableaux structurés (les fameux ListObjects qu’on crée avec Ctrl+T). Si votre tableau source est formaté en table Excel, la formule TRANSPOSE renverra une erreur #SPILL!. La solution : convertir temporairement la table en plage normale (clic droit sur la table > Convertir en plage), appliquer TRANSPOSE, puis reformater si nécessaire.
CHOOSEROWS et CHOOSECOLS pour inverser l’ordre des lignes ou colonnes
Inverser un tableau, ce n’est pas toujours transposer lignes en colonnes. Parfois, on veut simplement retourner l’ordre des lignes (afficher la dernière ligne en premier) ou réorganiser les colonnes sans changer l’orientation du tableau.
Depuis 2023-2024, Excel propose des fonctions dynamiques de réorganisation qui répondent à ce besoin :
- CHOOSEROWS permet de sélectionner et réordonner des lignes spécifiques d’une plage. En combinant avec SEQUENCE et ROWS, on peut inverser l’ordre complet des lignes d’un tableau.
- CHOOSECOLS fait la même chose pour les colonnes : on choisit l’ordre d’affichage sans toucher aux données sources.
- TAKE et DROP extraient ou suppriment un nombre défini de lignes ou colonnes depuis le début ou la fin d’une plage, ce qui permet de reconstituer un tableau inversé par blocs.
Ces fonctions créent une couche de réadressage par-dessus les données existantes. Les formules du tableau source continuent de fonctionner normalement, et le tableau réorganisé reflète leurs résultats en temps réel.
Exemple concret avec CHOOSEROWS
Pour inverser l’ordre des lignes d’une plage A2:D20, on entre dans une cellule vide :
=CHOOSEROWS(A2:D20, SEQUENCE(ROWS(A2:D20),,ROWS(A2:D20),-1))
SEQUENCE génère ici une suite décroissante (19, 18, 17… 1), et CHOOSEROWS affiche les lignes dans cet ordre. Toutes les formules contenues dans la plage source restent actives. Si une cellule de A2:D20 contient un SOMME ou un SI, le résultat transposé reste à jour.
Erreur #SPILL! et tableaux structurés : le blocage à anticiper
L’erreur #SPILL! est le problème le plus fréquent quand on tente d’utiliser TRANSPOSE ou CHOOSEROWS sur un tableau Excel formaté. Les formules à débordement sont incompatibles avec les tableaux structurés, et Excel ne propose pas de contournement automatique.
Voici les situations qui déclenchent cette erreur :
- La formule TRANSPOSE cible une plage qui fait partie d’un ListObject (table créée via Ctrl+T ou via l’onglet Insertion > Tableau).
- La zone de débordement chevauche des cellules déjà occupées par des données ou une autre formule dynamique.
- La plage de sortie tombe en dehors des limites de la feuille (cas rare, mais possible avec de très grands tableaux).
Pour éviter ce blocage, on convertit la table en plage classique avant d’appliquer la transposition. L’autre option consiste à placer la formule TRANSPOSE sur une feuille séparée, où aucune table structurée n’interfère avec la zone de débordement.
Quelle méthode choisir selon votre version d’Excel
Le choix de la méthode dépend directement de la version installée. Sur Excel 365 ou Excel 2021, privilégiez systématiquement les formules dynamiques (TRANSPOSE, CHOOSEROWS, CHOOSECOLS). Elles conservent le lien avec les données source et se mettent à jour automatiquement.
Sur Excel 2019 ou antérieur, TRANSPOSE fonctionne encore, mais en mode matriciel classique : il faut présélectionner la plage de destination exacte, saisir la formule, puis valider avec Ctrl+Maj+Entrée. Les fonctions CHOOSEROWS et CHOOSECOLS ne sont pas disponibles.
Dans tous les cas, le collage spécial avec Transposer reste utile uniquement quand on veut figer des valeurs sans conserver de formule. Pour tout le reste, les formules dynamiques sont la seule approche qui garantit l’intégrité des calculs après inversion.
Le point à retenir : avant de transposer, vérifiez si votre tableau est formaté en table structurée et quelle version d’Excel vous utilisez. Ces deux paramètres déterminent la méthode, et se tromper de méthode revient à reconstruire le tableau à la main.
