Hoe maak je een draaitabel in Excel?

Een draaitabel vat honderden of duizenden rijen ruwe data samen tot een overzicht dat je in enkele klikken aanpast. Je hoeft er geen formule voor te schrijven. Hieronder bouw je er stap voor stap een op, van het voorbereiden van de gegevens tot het opmaken en vernieuwen na een wijziging.

Een draaitabel groepeert en telt je gegevens zonder formules

Een draaitabel is een interactief overzicht dat een grote lijst samenvat door rijen te groeperen en waarden op te tellen, te tellen of te middelen. Je sleept velden naar zones en Excel rekent de samenvatting live uit. Verander je de indeling, dan herberekent de tabel meteen, zonder dat je iets aan de brondata raakt.

Typische vragen die een draaitabel in seconden beantwoordt: wat is de omzet per regio, hoeveel bestellingen per maand, of de gemiddelde orderwaarde per verkoper. In plaats van VERT.ZOEKEN of SOM.ALS te stapelen, laat je de draaitabel het groeperen doen. Voor het opzoeken van losse waarden blijven formules handiger; het verschil daartussen lees je in VERT.ZOEKEN vs X.ZOEKEN.

Goede brondata is de basis van een werkende draaitabel

Een draaitabel werkt alleen betrouwbaar als de brongegevens netjes zijn opgebouwd. Controleer je lijst op deze punten voordat je begint.

• Eén koprij. Elke kolom heeft bovenaan een unieke, korte kop; die kopjes worden de velden in de draaitabel.
• Geen lege rijen of kolommen. Een lege regel breekt het aaneengesloten bereik dat Excel herkent.
• Eén record per rij. Elke rij is één waarneming, bijvoorbeeld één bestelling of één transactie.
• Consistente gegevens. Getallen als getal, datums als datum, geen tekst zoals "12 stuks" in een getallenkolom.

Zet je lijst het liefst eerst om in een Excel-tabel met Ctrl+T. Voeg je later rijen toe, dan groeit de tabel automatisch mee en pakt de draaitabel na een vernieuwing de nieuwe regels op zonder dat je het bereik hoeft aan te passen. Een draaitabel op een gewoon bereik pakt nieuwe rijen niet vanzelf mee.

Let ook op samengevoegde cellen in je koprij of tussentitels die de lijst opbreken. Een draaitabel verwacht een platte lijst waarin elke kolom van boven tot onder hetzelfde soort gegeven bevat. Staan er tussenkopjes of subtotalen in de brondata, haal die er dan eerst uit; anders belanden ze als losse categorieën in je overzicht en kloppen de totalen niet.

Een draaitabel voeg je in vanaf het tabblad Invoegen

Met opgeschoonde data maak je de draaitabel in een paar stappen aan.

1. Klik op een willekeurige cel binnen je gegevenslijst. Zolang je cursor in de lijst staat, herkent Excel automatisch het hele aaneengesloten bereik; je hoeft niets te selecteren.
2. Ga naar het tabblad Invoegen en klik linksboven op Draaitabel. Werk je met een Ctrl+T-tabel, kies dan Uit tabel/bereik.
3. In het venster staat het bereik al ingevuld. Kies Nieuw werkblad zodat de draaitabel op een eigen, overzichtelijk tabblad komt.
4. Klik op OK. Excel opent een leeg draaitabelframe met rechts het paneel Draaitabelvelden.

Heb je haast of twijfel je over de indeling, gebruik dan Invoegen > Aanbevolen draaitabellen. Excel toont dan kant-en-klare voorstellen op basis van je gegevens die je met één klik plaatst.

De vier zones bepalen de vorm van je draaitabel

Rechts zie je het paneel Draaitabelvelden: bovenaan al je kolomkoppen, eronder vier zones. Je bouwt het overzicht door velden naar die zones te slepen.

Een concreet voorbeeld met een verkooplijst. Sleep het veld Regio naar Rijen en het veld Omzet naar Waarden. Meer is niet nodig: Excel toont nu per regio de totale omzet onder de kop Som van Omzet. Sleep je er ook Jaar naar Kolommen bij, dan splitst het overzicht zich per jaar uit. Zet je twee velden onder elkaar in de zone Rijen, bijvoorbeeld Regio en daaronder Klant, dan krijg je een genest overzicht met subtotalen per regio.

Je bepaalt zelf of Excel optelt, telt of middelt

Standaard telt Excel getallen op (Som van Omzet). Voor tekstvelden telt het juist het aantal. Je verandert die berekening eenvoudig.

1. Klik met de rechtermuisknop op een waarde in de kolom, of klik op het veld in de zone Waarden.
2. Kies Waardeveldinstellingen.
3. Selecteer onder Waarden samenvatten met de berekening: Som, Aantal, Gemiddelde, Max of Min.
4. Klik in hetzelfde venster op Getalnotatie om er meteen een nette opmaak aan te geven, bijvoorbeeld een euroteken of duizendtallen.

Op het tabblad Waarden weergeven als vind je nog een handige optie. Kies je daar % van eindtotaal, dan toont de tabel elk getal als aandeel van het geheel, ideaal voor een verdeling per regio of productgroep. Je kunt hetzelfde veld twee keer in de zone Waarden slepen, bijvoorbeeld eenmaal als Som en eenmaal als % van eindtotaal, zodat je bedrag en aandeel naast elkaar ziet.

Met filters, groepering en slicers zoom je in

Een draaitabel wordt pas echt bruikbaar als je erin kunt filteren en groeperen.

