Dans cet article, vous trouverez plusieurs façons de convertir des lignes en colonnes (ou des colonnes en lignes), peu importe comment vous l’appelez, c’est la même chose : ) Ces solutions fonctionnent dans toutes les versions d’Excel 2016, 2013, 2010, 2007 et inférieures, couvrir de nombreux scénarios possibles et expliquer les erreurs les plus courantes.

Convertir des lignes en colonnes dans Excel à l’aide de Collage spécial

Supposons que vous ayez un ensemble de données similaire à ce que vous voyez dans la partie supérieure des graphiques ci-dessous. Les noms de pays sont organisés en colonnes, mais la liste des pays est trop longue, nous ferions donc mieux de changer les colonnes en lignes pour que le tableau tienne dans l’écran :

Pour passer des lignes aux colonnes, procédez comme suit :

1. Sélectionnez les données d’origine. Pour sélectionner rapidement l’ensemble du tableau, c’est-à-dire toutes les cellules contenant des données dans une feuille de calcul, appuyez sur Ctrl + Accueil puis Ctrl + Maj + Fin.
2. Copiez les cellules sélectionnées soit en cliquant avec le bouton droit de la souris sur la sélection et en choisissant Copier dans le menu contextuel, soit en appuyant sur Ctrl + C.
3. Sélectionnez la première cellule de la plage de destination.

Assurez-vous de sélectionner une cellule qui se situe en dehors de la plage contenant vos données d’origine, afin que les zones de copie et de collage ne se chevauchent pas. Par exemple, si vous avez actuellement 4 colonnes et 10 lignes, le tableau converti aura 10 colonnes et 4 lignes.

4. Cliquez avec le bouton droit sur la cellule de destination et choisissez Collage spécial dans le menu contextuel, puis sélectionnez Transposer . 

Noter. Si vos données sources contiennent des formules, veillez à utiliser correctement les références relatives et absolues selon qu’elles doivent être ajustées ou rester verrouillées sur certaines cellules.

Comme vous venez de le voir, la fonction Collage spécial vous permet d’effectuer des transformations ligne à colonne (ou colonne à ligne) littéralement en quelques secondes. Cette méthode copie également le formatage de vos données d’origine, ce qui ajoute un argument de plus en sa faveur.

Cependant, cette approche présente deux inconvénients qui l’empêchent d’être qualifiée de solution parfaite pour transposer des données dans Excel :

• Il n’est pas bien adapté à la rotation de tableaux Excel entièrement fonctionnels . Si vous copiez l’intégralité du tableau, puis ouvrez la boîte de dialogue Collage spécial , vous constaterez que l’ option Transposer est désactivée. Dans ce cas, vous devez soit copier le tableau sans en-têtes de colonne, soit le convertir d’abord en plage.
• Collage spécial > Transposer ne lie pas le nouveau tableau aux données d’origine, il ne convient donc qu’aux conversions ponctuelles. Chaque fois que les données source changent, vous devez répéter le processus et faire pivoter à nouveau la table. Personne ne voudrait perdre son temps à convertir les mêmes lignes et colonnes encore et encore, n’est-ce pas ?

Comment transposer un tableau et le lier aux données d’origine

Voyons comment vous pouvez passer des lignes aux colonnes à l’aide de la technique familière Collage spécial , mais connecter la table résultante à l’ensemble de données d’origine. La meilleure chose à propos de cette approche est que chaque fois que vous modifiez les données dans la table source, la table inversée reflétera les modifications et sera mise à jour en conséquence.

1. Copiez les lignes que vous souhaitez convertir en colonnes (ou les colonnes à changer en lignes).
2. Sélectionnez une cellule vide dans la même feuille de calcul ou dans une autre.
3. Ouvrez la boîte de dialogue Collage spécial, comme expliqué dans l’exemple précédent et cliquez sur Coller le lien dans le coin inférieur gauche :

Vous obtiendrez un résultat similaire à celui-ci :

4. Sélectionnez le nouveau tableau et ouvrez la boîte de dialogue Rechercher et remplacer d’Excel pour accéder directement à l’onglet Remplacer.
5. Remplacez tous les caractères “=” par “xxx” ou tout autre caractère qui n’existe nulle part dans vos données réelles.
   

Cela transformera votre table en quelque chose d’un peu effrayant, comme vous le voyez dans la capture d’écran ci-dessous, mais ne paniquez pas, il suffit de 2 étapes supplémentaires et vous obtiendrez le résultat souhaité.

6. Copiez le tableau avec les valeurs “xxx”, puis utilisez Collage spécial> Transposer pour retourner les colonnes en lignes
7. Enfin, ouvrez la boîte de dialogue Rechercher et remplacer une fois de plus pour annuler la modification, c’est-à-dire remplacer tous les “xxx” par “=” pour restaurer les liens vers les cellules d’origine.

C’est une solution un peu longue mais élégante, n’est-ce pas ? Le seul inconvénient de cette approche est que le formatage d’origine se perd dans le processus et vous devrez le restaurer manuellement (je vais vous montrer un moyen rapide de le faire plus loin dans ce tutoriel).

Comment transposer dans Excel à l’aide de formules

Un moyen plus rapide de convertir dynamiquement des colonnes en lignes dans Excel consiste à utiliser la formule TRANSPOSE ou INDEX/ADDRESS. Comme dans l’exemple précédent, ces formules conservent également les connexions aux données d’origine mais fonctionnent un peu différemment.

Changer les lignes en colonnes dans Excel à l’aide de la fonction TRANSPOSE

