Excel Transponeerhandleiding: rijen omzetten naar kolommen en omgekeerd

Data transponeren in Excel laat je de oriëntatie van je gegevens omdraaien: rijen worden kolommen en kolommen worden rijen. Deze techniek is vooral handig wanneer je je dataset wilt herstructureren voor betere analyse, visualisatie of rapportage.

Of je nu data voorbereidt voor een draaitabel, een samenvatting maakt of simpelweg de lay-out wilt veranderen, Excel biedt drie hoofdmethoden om je data te transponeren:

1. Plakken speciaal - Een snelle, eenmalige conversie

2. TRANSPOSE()-functie - Een dynamische, formulegebaseerde aanpak

3. Power Query - Een geavanceerde methode voor grotere datasets

In dit artikel leer je stap voor stap hoe je elke methode gebruikt, wanneer je ze toepast en waar je op moet letten. Als je nieuw bent in Excel, nodigen we je uit om onze cursus Data Analysis in Excel te verkennen, waar je basisbewerkingen zoals draaitabellen en logische functies leert. 

Snel antwoord

Hoewel Excel meerdere manieren biedt om data te transponeren, zoals in de introductie genoemd, is de snelste manier Plakken speciaal:

• Selecteer en kopieer je data (Ctrl + C).

• Klik met de rechtermuisknop op de doelcel waar je de getransponeerde data wilt plaatsen.

• Selecteer "Plakken speciaal".

• Vink het vakje "Transponeren" aan.

• Klik op OK.

Dit draait direct je rijen naar kolommen en kolommen naar rijen, en biedt een snelle oplossing voor de meeste transponeerbehoeften.

Wat betekent transponeren in Excel?

Transponeren in Excel betekent rijen omzetten naar kolommen en kolommen naar rijen. Het is in feite je data 90 graden draaien: de oriëntatie verandert terwijl alle informatie behouden blijft.

Als je data transponeert, wordt de eerste rij de eerste kolom, de tweede rij de tweede kolom, enzovoort.

Als je bijvoorbeeld een dataset hebt met productnamen in rijen en maandelijkse verkopen in kolommen, dan krijg je na transponeren de productnamen in kolommen en de maandelijkse verkopen in rijen.

Manieren om te transponeren in Excel

Excel biedt drie verschillende methoden om data te transponeren, elk met eigen voordelen en ideale use-cases. Laten we elke techniek stap voor stap bekijken, zodat je de juiste aanpak kunt kiezen.

1. Data transponeren met Plakken speciaal

De methode Plakken speciaal is de eenvoudigste manier om data in Excel te transponeren. Je maakt hiermee een eenmalige kopie van je data in de getransponeerde oriëntatie. Laten we een voorbeeld doorlopen. Stel, we hebben de volgende regionale verkoopdata in verticale vorm: 

Voorbeeld van originele data in verticale vorm. Afbeelding door auteur. 

Zo transponeer je deze data van een verticale naar een horizontale lay-out:

1. Selecteer alle data die je wilt transponeren (in dit geval cellen A1:B5).
2. Kopieer de data (Ctrl+C of klik met de rechtermuisknop en kies Kopiëren).
3. Klik op de cel waar je de getransponeerde data wilt laten verschijnen (bijvoorbeeld cel D1).
4. Klik met de rechtermuisknop en kies Plakken speciaal in het contextmenu.

De methode Plakken speciaal en de optie Transponeren. Afbeelding door auteur. 

5. Vink in het dialoogvenster Plakken speciaal het vakje Transponeren aan en klik op OK.

Het resultaat na transponeren zie je hieronder:

Resultaat na het transponeren van de originele data. Afbeelding door auteur. 

Zie je hoe de data volledig is gedraaid? Wat eerst in rijen stond, staat nu in kolommen en andersom. De regiobenamingen die in kolom A stonden, staan nu over rij 1, en de verkoopcijfers die in kolom B stonden, staan nu over rij 2.

Wanneer gebruik je deze methode:

• Voor snelle, eenmalige transformaties
• Als je een simpele oplossing wilt zonder formules
• Voor kleinere datasets die niet vaak hoeven te worden bijgewerkt

Voordelen: Eenvoudig en snel toe te passen met slechts enkele klikken. 

Beperkingen: Maakt een statische kopie die niet wordt bijgewerkt als je originele data verandert.

2. De functie TRANSPOSE() gebruiken

De functie TRANSPOSE() biedt een dynamisch alternatief voor Plakken speciaal. In tegenstelling tot de vorige methode, die een statische kopie maakt, behoudt deze functie een livekoppeling met je originele data en werkt het getransponeerde resultaat automatisch bij wanneer de brondata verandert.

We gebruiken dezelfde regionale verkoopdata om te laten zien hoe dit werkt:

1. Selecteer eerst het doelbereik waar je getransponeerde data moet komen. Voor ons voorbeeld met regionale verkoopdata moeten we cellen selecteren die passen bij de getransponeerde structuur. Omdat de originele data 5 rijen bij 2 kolommen is, selecteren we een bereik van 2 rijen bij 5 kolommen.