• Datums groeperen. Klik met de rechtermuisknop op een datum in de tabel en kies Groeperen. Selecteer bijvoorbeeld Maanden en Jaren om losse datums samen te vatten tot maandtotalen.
• Slicers toevoegen. Ga naar Draaitabelanalyse > Slicer invoegen en vink een veld aan. Je krijgt knoppen waarmee je met één klik op bijvoorbeeld een regio filtert.
• Tijdlijn toevoegen. Voor datumvelden kies je Tijdlijn invoegen, een schuifbalk waarmee je snel een periode selecteert.
• Sorteren en top 10. Klik op het pijltje bij een rijlabel om te sorteren of om via Waardefilters alleen de tien hoogste waarden te tonen.

Filters in de zone Filters plaatsen een keuzemenu bovenaan de tabel; slicers doen hetzelfde maar blijven zichtbaar als knoppen, wat prettiger werkt in een dashboard.

Met het tabblad Ontwerp maak je de draaitabel leesbaar

Standaard toont Excel de draaitabel in de compacte indeling, waarin geneste velden onder elkaar inspringen. Voor een rapport werkt een andere indeling vaak prettiger.

1. Klik in de draaitabel en ga naar het tabblad Ontwerp.
2. Open Indeling van rapport en kies Tabelvorm weergeven als je elk veld een eigen kolom wilt geven, handig om de gegevens later te kopiëren.
3. Via Subtotalen en Eindtotalen zet je die totalen aan of uit.
4. Kies een van de Draaitabelstijlen voor kleur en randen, en zet Gestreepte rijen aan voor betere leesbaarheid.

Wil je de cijfers ook visueel tonen, ga dan naar Draaitabelanalyse > Draaigrafiek. De grafiek is gekoppeld aan de tabel en verandert mee als je filtert of van indeling wisselt.

Met een berekend veld voeg je een eigen berekening toe

Soms wil je een getal dat niet als kolom in de brondata staat, bijvoorbeeld de winst als omzet min inkoop. Dat maak je met een berekend veld, zonder je brongegevens aan te passen.

1. Klik in de draaitabel en ga naar Draaitabelanalyse > Velden, items en sets > Berekend veld.
2. Geef het veld een naam, bijvoorbeeld Winst.
3. Typ bij Formule de berekening met de veldnamen, zoals = Omzet - Inkoop. Dubbelklik op een veld in de lijst om het in de formule te zetten.
4. Klik op Toevoegen en daarna op OK. Het nieuwe veld verschijnt in de veldenlijst en in de zone Waarden.

Een berekend veld rekent altijd op de totalen per groep, niet per losse rij. Voor eenvoudige verhoudingen werkt het prima; heb je een berekening per afzonderlijke regel nodig, voeg die dan als gewone kolom aan de brondata toe.

Dubbelklikken toont de onderliggende rijen

Wil je weten welke records achter een getal zitten, dubbelklik dan op een waarde in de draaitabel. Excel maakt een nieuw werkblad met alleen de brongegevens die dat getal opbouwen. Zie je bijvoorbeeld een onverwacht hoge omzet voor één regio, dan haal je met een dubbelklik direct de betreffende bestellingen tevoorschijn.

Deze functie, ook wel inzoomen genoemd, is handig om cijfers te controleren voordat je een rapport deelt. De nieuwe lijst is een momentopname; hij werkt niet mee als je de draaitabel later vernieuwt, dus verwijder zo'n tijdelijk blad als je het niet meer nodig hebt.

Deze problemen los je snel op

Werkt de draaitabel niet zoals verwacht, dan gaat het meestal om een van deze punten.

• Excel telt het aantal in plaats van de som. Er staat tekst of een lege cel in de getallenkolom. Ruim die op en zet in Waardeveldinstellingen de berekening terug op Som.
• Nieuwe rijen verschijnen niet. De draaitabel staat op een vast bereik. Werk met een Ctrl+T-tabel of pas de bron aan via Draaitabelanalyse > Gegevensbron wijzigen.
• Een verwijderde bronkolom geeft fouten. Verdwijnt een kolom waarnaar de tabel verwijst, dan kan een berekening breken; hoe je zo'n verwijzingsfout herstelt, staat in de #VERW!-fout oplossen.
• Oude items blijven in de filterlijst staan. Zet in de draaitabelopties het aantal te bewaren items per veld op Geen en vernieuw.
• Datums groeperen lukt niet. Er staat een datum als tekst in de kolom, of er zit een lege cel tussen. Zet de kolom om naar een echt datumtype en verwijder de lege regels, dan werkt groeperen weer.

Na een wijziging in de brondata druk je op Vernieuwen

Een draaitabel werkt met een kopie van je gegevens en past zich niet automatisch aan. Wijzig of voeg je data toe, dan moet je hem verversen.

1. Klik met de rechtermuisknop ergens in de draaitabel en kies Vernieuwen, of gebruik Alt+F5.
2. Wil je alle draaitabellen in het bestand tegelijk bijwerken, ga dan naar Draaitabelanalyse > Vernieuwen > Alles vernieuwen (Ctrl+Alt+F5).

Een handige gewoonte is om automatisch vernieuwen aan te zetten. Klik met de rechtermuisknop in de tabel, kies Draaitabelopties, ga naar het tabblad Gegevens en vink Gegevens vernieuwen bij het openen van het bestand aan. Zo is je overzicht bij elke opening actueel zonder dat je eraan hoeft te denken.

Loopt de e-mail vast waarin je het rapport wilt versturen, kijk dan bij Outlook maakt geen verbinding met de server. Vind je een eerder verstuurd rapport niet meer terug, dan helpt de zoekfunctie van Outlook repareren. Een uitgebreide uitleg over de indeling van draaitabellen vind je in de documentatie van Microsoft.