Wat kun je doen aan grote en langzame Excel-bestanden?

Tijdens Excel-trainingen krijg ik regelmatig de vraag wat er gedaan kan worden aan langzame en enorm grote Excel-bestanden (grootte wat betreft de omvang in bytes). Nou hoeft groot niet altijd te betekenen dat het ook langzaam is en andersom ook niet: een langzaam bestand hoeft niet altijd groot te zijn.

Belangrijker is: kan er iets aan gedaan worden? Het antwoord is: ja, soms wel.

Wat kun je doen aan grote bestanden?

1. Opruiming houden

Het meest voor de hand liggend is om onderdelen die niet nodig zijn te verwijderen. Denk aan lege werkbladen, maar ook aan cellen die wel een opmaak hebben, maar die helemaal niet gebruikt worden. Die opmaak neemt wel ruimte in. Geef dus niet een hele rij of kolom een rand, opvulkleur, lettertype of getalopmaak, maar alleen de cellen waarvoor dit nodig is. Vergeet hierbij ook de voorwaardelijke opmaak niet (die heeft een nog grotere impact dan de gewone celeigenschappen!).

Er komt overigens een speciale opdracht in Excel die je hierbij een handje kan helpen. Die zit nu nog niet in de Microsoft365-versie, maar al wel in de browser-versie. Heb je een bestand opgeslagen op OneDrive of SharePoint, dan kun je dat bestand in een browser openen en die opdracht alvast bekijken en gebruiken. Hij staat op de tab Controleren en heet Prestatie controleren (Eng: Check Performance).
Rechts verschijnt dan een deelvenster dat je kunt gebruiken voor deze opruiming.

2. Vermijd het oude bestandformaat .xls

Vaak gaan bestanden jaren mee, waar op zich niets mis mee is. Maar ik zie nog regelmatig .xls-bestanden. Dat betekent dat ze nog in het oude bestandsformaat van versie 2003 staan. Dat bestand gaat dus al minstens 20 jaar mee! Maar een bestand in dat oude bestandsformaat is ook vele malen groter dan tegenwoordig noodzakelijk is. Bij een van mijn cursisten kon een bestand van 73 mB alleen al door het op te slaan in het huidige bestandsformaat .xlsx tot 21 mB worden teruggebracht!

Hoe herken je een bestand in het oude bestandsformaat? Omdat er bij de bestandsnaam (hier ‘voorbeeld’) bovenaan het scherm ”Compatibiliteitsmodus” staat ①. Als je het weergeven van de bestandsextensie hebt ingeschakeld zie je ook achter de naam .xls staan ②.

Hoe kun je zo’n bestand overzetten naar het huidige bestandsformaat? Open het bestand > kies Bestand > Opslaan als. Kies bij Opslaan als in de lijst met bestandsformaten Excel-werkmap (dat is de eerste in de lijst).
Omdat het bestand een andere extensie krijgt heb je hierna 2 bestanden: zorg nu dat je de oude niet meer gebruikt (verwijder die of geef die aan andere naam die duidelijk maakt dat het de oude versie is). Je zult niet de eerste zijn die per ongeluk soms in het ene en dan in het ander bestand werkt!

Een ander voordeel van het nieuwe bestandsformaat is dat je nu ook in dit bestand gebruik kunt maken van alle nieuwe functionaliteit die Excel te bieden heeft.
Is er ook een nadeel? Ja, als het gaat om een bestand dat nog bewerkt moet kunnen worden in versie 2003 of nog eerder, dan moet je het niet opslaan in het nieuwe bestandsformaat. Maar wie gebruikt er nog versie 2003 of eerder …

Oh, en vergeet niet dat hetzelfde ook geldt voor bestanden in Word en PowerPoint!

3. Staan er afbeeldingen in je werkmap?