2. Voer met de doelcellen actief de TRANSPOSE()-formule in de formulebalk in:

De functie TRANSPOSE() in Excel. Afbeelding door auteur. 

3. In moderne Excel (2019 of Microsoft 365) druk je gewoon op Enter. In oudere Excel-versies moet je Ctrl+Shift+Enter gebruiken, omdat dit een matrixformule is.

Het resultaat lijkt op ons vorige voorbeeld, maar met een belangrijk verschil: als je een waarde in de originele data verandert, wordt de getransponeerde versie automatisch bijgewerkt.

Resultaat na gebruik van de functie TRANSPOSE(). Afbeelding door auteur. 

Wanneer gebruik je deze methode:

• Als je brondata vaak verandert
• Als je een livekoppeling tussen origineel en getransponeerd wilt behouden
• Voor dynamische rapporten of dashboards die automatisch moeten vernieuwen

Voordelen: Maakt een dynamische koppeling die automatisch bijwerkt wanneer brondata verandert. 

Beperkingen: Vereist zorgvuldige selectie van de juiste doelbereikgrootte; vereist begrip van matrixformules voor oudere Excel-versies.

3. Data transponeren met Power Query

Power Query is Excel’s krachtigste tool voor datatransformatie, perfect voor grotere datasets en complexe bewerkingen. Hoewel het meer stappen vereist dan de vorige methoden, biedt het ongeëvenaarde flexibiliteit.

We gebruiken opnieuw onze regionale verkoopdata om te laten zien hoe Power Query data kan transponeren:

1. Begin door op het tabblad Gegevens in het lint te klikken en kies vervolgens de optie Gegevens ophalen (Power Query).

Hoe je Power Query start in Excel. Afbeelding door auteur. 

2. In het dialoogvenster dat verschijnt zie je verschillende gegevensbronnen. Omdat we met een klein voorbeeld werken dat al in Excel staat, kies je Lege tabel, zodat we onze data handmatig kunnen invoeren. Houd er rekening mee dat de veelzijdigheid aan gegevensbronnen een van de kernvoordelen van Power Query is.

Kiezen voor de optie Lege tabel als gegevensbron. Afbeelding door auteur. 

3. Voer op het volgende scherm je data in (of plak deze vanuit je werkblad) in de tabel. Vink Eerste rij als koptekst gebruiken aan als je data kolomkoppen bevat.

Data invoeren voor Power Query. Afbeelding door auteur. 

4. Na het klikken op Volgende opent de Power Query Editor met je data geladen. Klik nu op het tabblad Transformeren in het lint en kies Transponeren in het menu.

Data transformeren via de functie Transponeren in Power Query. Afbeelding door auteur. 

5. Je ziet je data onmiddellijk getransponeerd in het voorbeeldvenster. Merk op hoe Power Query je rijen en kolommen automatisch omdraait. De formulebalk toont Table.Transpose(Source), wat de toegepaste transformatie aangeeft.

Resultaat van transponeren in Power Query. Afbeelding door auteur. 

6. Als je tevreden bent met de transformatie, klik dan op Sluiten & laden op het tabblad Start om de getransponeerde data terug te zetten naar je werkblad.

Wanneer gebruik je deze methode:

• Voor grote datasets waar de andere methoden onhandig kunnen zijn
• Wanneer je extra transformaties wilt uitvoeren naast alleen transponeren
• Als onderdeel van een complexere workflow voor opschonen en voorbereiden van data
• Voor repetitieve taken die je wilt automatiseren

Voordelen:

• Ideaal voor grote datasets en complexe transformaties
• Maakt een query die je kunt vernieuwen wanneer brondata verandert
• Biedt extra opties voor opschonen binnen dezelfde workflow
• Kan worden opgeslagen als onderdeel van een herhaalbaar proces

Beperkingen:

• Vereist meer stappen dan de andere methoden
• Heeft een steilere leercurve voor beginners
• Opent een apart editorvenster

Kolommen, rijen en tabellen transponeren

We hebben de drie belangrijkste methoden om data in Excel te transponeren behandeld. Laten we kort enkele specifieke scenario’s bespreken die je kunt tegenkomen.

Hoe transponeer je kolommen naar rijen in Excel

Als je data in kolommen staat en je deze in rijen nodig hebt, gebruik dan een van de besproken methoden:

Plakken speciaal gebruiken:

1. Selecteer en kopieer je kolomgebaseerde data (Ctrl + C).
2. Klik met de rechtermuisknop op de eerste cel van de nieuwe rij waar je de data wilt plaatsen.
3. Klik op Plakken speciaal en vink Transponeren aan.
4. Klik op OK om je kolommen naar rijen om te zetten.

Dit herschikt de data direct, maar maakt een statische kopie.

De functie TRANSPOSE() gebruiken:

1. Selecteer een leeg bereik dat overeenkomt met het aantal rijen in de originele kolommen.

2. Typ =TRANSPOSE(A1:A5) (pas aan naar behoefte).

