Excel Power Pivot: een stapsgewijze gids

Grote Excel-bestanden analyseren zorgt vaak voor trage prestaties. 

Power Pivot biedt een andere aanpak. Het verbindt tabellen en verwerkt berekeningen zonder in te leveren op performance. In plaats van te worstelen met ketens van VLOOKUP() en hulpkolommen werk je met een gestructureerd systeem dat rechtstreeks in Excel is ingebouwd.

In deze gids leer je hoe je datamodellen opzet, tabelrelaties maakt, DAX-formules schrijft en interactieve rapporten bouwt met Power Pivot.

Wat is Power Pivot en waarom is het handig?

Power Pivot is Excel’s ingebouwde datamotor voor modellering. Het laat je grotere datasets inladen, meerdere tabellen koppelen en complexe berekeningen uitvoeren zonder de traagheid van traditionele werkbladen.

Waarin Power Pivot verschilt

In plaats van data direct in een werkblad op te slaan, laadt Power Pivot alles in Excel’s interne datamodel. 

Een standaardwerkblad kan grofweg een miljoen rijen aan en wordt meestal al veel eerder traag. Power Pivot omzeilt die limiet door data te comprimeren en apart te beheren, zodat je met tientallen miljoenen rijen kunt werken terwijl de werkmap vlot blijft.

Een relationele structuur in plaats van VLOOKUP-ketens

Zodra je data in het model staat, kun je tabellen relateren via sleutels, net als in een lichte database. Je hoeft niet alles plat te slaan tot één gigantisch werkblad en geneste VLOOKUP()-functies te gebruiken om tabellen te forceren. Met Power Pivot analyseer je gekoppelde tabellen netjes en betrouwbaar naast elkaar.

Sterkere berekeningen met DAX

Power Pivot uses DAX (Data Analysis Expressions), een formuletaal die expliciet is gebouwd voor analytisch werk. Hiermee maak je metingen die veel verder gaan dan wat een standaarddraaitabel aankan, van simpele sommen tot tijdgebaseerde metrics, verhoudingen, rollende vensters en andere geavanceerde berekeningen.

Voorbeeldscenario’s

Hier zijn twee voorbeelden van hoe bedrijven Power Pivot gebruiken in hun werkzaamheden: 

• Verkoopprestatie bijhouden: Combineer bestelgeschiedenis, producttabellen en klantkenmerken, en bouw vervolgens DAX-metingen voor omzet jaar-op-jaar of customer lifetime value zonder handmatig te mergen.
• Operationele rapportage: Koppel voorraad-, zending- en leveranciersdata en bereken vervolgens fill rates, doorlooptijden of forecastafwijkingen vanuit hetzelfde model.

Kortom, Power Pivot geeft je een database-achtige ervaring binnen Excel. Werk je met grote of multi-tabel-datasets, dan verandert het je rommelige rapportageprocessen in snelle, schaalbare modellen waarop je kunt voortbouwen.

Power Pivot instellen in Excel

Laten we nu kijken hoe je Power Pivot in Excel kunt gebruiken. 

Power Pivot inschakelen

Je hoeft Power Pivot niet te downloaden. Het zit al in Excel. Om het in te schakelen:

1. Open het Excel-werkblad 
2. Klik op Bestand op het lint
3. Selecteer Opties > Invoegtoepassingen 
4. Kies vervolgens in de vervolgkeuzelijst COM-invoegtoepassingen en klik op Start
5. Er verschijnt een pop-upvenster. Kies hier Microsoft Power Pivot for Excel en klik vervolgens op OK

Nu verschijnt Power Pivot op je lint.

Schakel de Power Pivot-invoegtoepassing in Excel in. Afbeelding door de auteur.

Let op: Power Pivot werkt alleen in Excel Professional Plus of Microsoft 365. Als je het tabblad na het inschakelen niet ziet, bevat de Excel-versie op je computer het mogelijk niet.

Data importeren uit meerdere bronnen

Je kunt nu data importeren uit verschillende bronnen, zoals een Excel-bestand, een CSV-bestand of zelfs een SQL Server-database. 

Voor dit voorbeeld hebben we twee datasets in een .xlsb-bestand:

1. sales.xlsb 

2. customer.xlsb 