Die kunnen ook een enorme impact hebben op de bestandsgrootte. Zeker als ze ook nog eens zijn bijgesneden en/of de afmetingen zijn gewijzigd. Excel onthoudt van elke afbeelding de oorspronkelijke grootte en instelling, zodat je hiernaar altijd terug kunt keren (Afbeelding opnieuw instellen). Maar dat kost natuurlijk ruimte.
Beperk de grootte door afbeeldingen te comprimeren. Selecteer een afbeelding en kies Afbeeldingsindeling > Afbeelding comprimeren. Je kunt de instelling die je dan maakt toepassen op de geselecteerde afbeelding of op alle afbeeldingen.

Wat kun je doen aan langzame bestanden?

Soms moet je erg lang wachten tot een bestand is geopend, bijvoorbeeld omdat het heel veel werkbladen heeft en/of heel veel berekeningen. Dat komt onder andere omdat bij het openen alle formules in alle werkbladen opnieuw worden doorgerekend.
Als het bestand eenmaal is geopend en je wijzigt wat, dan zal Excel bij elke wijziging alle geopende bestanden opnieuw doorrekenen. Bij heel veel en/of ingewikkelde berekeningen, kan het soms lang duren voordat dit klaar is en je de volgende wijziging kunt doen. De veel gebruikte VERT.ZOEKEN (Eng: VLOOKUP) is hierom berucht!

1. Handmatig berekenen

Je zou het doorrekenen tijdelijk kunnen uitschakelen. Je doet dit op het tabblad Formules > Berekeningsopties ① (Eng: Calculations Options) Je voert daarna de gewenste wijzigingen door en zet dan de automatische berekeningen weer aan. Tussendoor kun je eventueel laten doorrekenen met ② Nu berekenen (rekent alle geopende werkmappen door, Eng: Calculate Now) of Blad berekenen (rekent alleen het huidige werkblad door, Eng: Calculate Sheet).
Of je gebruikt de sneltoetsen hiervoor: F9 en Shift+F9.

Als er niet doorgerekende formules in je werkblad staan, kun je dat zien op de statusbalk, omdat daar links Berekenen (Engels: Calculate)staat.

Als je de berekeningsopties op handmatig hebt gezet, zal Excel vóór het opslaan toch het bestand nog doorrekenen (tenzij je dit specifiek uitschakelt bij Bestand > Opties in de rubriek Formules).

2. Optimaliseer je berekeningen

Vermijd berekeningen met hele kolommen als je niet de hele kolom nodig hebt, zoals =SOM(A:A). Maak er dan van =SOM(A1:A2000).
Soms zie ik hele lange complexe berekeningen. Die zijn niet alleen lastig om te lezen en aan te passen, maar zijn ook langzamer dan berekeningen in stapjes (met hulpkolommen die je eventueel verbergt).
Probeer zogenaamde volatiele functies niet te veel te gebruiken als dit niet nodig is. Dat zijn functies die elke keer opnieuw berekend worden. Dat kost natuurlijk tijd, zeker als je er veel gebruikt. Denk aan functies als NU, VANDAAG, ASELECT, ASELECT.TUSSEN en VERSCHUIVING (Eng: NOW, TODAY, RAND, RANDBETWEEN en OFFSET).

3. Koppelingen naar andere bestanden

Wanneer die aanwezig zijn in je bestand kan dat ook vertragend werken. Bij het openen krijg je meestal de vraag of je de gegevens wilt bijwerken of niet. Als je kiest voor niet bijwerken opent het bestand sneller, maar dan kunnen de gegevens verouderd zijn. Kies je voor bijwerken, dan duurt het openen langer, maar je weet wel dat je dan de bijgewerkte gegevens hebt!
Vaak worden tijdelijk koppelingen toegepast of zijn koppelingen per ongeluk in een bestand gekomen. Koppelingen kun je verbreken op de volgende manier.
Kies op de tab Gegevens > Koppelingen bewerken > selecteer de koppeling > Koppeling verbreken (Engels: Data > Edit Links > Break Link). Het resultaat van de koppeling staat dan als waarde in de cel (soort waarden plakken).