3. Druk op Enter (of Ctrl + Shift + Enter in oudere versies).

Dit houdt de getransponeerde data gekoppeld aan de originele kolommen, zodat deze automatisch wordt bijgewerkt.

Hoe transponeer je rijen naar kolommen in Excel

Als je data in rijen is gestructureerd maar in kolommen moet worden weergegeven, kun je de functie Plakken speciaal van Excel gebruiken voor een snelle, eenmalige conversie of de functie TRANSPOSE() voor een dynamische aanpak.

Plakken speciaal gebruiken:

1. Selecteer en kopieer je rijgebaseerde data (Ctrl + C).
2. Klik met de rechtermuisknop op de doelcel waar je de kolommen wilt laten verschijnen.
3. Klik op Plakken speciaal en vink het vakje Transponeren aan.
4. Klik op OK om je rijen naar kolommen om te zetten.

Merk op dat de stappen exact hetzelfde zijn als bij het transponeren van kolommen naar rijen. Excel ziet rijen en kolommen als uitwisselbaar.

De functie TRANSPOSE() gebruiken:

1. Selecteer een leeg bereik met hetzelfde aantal kolommen als je originele rijen.

2. Typ =TRANSPOSE(A1:F1) (pas het bereik aan naar behoefte).

3. Druk op Enter of Ctrl + Shift + Enter  .

Eventuele wijzigingen in de originele rij worden nu automatisch bijgewerkt in de getransponeerde kolommen.

Als je met grotere datasets werkt die vaak moeten worden bijgewerkt, is Power Query een andere effectieve manier om kolom-naar-rij- of rij-naar-kolomtransformaties te automatiseren terwijl de data gekoppeld blijft. In de vorige sectie staan de stappen om Power Query te gebruiken voor transponeren. 

Hoe transponeer je tabellen in Excel

Bij het werken met volledige tabellen of gestructureerde bereiken:

1. Voor kleinere tabellen selecteer je het volledige bereik en gebruik je Plakken speciaal → Transponeren, zoals eerder getoond.
2. Voor grotere tabellen met veel kolommen en rijen biedt Power Query meer controle en kan het elke tabelgrootte efficiënt verwerken.
3. Voor tabellen waarvan slechts een deel moet worden getransponeerd, kun je je aanpak opsplitsen—bepaalde secties transponeren terwijl andere in hun oorspronkelijke oriëntatie blijven

Onthoud dat bij het transponeren van een tabel niet alleen je data wordt omgewisseld, maar ook je kopteksten. In veel gevallen wil je dat je eerste rij je eerste kolom wordt (en andersom) om correcte datalabeling te behouden.

Overige aandachtspunten

Bij het transponeren van data in Excel zijn er enkele belangrijke punten die je kunnen helpen veelvoorkomende problemen te voorkomen en je workflow te verbeteren:

Omgaan met lege cellen

De functie TRANSPOSE() kan lege cellen vervangen door nullen, wat niet altijd wenselijk is. Om lege waarden te behouden bij gebruik van deze functie, kun je een geneste IF()-verklaring gebruiken:

=TRANSPOSE(IF(A1:F5="","",A1:F5))	

Deze formule controleert of cellen in het oorspronkelijke bereik leeg zijn en laat ze in dat geval leeg in het getransponeerde resultaat, in plaats van ze om te zetten naar nullen.

Koppelingen met brondata behouden

Zoals besproken gaan de drie transponatiemethoden verschillend om met koppelingen naar brondata:

• Plakken speciaal maakt een statische kopie zonder verbinding met de originele data.

• De functie TRANSPOSE() behoudt een dynamische koppeling en werkt automatisch bij wanneer brondata verandert.

• Power Query laat je de verbinding verversen wanneer nodig, zodat je controle hebt over wanneer updates plaatsvinden.

Kies de juiste methode op basis van hoe vaak je brondata verandert en of je automatische updates nodig hebt.

TRANSPOSE() gebruiken met voorwaardelijke opmaak

Houd er rekening mee dat opmaak niet wordt meegenomen bij gebruik van de functie TRANSPOSE(). Als je originele data voorwaardelijke opmaak gebruikt om belangrijke waarden te markeren, moet je nieuwe regels voor voorwaardelijke opmaak toepassen op het getransponeerde bereik.

Dit geldt voor alle visuele opmaak, inclusief celkleuren, lettertypen en randen. Alleen de waarden zelf worden getransponeerd, niet hun visuele presentatie.

Conclusie

Data transponeren in Excel is een waardevolle vaardigheid die je organisatie en analysetrajecten aanzienlijk kan verbeteren. Elk van de drie behandelde methoden biedt specifieke voordelen, afhankelijk van je behoeften.

Voor meer geavanceerde technieken voor datamanipulatie in Excel kun je onze skill track Excel Fundamentals bekijken, die een reeks vaardigheden behandelt om je data-analyse te versterken. Wil je je datatransformatieskills buiten Excel uitbreiden? Bekijk dan onze tutorial How to Transpose a Matrix in R voor vergelijkbare technieken in de R-omgeving.