Om ze te importeren in Power Pivot:

1. Klik op het tabblad Power Pivot en selecteer Beheren. Er opent een nieuw venster
2. Ga naar Start en klik op Externe gegevens ophalen en kies Uit andere bronnen
3. Scroll naar beneden en klik op Excel-bestand

Haal de gegevens uit andere bronnen. Afbeelding door de auteur.

4. Klik nu in de pop-up op Bladeren en selecteer het bestand customer.xlsb 

5. Vink het vakje Eerste rij gebruiken als kolomkop aan en klik op Volgende

Importeer het Excel-bestand in Power Pivot. Afbeelding door de auteur.

Klik in het volgende venster op Voorbeeld & Filter om te zien hoe je data eruitziet vóór het importeren. Ben je tevreden, klik dan op OK, en je ziet dat alle rijen succesvol zijn overgezet. Klik daarna op Sluiten.

Bekijk een voorbeeld van de geselecteerde data. Afbeelding door de auteur. 

Herhaal hetzelfde proces voor het bestand sales.xlsb. Onderaan het scherm zie je vervolgens dat beide bestanden zijn geïmporteerd. Dubbelklik erop en hernoem ze. 

Beide bestanden geïmporteerd. Afbeelding door de auteur.

Relaties en datamodellen opbouwen

Nu je data is geladen in Power Pivot, is het tijd om de tabellen te koppelen zodat Excel begrijpt hoe ze samenhangen. Deze stap vormt de basis voor al je rapporten.

Relaties tussen tabellen maken

Om een relatie te maken tussen de tabellen Sales en Customers:

1. Klik op het tabblad Start op Diagramweergave. Je ziet daar beide geïmporteerde tabellen
2. Klik op CustomerID in de tabel Sales
3. Sleep deze naar CustomerID in de tabel Customer om een relatie tussen de twee tabellen te maken

Let op: Wil je de relatie bewerken, klik dan met de rechtermuisknop op de lijn en kies Relatie bewerken... Selecteer in het venster de kolommen waarmee je de relatie wilt leggen.

Bouw een relatie tussen de tabellen. Afbeelding door de auteur.

In deze relatie kan één klant meerdere keren voorkomen in de tabel Sales, maar elke klant komt slechts één keer voor in de tabel Customers. Dit is een eenvoudige één-op-veel-relatie die ons laat velden uit beide tabellen gebruiken in draaitabellen en berekeningen doen zonder opzoekformules.

Ontwerpen met een sterschema

Een sterschema is een van de eenvoudigste manieren om een Power Pivot-model te structureren. Het houdt je tabellen georganiseerd en maakt berekeningen voorspelbaar.

Eerst kies je de feitentabel. In dit geval fungeert Sales als feitentabel omdat deze de transactieregels bevat: datum, klant, product, hoeveelheid en bedrag.

Bepaal vervolgens de dimensietabellen die de data in Sales beschrijven. Veelvoorkomende voorbeelden zijn:

• Customers (primaire sleutel: CustomerID)
• Products (primaire sleutel: ProductID)
• Regions (primaire sleutel: RegionID)

Elke dimensietabel heeft een primaire sleutel. Je verbindt die sleutel met de bijbehorende vreemde sleutel in de feitentabel:

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

Eenmaal gekoppeld staat de tabel Sales centraal met dimensietabellen eromheen. Dat is je ster. Deze structuur houdt het model overzichtelijk, versnelt berekeningen en verbetert de consistentie in rapportages.

Maak een sterschema. Afbeelding door de auteur.

Berekende kolommen toevoegen

Met je relaties op hun plaats kun je nieuwe velden direct in het datamodel maken.

1. Schakel over naar Gegevensweergave

2. Selecteer het lege veld Kolom toevoegen aan het einde van de tabel.

3. Voer = [TotalAmount] / [Qty] in en druk op Enter zodat Excel de hele kolom vult

4. Hernoem de kop naar PricePerUnit

Zo worden berekende kolommen onderdeel van de tabel zelf. Ze worden in het model opgeslagen, met je data ververst en blijven beschikbaar voor elke draaitabel of DAX-meting die je later maakt.

Voeg een extra berekende kolom toe. Afbeelding door de auteur.