Comme son nom l’indique, la fonction TRANSPOSE est spécialement conçue pour transposer des données dans Excel :

=TRANSPOSE(tableau)

Dans cet exemple, nous allons convertir un autre tableau qui répertorie les États américains par population :

1. Comptez le nombre de lignes et de colonnes dans votre tableau d’origine et sélectionnez le même nombre de cellules vides, mais dans l’autre sens.

Par exemple, notre exemple de tableau comporte 7 colonnes et 6 lignes, en-têtes compris. Étant donné que la fonction TRANSPOSE changera les colonnes en lignes, nous sélectionnons une plage de 6 colonnes et 7 lignes.

2. Avec les cellules vides sélectionnées, tapez cette formule :

=TRANSPOSE(A1:G6)

3. Puisque notre formule doit être appliquée à plusieurs cellules, appuyez sur Ctrl + Maj + Entrée pour en faire une formule matricielle .

Voilà, les colonnes sont converties en lignes, exactement comme nous le voulions :

Avantages de la fonction TRANSPOSE :

Le principal avantage de l’utilisation de la fonction TRANSPOSE est que la table pivotée conserve la connexion à la table source et que chaque fois que vous modifiez les données source, la table transposée change en conséquence.

Faiblesses de la fonction TRANSPOSE :

• La mise en forme du tableau d’origine n’est pas enregistrée dans le tableau converti, comme vous le voyez dans la capture d’écran ci-dessus.
• S’il y a des cellules vides dans le tableau d’origine, les cellules transposées contiendront 0 à la place. 
• Vous ne pouvez modifier aucune cellule du tableau pivoté car il dépend beaucoup des données source. Si vous essayez de modifier une valeur de cellule, vous vous retrouverez avec l’erreur “Vous ne pouvez pas modifier une partie d’un tableau”.

En conclusion, quelle que soit la qualité et la facilité d’utilisation de la fonction TRANSPOSE, elle manque certainement de flexibilité et n’est donc peut-être pas la meilleure solution dans de nombreuses situations.

Convertir ligne en colonne avec les fonctions INDIRECT et ADRESSE

Dans cet exemple, utilisera une combinaison de deux fonctions, ce qui est un peu délicat. Faisons donc pivoter un tableau plus petit pour mieux nous concentrer sur la formule.

Supposons que vous ayez des données dans 4 colonnes (A – D) et 5 lignes (1 – 5) :

Pour convertir les colonnes en lignes, procédez comme suit :

1. Entrez la formule ci-dessous dans la cellule la plus à gauche de la plage de destination, disons A7, et appuyez sur le Entrer:

=INDIRECT(ADDRESSE(COLONNE(A1); LIGNE(A1)))

2. Copiez la formule vers la droite et vers le bas dans autant de lignes et de colonnes que nécessaire en faisant glisser la petite croix noire dans le coin inférieur droit des cellules sélectionnées : 

Dans votre table nouvellement créée, toutes les colonnes sont commutées en lignes.

Si vos données commencent dans une ligne autre que 1 et une colonne autre que A, vous devrez utiliser une formule un peu plus complexe :

=INDIRECT(ADRESSE(COLONNE(A1)-COLONNE($A$1) + LIGNE($A$1); LIGNE(A1) – LIGNE($A$1) + COLONNE($A$1)))

Où A1 est la cellule la plus à gauche de votre table source. Veillez également à utiliser des références de cellules absolues et relatives.

Cependant, les cellules transposées semblent très simples et ternes, par rapport aux données d’origine :

Mais ne soyez pas déçu, ce problème peut être facilement résolu. Pour restaurer la mise en forme d’origine, voici ce que vous faites :

• Copiez le tableau d’origine.
• Sélectionnez la table résultante.
• Cliquez avec le bouton droit sur le tableau résultant et choisissez Options de collage / Formatage .

Avantages : Cette formule offre un moyen plus flexible de transformer des lignes en colonnes dans Excel. Il permet d’apporter des modifications au tableau transposé car vous utilisez une formule régulière et non une formule matricielle.

Lacunes : je n’en vois qu’une – le formatage des données ordinales est perdu. Cependant, vous pouvez le restaurer rapidement, comme indiqué ci-dessus.

Comment fonctionne cette formule

Maintenant que vous savez comment utiliser la combinaison INDIRECT / ADRESSE, vous souhaiterez peut-être avoir un aperçu de ce que fait réellement la formule.

Comme son nom l’indique, la fonction INDIRECT est utilisée pour référencer indirectement une cellule. Mais le vrai pouvoir d’INDIRECT est qu’il peut transformer n’importe quelle chaîne en référence, y compris une chaîne que vous construisez en utilisant d’autres fonctions et les valeurs d’autres cellules. Et c’est exactement ce que nous allons faire. Si vous suivez cela, vous comprendrez tout le reste avec facilité )

Comme vous vous en souvenez, nous avons utilisé 3 autres fonctions dans la formule – ADRESSE, COLONNE et LIGNE.

La fonction ADDRESS obtient l’adresse de la cellule par les numéros de ligne et de colonne que vous spécifiez, respectivement. N’oubliez pas l’ordre : première – ligne, deuxième – colonne.

Dans notre formule, nous fournissons les coordonnées dans l’ordre inverse, et c’est ce qui fait l’affaire ! En d’autres termes, cette partie de la formule ADDRESSE(COLONNE(A1) ; LIGNE(A1)) échange les lignes en colonnes, c’est-à-dire prend un numéro de colonne et le change en numéro de ligne, puis prend un numéro de ligne et le transforme en colonne numéro.

Enfin, la fonction INDIRECT produit les données pivotées.