Draaitabel vernieuwen: Excel en Google Spreadsheets

Je hebt je perfecte rapport eindelijk af, en een keurige draaitabel laat de verkoop per regio, product en maand zien. Alles ziet er goed uit.

Dan werkt je teamgenoot de gegevensbron bij. Je opent het bestand opnieuw en verwacht dat de nieuwe cijfers verschijnen, maar er verandert niets.

Je totalen kloppen niet. De nieuwe items ontbreken. En nu vraag je je af of jij iets verkeerd hebt gedaan.

Dit is wat je moet weten: Excel en Google Spreadsheets gebruiken een draaitabelcache, in feite een opgeslagen momentopname van je gegevens. Dat helpt je draaitabel sneller laden, maar wordt niet automatisch bijgewerkt als de data verandert.

In deze gids laat ik je zien hoe je je draaitabellen handmatig ververst, of je nu in Excel of in Google Spreadsheets werkt.

Een draaitabel verversen

Laten we een paar manieren bekijken om de draaitabel in Excel en Google Spreadsheets te verversen.

Een draaitabel verversen in Excel

Er zijn drie manieren om een draaitabel in Excel te verversen: 

Handmatig

Zo doe je het handmatig: 

1. Klik ergens in je draaitabel.
2. Ga naar het tabblad Gegevens in het lint bovenaan.
3. Klik op Alles vernieuwen (of alleen Vernieuwen, afhankelijk van je Excel-versie).

Daarna werkt Excel je draaitabel bij met de nieuwste gegevens uit je brontabel.

De draaitabel handmatig vernieuwen. Afbeelding door de auteur.

Tip: Heb je meer dan één draaitabel van verschillende datasets in je werkmap, dan werkt Alles vernieuwen ze allemaal bij.

Sneltoetsen

Als je net zo van toetsen houdt als ik, is er een snellere manier: druk op Alt + F5 om de geselecteerde draaitabel te verversen.

Wil je alle draaitabellen in je werkmap verversen, druk dan op Ctrl + Alt + F5.

Automatisch verversen

Je kunt Excel je draaitabel ook laten verversen telkens wanneer je een bestand opent:

1. Klik ergens in je draaitabel.
2. Ga naar Analyseren > Draaitabel > Opties. (of klik met de rechtermuisknop op je draaitabel en kies Opties voor draaitabel)
3. Ga in het dialoogvenster Opties voor draaitabel naar het tabblad Gegevens.
4. Vink Gegevens vernieuwen bij het openen van het bestand aan.
5. Klik op OK.

Vanaf nu haalt Excel bij het openen van de werkmap automatisch de nieuwste gegevens op.

Automatisch verversen van de draaitabel instellen. Afbeelding door de auteur.

Een draaitabel verversen in Google Spreadsheets

In Google Spreadsheets worden Pivot tables (Google schrijft dit met een spatie) automatisch bijgewerkt wanneer je brondata verandert.

Als je bijvoorbeeld wijzigingen in je data aanbrengt en vervolgens naar je draaitabel kijkt, zie je dat de draaitabel de wijzigingen automatisch weergeeft. Je hoeft niet op Vernieuwen te klikken of iets speciaals te doen.

Automatiseringsopties

Als je regelmatig data bijwerkt, kan handmatig verversen lastig worden. Gelukkig kun je in zowel Excel als Google Spreadsheets het proces automatiseren, zodat je draaitabellen up-to-date blijven zonder extra klikken.

In Excel

Er zijn drie manieren om het verversen in Excel te automatiseren.

VBA-automatisering

Als je vertrouwd bent met macro’s, kun je VBA (Visual Basic for Applications) gebruiken om draaitabellen automatisch te verversen:

1. Druk op Alt + F11 om de VBA-editor te openen.

2. Klik op Invoegen > Module.

3. Plak deze eenvoudige code:

Sub AutoRefreshPivotTables()
    Dim PT As PivotTable
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
            PT.PivotCache.Refresh
        Next PT
    Next WS
End SubMogelijk gemaakt door

4. Sluit de editor.
5. Wanneer je de werkmap nu opent, ververst deze de draaitabel automatisch 

De draaitabel verversen met VBA. Afbeelding door de auteur.

Power Query-verversing

Als je draaitabel is gekoppeld aan Power Query, kun je in één stap zowel je data als je draaitabellen verversen: 

Ga naar Gegevens > Alles vernieuwen. 

Dit werkt je query en elke gekoppelde draaitabel bij.

Protip: Onder Gegevens > Query’s & Verbindingen zie je rechts het paneel Query’s & Verbindingen. Klik met de rechtermuisknop en kies Eigenschappen. Hier kun je vernieuwingen plannen.

Power Query gebruiken om draaitabellen bij te werken. Afbeelding door de auteur.

Externe gegevensverbindingen

Haalt je draaitabel data op uit externe bronnen (zoals een database, CSV of webfeed), dan kan Excel die automatisch bijwerken. Zo stel je dat in:

• Ga naar Gegevens > Verbindingen > Eigenschappen.

• Vink op het tabblad Gebruik aan: Gegevens vernieuwen bij het openen van het bestand.

• Vink Vernieuwen elke n minuten aan als je op een timer wilt bijwerken. (Optioneel) 

