Dans Excel, la fonction Transposer et le collage spécial permettent de basculer des colonnes en lignes (et inversement). Le problème survient dès que le tableau source contient des couleurs de cellules, des bordures, des polices personnalisées ou des mises en forme conditionnelles : aucune méthode native ne transpose données et formats en une seule opération. Comprendre ce que chaque technique conserve, et ce qu’elle perd, évite de reconstruire manuellement la mise en page après chaque transposition.
Collage spécial avec transposition dans Excel : ce qui est conservé et ce qui ne l’est pas
La méthode la plus courante pour inverser colonnes et lignes repose sur le collage spécial. On sélectionne la plage source, on la copie, puis on choisit une cellule de destination et on coche l’option Transposer dans le menu Collage spécial.
A voir aussi : Mise en forme de tableaux Excel : techniques et astuces essentielles
Cette manipulation copie les valeurs, les formules recalculées et une partie de la mise en forme directe (couleur de fond, police, taille). Les bordures sont partiellement transférées, mais leur orientation peut changer de manière inattendue, car une bordure droite sur une cellule en colonne devient une bordure inférieure une fois transposée en ligne.
En revanche, les mises en forme conditionnelles ne suivent pas. Si le tableau source affiche des cellules en rouge lorsque la valeur dépasse un seuil, ces règles restent attachées à la plage d’origine. La plage transposée affiche les bonnes couleurs au moment du collage (puisqu’elles sont copiées comme format direct), mais les règles conditionnelles ne se mettront plus à jour si les données changent.
A lire aussi : Touches échangées ? inverser clavier en quelques secondes
Fonction TRANSPOSE et dynamic arrays dans Excel 365
La fonction TRANSPOSE fonctionne différemment du collage spécial. Saisie sous la forme =TRANSPOSE(A1:D10) dans une cellule de destination, elle crée un lien dynamique vers la plage source. Chaque modification de la source se répercute automatiquement dans la zone transposée.
Dans Excel 365, les dynamic arrays simplifient la saisie : il suffit de valider la formule dans une seule cellule pour que le résultat se déverse sur les cellules adjacentes. Plus besoin de sélectionner à l’avance la plage de destination ni de valider avec Ctrl+Maj+Entrée comme dans les versions antérieures.
Limite principale : aucun format visuel n’est copié
La fonction TRANSPOSE ne transfère que les valeurs et les formules. La cellule de destination hérite du format par défaut de la feuille, pas de celui de la source. Couleurs, polices, bordures : tout est perdu.
Pour retrouver un rendu visuel cohérent, deux pistes existent :
- Appliquer manuellement le même style ou thème de tableau à la plage transposée, ce qui fonctionne pour des formats simples mais devient fastidieux sur un tableau complexe.
- Créer des règles de mise en forme conditionnelle sur la plage de destination qui reproduisent la logique visuelle de la source (seuils de couleur, barres de données). Cette approche simule la conservation des formats sans réellement les copier.
- Combiner TRANSPOSE avec des noms définis utilisant GET.CELL pour récupérer certaines propriétés de mise en forme, une technique avancée documentée dans les guides Excel 365 mais peu accessible aux utilisateurs occasionnels.
Power Query pour transposer des données Excel volumineuses
Power Query propose une transposition par le menu Transform, puis Transpose (ou via Unpivot/Pivot pour des restructurations plus fines). Cette méthode traite efficacement des plages de plusieurs milliers de lignes sans saturer le presse-papiers.
Power Query recharge les données dans une table propre, sans aucun format visuel. Les styles, les couleurs de cellules et les mises en forme conditionnelles du tableau source ne sont pas conservés. La table de sortie adopte le style par défaut d’un tableau Excel structuré.
L’intérêt de Power Query réside ailleurs : la requête est reproductible. Si le fichier source est mis à jour chaque mois, un simple rafraîchissement de la requête re-transpose les données sans intervention manuelle. La mise en forme, elle, peut être gérée une seule fois via un modèle de tableau personnalisé appliqué à la sortie.
Macro VBA pour inverser colonnes et lignes en gardant les formats
La seule méthode capable de transposer données et formats en une opération est une macro VBA dédiée. Le principe consiste à parcourir chaque cellule de la plage source, lire sa valeur, puis copier explicitement ses propriétés visuelles vers la cellule transposée.
Propriétés à copier dans la macro
Une macro de transposition complète doit gérer plusieurs attributs au-delà de la simple valeur :
Valuepour le contenu de la cellule, etNumberFormatpour le format d’affichage (date, pourcentage, nombre décimal).Interior.ColoretInterior.Patternpour la couleur de fond,Font.Color,Font.Bold,Font.Sizepour la police.Borders(chaque côté individuellement, avec style et épaisseur) etFormatConditionspour les mises en forme conditionnelles, qui nécessitent une boucle dédiée car leurs règles doivent être recréées sur la plage de destination.
Ce type de macro demande quelques dizaines de lignes de code. Le résultat est le seul à reproduire fidèlement l’apparence du tableau source après inversion des colonnes et des lignes, y compris les bordures, les couleurs personnalisées et les formats conditionnels.
Précaution avant exécution
Avant de lancer la macro, dupliquer l’onglet source reste une bonne pratique. Une erreur dans la plage de destination (chevauchement avec des données existantes, par exemple) peut écraser des cellules sans possibilité d’annulation via Ctrl+Z, car les macros VBA ne s’inscrivent pas dans l’historique d’annulation d’Excel.
Choisir la bonne méthode selon le besoin de mise en forme
| Méthode | Données | Formats directs | Formats conditionnels | Lien dynamique |
|---|---|---|---|---|
| Collage spécial + Transposer | Oui | Partiel | Non | Non |
| Fonction TRANSPOSE | Oui | Non | Non | Oui |
| Power Query | Oui | Non | Non | Oui (rafraîchissement) |
| Macro VBA | Oui | Oui | Oui | Non |
Pour un tableau simple avec peu de mise en forme, le collage spécial suffit. Pour des données liées qui évoluent fréquemment, la fonction TRANSPOSE combinée à une mise en forme conditionnelle recréée sur la destination offre un bon compromis. Pour un tableau richement formaté où chaque couleur et bordure compte, seule une macro VBA garantit une transposition complète des formats. Le choix dépend moins du volume de données que du degré de personnalisation visuelle du tableau source.