DAX-formules schrijven voor analyse

Nu het model klaar is, kunnen we DAX-formules maken om de data te analyseren. Deze formules helpen ons totalen, vergelijkingen en tijdsgebonden berekeningen in onze rapporten te bouwen.

Metingen maken

Gebruik metingen wanneer je berekeningen wilt die automatisch verversen binnen een draaitabel.

Zo maak je een meting:

1. Open het Power Pivot-venster

2. Ga naar Start > Berekeningen > Nieuwe meting

3. Voer een formule in zoals = SUM(Sales[TotalAmount])

4. Geef hem de naam Total Sales en selecteer OK

Metingen maken. Afbeelding door de auteur. 

Een percentage-van-het-totaal-meting toevoegen

Je kunt deze formule ook gebruiken om een percentage van het totaal toe te voegen:

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

Dit toont het aandeel van elke regio in de totale omzet.

Voeg een percentage van het totaal toe. Afbeelding door de auteur.

Time intelligence gebruiken

Time-intelligencefuncties zijn DAX-formules die begrijpen hoe data door dagen, maanden, kwartalen en jaren beweegt. Ze laten je year-to-date-totalen berekenen, resultaten vergelijken met eerdere perioden en trends beoordelen zonder filters handmatig aan te passen.

Om te zien hoe deze functies in je model werken, heb je eerst een goede Datumtabel nodig.

De Datumtabel instellen

Zo stel je de tabel in: 

1. Ga naar Power Pivot > Aan gegevensmodel toevoegen
2. Selecteer in Power Pivot de tabel en kies Ontwerpen > Markeren als datumtabel

Maak een datumtabel. Afbeelding door de auteur.

3. Leg nu vanuit Start > Diagramweergave een relatie tussen Date[Date] → Sales[OrderDate].

Koppel Date Table[Date] aan Sales[OrderDate]. Afbeelding door de auteur.

Tijdsintelligentie-metingen maken

Zodra de Date-tabel klaar is, kun je metingen bouwen die prestaties over verschillende perioden evalueren.

Year-to-date:

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

Vergelijking met vorig jaar:

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

Bereken de tijd. Afbeelding door de auteur. 

Met de metingen gereed ga je terug naar Excel en creëer een draaitabel using het gegevensmodel. Plaats vervolgens velden uit de Datumtabel in het gebied Rijen en voeg Total Sales, Total Sales YTD en Sales Last Year toe aan Waarden. 

Dit laat zien hoe de time-intelligence-metingen werken met de Date-tabel binnen het model.

Draaitabel met Total Sales, YTD en Last Year Date. Afbeelding door de auteur. 

Veelvoorkomende DAX-patronen

Sommige DAX-formules komen vaak terug omdat ze je helpen data snel op te splitsen en veelgestelde vragen te beantwoorden. Hier zijn twee patronen die in veel modellen goed werken:

Gemiddelde per categorie:

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

Doorlopende som over datums:

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Maak er bij het creëren van metingen een paar eenvoudige gewoontes van: 

• Geef ze duidelijke namen
• Houd formules leesbaar
• Gebruik variabelen (VAR) wanneer de meting lang wordt. 

Zo wordt het model later makkelijker te begrijpen wanneer je erop terugkomt.

Visualiseren en werken met je model

Nu het model en de metingen klaar zijn, maken we er visuals van die je in real time kunt verkennen en aanpassen.

Draaitabellen en draaigrafieken maken

Zo voeg je een draaitabel in vanuit het gegevensmodel om direct met je gekoppelde tabellen te werken:

1. Open een Excel-blad
2. Ga naar Invoegen > Draaitabel > Uit gegevensmodel
3. Selecteer Nieuw werkblad

In het paneel Velden van draaitabel kun je nu velden uit elke tabel halen. Bijvoorbeeld:

• Sleep RegionName uit de tabel Regions naar Rijen
• Sleep Total Sales naar Waarden

Omdat we eerder relaties hebben gebouwd, brengt Excel alles automatisch samen.

Maak een draaitabel met de Power Pivot-gegevens. Afbeelding door de auteur.