Deze instelling is handig voor dashboards die afhankelijk zijn van live data of dagelijkse imports.

In Google Spreadsheets

In Google Spreadsheets hoef je niets te automatiseren met Apps Script of add-ons, want de draaitabel wordt automatisch bijgewerkt. 

Als je nieuwe kolommen of rijen toevoegt, vergroot dan gewoon het gegevensbereik of voeg ze in tussen bestaande kolommen of rijen, en Google Spreadsheets werkt alles zelf bij.

Editor voor draaitabellen in Google Spreadsheets. Afbeelding door de auteur.

Problemen met het verversen van draaitabellen oplossen

Zelfs als alles goed is ingesteld, ververst je draaitabel soms niet zoals je verwacht. Laten we kijken naar veelvoorkomende oorzaken en hoe je deze problemen in zowel Excel als Google Spreadsheets oplost.

In Excel

Zo los je draaitabelproblemen in Excel op:

Controleer bereikverwijzingen en cache

Als je draaitabel geen nieuwe data laat zien, kan het zijn dat je gegevensbereik de nieuwste rijen niet omvat. Zo los je dat op:

• Klik ergens in de draaitabel. Ga naar Analyseren > Gegevensbron wijzigen en controleer of het bereik alle data dekt.

• Zet je gegevensbereik om in een tabel (Ctrl + T), want tabellen breiden de draaitabel automatisch uit wanneer je na het verversen nieuwe rijen toevoegt.

• Werkt de draaitabel nog steeds niet bij, dan is de cache mogelijk verouderd. Probeer Gegevens > Alles vernieuwen om die opnieuw op te bouwen.

Verbind externe gegevensbronnen opnieuw

Soms kan je draaitabel niet verversen omdat de gekoppelde gegevensbron, zoals een database, Power Query-bestand of online sheet, offline is of verplaatst:

• Ga naar Gegevens > Verbindingen.
• Controleer de status van elke vermelde verbinding.
• Als er een als inactief of ontbrekend wordt weergegeven, klik dan om opnieuw te verbinden of het bestandspad bij te werken.
• Klik zodra de verbinding is hersteld op Alles vernieuwen om de nieuwste data te laden.

Tip: Als het databestand is hernoemd of verplaatst, werk de link dan direct bij in de verbindings­eigenschappen, zodat het niet opnieuw misgaat.

Verminder vertraging bij grote datasets

Duurt het verversen van je werkmap te lang, doe dan het volgende:

• Zet automatische berekening tijdelijk uit (Formules > Opties voor berekenen > Handmatig) en ververs daarna.
• Splits zeer grote draaitabellen op in kleinere, gefilterd op regio, product of maand.
• Verwijder onnodige velden of totalen die de verwerking vertragen.

In Google Spreadsheets

Hoewel draaitabellen in Google Spreadsheets meestal automatisch verversen, tonen ze soms toch niet de laatste cijfers. Laten we twee veelvoorkomende oorzaken en oplossingen bekijken.

Controleer de bereikverwijzing

Als je nieuwe rijen of kolommen buiten het oorspronkelijke gegevensbereik toevoegt waarop je draaitabel is gebaseerd, neemt Spreadsheets die niet automatisch mee.

Je hebt bijvoorbeeld een nieuwe kolom Betaalmethode rechts van de dataset toegevoegd, maar de draaitabel ziet die niet:

• Open de editor voor draaitabellen en pas het bereik aan. 
• Selecteer bij het maken van je draaitabel een bereik dat iets groter is dan je huidige data.
• Als je later nieuwe rijen of kolommen moet toevoegen, voeg ze dan binnen het bestaande bereik in en niet daarbuiten.

Staat je huidige bereik bijvoorbeeld op A1:C11, voeg dan geen nieuwe kolom toe in D. Voeg in plaats daarvan een kolom in tussen A en C, zodat de nieuwe kolom binnen het bereik blijft.

Zo blijft je draaitabel altijd automatisch bijwerken.

Filters verwijderen 

Soms toont je draaitabel niet de nieuwste cijfers, ook al zijn je gegevens gewijzigd. Dat gebeurt meestal wanneer er een filter actief is.

Stel dat je een filter hebt ingesteld om alleen items met een omzet groter dan 50 te tonen. Daarna wijzig je de omzet van een item van 25 naar 130, maar het verschijnt nog steeds niet in het rapport. Dat komt doordat het filter de weergave blokkeert:

• Ga naar Gegevens > klik op Filter verwijderen om het uit te schakelen.
• Controleer je draaitabel om te bevestigen dat het bijgewerkte item nu wordt weergegeven.
• Als alles goed staat, kun je het filter opnieuw toepassen als je het nog nodig hebt.

Door het filter uit en weer in te schakelen, dwing je Google Spreadsheets om je data opnieuw te controleren en de meest recente waarden te tonen.

Tot slot 

Een snelle vernieuwing kan het verschil maken. Zo blijven je rapporten kloppend en weerspiegelen de cijfers die je ziet altijd je meest recente data.

Je kunt je tabellen handmatig bijwerken, een sneltoets gebruiken of automatisering instellen als je tijd wilt besparen. Wil je je vaardigheden verder uitbouwen, schrijf je dan in voor onze speciale cursussen. Ik raad Data Analysis in Excel en Introduction to Power Query in Excel aan.