Wil je een visual, klik dan ergens in de draaitabel, ga naar Invoegen > Draaigrafiek, kies een grafiektype (zoals geclusterde kolom) en bevestig. De grafiek blijft gekoppeld aan de draaitabel, dus alles werkt samen bij updates.

Voeg een draaigrafiek toe. Afbeelding door de auteur.

Slicers en filters toevoegen

Slicers geven je snelle, knopachtige filters die het rapport interactief maken. Zo voeg je ze toe:

1. Klik op je draaitabel
2. Ga naar Invoegen > Slicer
3. Kies velden zoals RegionName of ProductName

Een slicer verschijnt als een vak op het blad. Wanneer je verschillende items klikt, worden de draaitabel en grafiek direct bijgewerkt. Heb je meerdere draaitabellen, dan kun je één slicer aan allemaal koppelen voor consistente filtering op de pagina.

Voeg Slicers toe. Afbeelding door de auteur.

KPI’s bouwen

KPI’s helpen je prestaties ten opzichte van een doel te zien zonder extra berekeningen aan het werkblad toe te voegen. Zo bouw je ze:

1. Ga in het Power Pivot-venster naar KPI’s > Nieuwe KPI
2. Stel Total Sales in als basismeting
3. Gebruik Absolute waarde, voer je doel in (bijvoorbeeld 4000), pas drempels aan en kies een pijl-/pictogramstijl
4. Klik op OK om de KPI te maken

Stel de KPI van een meting in. Afbeelding door de auteur.

5. Vouw in het paneel Draaitabelvelden de tabel Sales uit en vouw vervolgens Total Sales uit
6. Sleep daaruit Total Sales en Status naar het veld Waarden

Nu zie je de doelprestatie ten opzichte van een drempel. 

Toon de KPI-status in een Excel-draaitabel. Afbeelding door de auteur.

Power Pivot-prestaties optimaliseren

Als het model eenmaal is gebouwd, willen we het snel en prettig houdbaar maken. Power Pivot kan grote datasets aan, maar een paar kleine aanpassingen helpen het bestand responsief te blijven, zeker wanneer je in de tijd meer data toevoegt.

Modelgrootte verkleinen

Een lichter model draait sneller, dus verwijder alles wat je niet nodig hebt.

Je kunt ongebruikte kolommen verwijderen in Gegevensweergave. Zelfs als een kolom nooit in een draaitabel verschijnt, neemt die toch geheugen in; snoeien houdt het model schoon.

Wanneer je nieuwe data inlaadt, gebruik Power Query om rijen en kolommen te filteren vóór ze het model in gaan. Zo laad je alleen de velden die je nodig hebt en blijft alles overzichtelijker.

Probeer berekende kolommen te vermijden tenzij ze echt nodig zijn, omdat ze voor elke rij een waarde opslaan, wat de bestandsgrootte snel doet toenemen. Metingen daarentegen zijn efficiënter omdat ze alleen rekenen wanneer een draaitabel ze nodig heeft. 

Efficiënte gegevenstypen kiezen

Power Pivot comprimeert data verschillend afhankelijk van het gegevenstype. Met het juiste type kan dat een merkbaar verschil maken.

Selecteer in Gegevensweergave een kolom en kies het meest nauwkeurige type onder Gegevenstype in het lint. Bijvoorbeeld:

• Gehele getallen > Geheel getal
• Decimale waarden > Decimaal getal
• ID’s of codes die niet voor berekeningen worden gebruikt > Tekst

Kies je het juiste type, dan comprimeert Power Pivot de kolom beter, wat de grootte verkleint en berekeningen versnelt.

Controleer en gebruik het juiste gegevenstype. Afbeelding door de auteur.

Problemen met vernieuwen en berekenen oplossen

Als je draaitabellen de nieuwste data niet tonen, ga dan naar het tabblad Power Pivot en klik op Alles vernieuwen. Dit laadt alles opnieuw vanuit je bronbestanden.

Als getallen niet kloppen, open dan de Diagramweergave en controleer je relaties, want een ontbrekende of kapotte relatie kan totalen laten verspringen of verkeerd filteren.

Krijg je een DAX-fout, vooral bij complexere metingen, dan betekent dit vaak dat de formule zichzelf indirect verwijst. Herschrijf de meting in dat geval met eenvoudigere logica of gebruik VAR-blokken om de cirkelverwijzing op te lossen.

Integreren met Power Query en Power BI

Een van de advantages van Power Pivot is hoe makkelijk het samenwerkt met de rest van Microsoft’s datastack. We kunnen Power Query gebruiken om de data op te schonen en vorm te geven vóór die het model ingaat, of het hele model overzetten naar Power BI wanneer je interactieve dashboards nodig hebt.

Data opschonen en transformeren in Power Query

Power Query is de beste plek om je data voor te bereiden voordat je deze in Power Pivot laadt. Je kunt er alles vooraf schonen, filteren en vormgeven zodat het model georganiseerd blijft.

Je opent Power Query via Gegevens > Van tekst/CSV > Transformeren. Dit brengt de data in de editor, waar je:

• Duplicaten kunt verwijderen
• Kolommen kunt hernoemen of herschikken
• Waarden eruit kunt filteren die je niet nodig hebt
• Gegevenstypen kunt wijzigen vóór ze het model bereiken

Power Query legt elke stap vast aan de rechterkant van het venster. Dat betekent dat de opschoning automatisch draait telkens wanneer je het bestand ververst.

Als alles goed staat, selecteer Sluiten & laden naar en kies vervolgens Gegevensmodel. De opgeschoonde data wordt direct in Power Pivot geladen.

Modellen exporteren naar Power BI

Je kunt je Power Pivot-model ook meenemen naar Power BI wanneer je rijkere visuals of gedeelde dashboards nodig hebt. Zo doe je dat:

1. Sla je Excel-werkmap op
2. Open Power BI Desktop
3. Ga naar Gegevens ophalen > Excel-werkmap
4. Selecteer je bestand

Power BI importeert de tabellen en relaties precies zoals ze bestaan in Power Pivot. Van daaruit kun je dashboards bouwen, samenwerken met je team en geplande vernieuwingen instellen zodat je rapporten up-to-date blijven zonder handmatige stappen.

Best practices voor duurzame modellen

Naarmate je model groeit, zorgt orde houden ervoor dat je het makkelijker kunt bijwerken, debuggen en erop voortbouwen. Hier zijn een paar gewoontes die helpen om het model in de tijd schoon en betrouwbaar te houden:

Naamgevingsconventies en organisatie

Duidelijke namen maken een groot verschil wanneer je na weken of maanden terugkomt in een bestand. Gebruik daarom leesbare namen voor metingen zoals Total_Sales, Total_Quantity of Profit_Margin zodat je altijd weet waar elke meting voor staat.

Je kunt gerelateerde metingen ook groeperen in weergavemappen in het Power Pivot-venster. Wanneer het model groter wordt, maken deze mappen het makkelijker om de berekeningen te vinden die je nodig hebt.

Datavalidatie

Voordat je de cijfers vertrouwt, doe een paar snelle controles:

• Vergelijk totalen uit de brondata met totalen in je draaitabellen
• Gebruik simpele DAX-controles zoals:

• COUNTROWS() om te bevestigen hoeveel rijen er in een tabel staan

• DISTINCTCOUNT() om unieke waarden te verifiëren, zoals klanten of producten

Deze kleine tests helpen je ontbrekende relaties, onjuiste filters of dataproblemen op te sporen voordat ze grotere problemen veroorzaken.

Je modellen onderhouden en bijwerken

Wanneer er nieuwe data is, ga naar het tabblad Power Pivot en kies Vernieuwen of Alles vernieuwen. Power Pivot laadt dan alles opnieuw vanuit de gekoppelde bronnen.

Maak vóór grote structurele wijzigingen, zoals het toevoegen van nieuwe relaties of het herschrijven van sleutelmetingen, een back-upkopie van het bestand. Dat geeft je een veilige terugvaloptie als iets niet gaat zoals gepland.

Tot slot

Power Pivot brengt je data samen op één plek en helpt je rapporten te bouwen die duidelijk en betrouwbaar zijn. Als het model eenmaal staat, kun je je cijfers verkennen, visuals maken en alles updaten met één verversing.

Wil je de volledige set tools van Excel leren, bekijk dan ons traject Data Analysis with Excel Power Tools en natuurlijk ook onze cursus Power Pivot in Excel.