Excel-werkmappen staan erom bekend dat ze pijnlijk traag en zwaar worden naarmate er meer gegevens/formules/afbeeldingen aan worden toegevoegd.
Soms is het gemakkelijk om de extra massa weg te werken en de grootte van het Excel-bestand binnen de perken te houden, maar soms ook niet.
En omdat Excel geen web-based tool is, werken en werken de meesten van ons samen door Excel-bestanden via e-mail te versturen. En een groot Excel-bestand van 50 MB kan moeilijk te delen zijn met je collega’s/klanten.
In mijn vorige baan gebruikten we Lotus Notes e-mail client, die slakkengang is, en het verzenden van zware Excel-bestanden via die e-mail was een marteling.
Hoewel er geen magische manier is om de omvang van een Excel-werkmap te verkleinen, zijn er wel een paar technieken die u kunt gebruiken om wat flab te verminderen.
Deze Tutorial behandelt:
Tips om Excel-bestandsgrootte te verminderen
Hieronder vindt u de technieken die u kunt gebruiken om de bestandsgrootte van uw Excel-werkmap te verminderen.
Voor elke tip heb ik een aantal tests gedaan en heb ik snapshots toegevoegd om u de impact op de bestandsgrootte te laten zien. De resultaten die u krijgt, zijn afhankelijk van uw gegevens en bestanden.
Op basis van uw werkmap kunt u een of meer van deze tips gebruiken.
Ontnodige werkbladen/gegevens verwijderen
De beste oplossingen zijn vaak de gemakkelijkste.
En deze techniek is meer een kwestie van gezond verstand en minder van enige Excel-tovenarij.
Als u een Excel-werkmap hebt die een aantal werkbladen bevat die niet nodig zijn (en niet worden gebruikt in formules), kijk dan of u die kunt verwijderen.
Dit heeft een directe correlatie met de Excel-bestandsgrootte. Dus zodra u een aantal van de werkbladen verwijdert en de werkmap opslaat, zult u direct een kleinere bestandsgrootte zien.
Testresultaten:
Ik heb een Excel bestand gemaakt met 10 werkbladen waarbij elk werkblad 2.6 miljoen gegevenspunten (26 kolommen met 100.000 gegevenspunten in elke kolom).
De resulterende grootte van deze werkmap is 143 MB (en het duurt behoorlijk lang om deze op te slaan en te openen).
Nu ik vijf bladen heb verwijderd, is de grootte met de helft verminderd (duh).
Zoals u ziet, hebben de gegevens die u in uw werkblad hebt een directe correlatie met de bestandsgrootte.
Omzetten naar binair formaat (XLSB)
Weet u dat alleen al door een Excel-bestand op te slaan in het XLSB-formaat, de bestandsgrootte kan worden verkleind?
Ja, het is waar.
Het enige wat u hoeft te doen is tijdens het opslaan van een werkmap, de .xlsb-indeling te gebruiken.
Hier volgen de stappen om dit te doen:
- Klik op het tabblad Bestand.
- Klik op Opslaan als.
- Klik op Bladeren.
- In het dialoogvenster Opslaan als wijzigt u het bestandstype in Excel Binaire werkmap (.xlsb)
Testresultaten
Ik had een Excel-werkmap die 14,6 MB ruimte in beslag nam.
Nadat ik hetzelfde bestand had geconverteerd naar het XLSB-formaat, kon ik de grootte terugbrengen tot 10,4 MB (een vermindering van ~28%).
Wat is XLSB?
Wanneer u een Excel-bestand opslaat (.xlsx of .xlsm), wordt het opgeslagen in het XML-formaat.
Als ik bijvoorbeeld de bestandsextensie van het XLSX-bestand verander in ZIP en het vervolgens open, is dit wat ik krijg.
En als ik verder ga in de map ‘xl’ en dan in de map met werkbladen, krijg ik de werkbladen in die werkmap te zien (en deze werkbladen zijn opgeslagen als een XML-document).
Nu gaan we hetzelfde doen met een XLSB bestand.
Dus ik verander eerst de bestandsextensie in ZIP, open het vervolgens en zoek blad1. Dit is wat je krijgt:
Je zult zien dat zowel XLSX als XLSB het werkblad in een ander formaat opslaat.
XLSX/XLSM slaat het op als een XML bestand en XLSB slaat het op als een binair (.bin) bestand.
En zonder al te technisch te worden, XML-bestanden zijn groot in omvang vergeleken met een binair bestand.
Dus wanneer u uw Excel-werkmap opslaat in het XLSB-formaat, wordt de bestandsgrootte kleiner. In ons bovenstaande voorbeeld was de reductie ~30%.
XLSB Vs XLSX/XLSM
Als je de XLSB-indeling gebruikt, is de reductie van de bestandsgrootte een groot voordeel. Maar je moet wel een beetje voorzichtig zijn als je met het XLSB-formaat werkt.
Voordelen van het XLSB-formaat:
- Het vermindert de grootte van het Excel-bestand. Dit kan handig zijn wanneer u grote bestanden hebt en deze via e-mail wilt delen. U kunt het bestand snel verkleinen door het in XLSB op te slaan.
- XLSB-bestanden worden sneller geopend en opgeslagen dan XML-bestanden. Op basis van de beperkte tests die ik heb gedaan, openen en bewaren XLSB-bestanden 25%-50% sneller. Dit verschil is alleen merkbaar bij grote Excel-bestanden. Bij kleinere bestanden zijn zowel XLSB als XLSX snel.
Hoewel XLSB er goed uitziet, zijn er een paar redenen waarom je beter XLSX/XLSM-bestanden kunt gebruiken:
- Bij de XLSB-indeling kun je niet weten of er een macro in zit of niet. Dit maakt het riskanter, omdat iemand het kan gebruiken om kwaadaardige code uit te voeren.
- XLSB-bestanden zijn gecodeerd in een bedrijfseigen bin-bestandsindeling, terwijl XML een open-source leesbare bestandsindeling is. Dit betekent dat als u hulpprogramma’s van derden met Excel gebruikt, u beter XML-indeling kunt gebruiken.
- Power Query kan geen gegevens uit een XLSB-bestand lezen. Dus als u Power Query gebruikt, is het beter om uw gegevens in XLSX/XLSM-indeling te hebben.
Als algemene regel geldt dat als uw bestand klein is (minder dan 5 MB), het beter is om het bij XLSX/XLSM-indelingen te houden.
Op basis van wat ik van mensen heb gehoord en op veel forums heb gelezen, geven veel mensen de voorkeur aan XLSB als bestandsindeling als het gaat om het gebruik van Excel.
Hier staat een goed artikel over het begrijpen van de XLSB-indeling.
Verwijder onnodige formules
Te veel formules maken uw werkmap te groot.
Als u dus een gegevensverzameling hebt die u nodig hebt, maar de formules erin niet nodig hebt, kunt u deze formules beter omzetten in waarden.
Hieronder vindt u de stappen om formules snel om te zetten in waarden in Excel:
- Selecteer het hele werkblad/de hele gegevensverzameling.
- Druk op de toets F5.
- Klik in het dialoogvenster Ga naar speciaal op ‘Speciaal’.
- Selecteer in het dialoogvenster Ga naar speciaal op Formules.
- Klik op OK.
De bovenstaande stappen zouden alle cellen selecteren waar een formule in staat.
Nu de onderstaande stappen gebruiken om deze formules om te zetten in waarden:
- Kopieer de geselecteerde cellen (control + C)
- Klik op het tabblad Home.
- Klik in de groep Klembord op Plakken.
- Klik op Plak Waarde Icoon.
De bovenstaande stappen zouden alle cellen met formules omzetten in waarden.
Testresultaten
Ik heb een Excel-bestand gemaakt met 2,7 miljoen datapunten (1048576*26) en vervolgens drie kolommen met formules toegevoegd (SUM, COUNT en AVERAGE).
Daarna heb ik deze formules in waarden omgezet en het bestand met een andere naam opgeslagen.
Hieronder ziet u het resultaat:
Het bestand zonder formules is ~8% kleiner (en merk op dat ik slechts 3 kolommen met formules had).
In het geval dat u veel formules hebt, kan dit de omvang vergroten.
Afbeeldingen comprimeren (voor en na uploaden)
Als u werkt met Excel-bestanden waarin afbeeldingen zijn opgenomen, moet u deze afbeeldingen optimaliseren en ervoor zorgen dat de grootte kleiner is.
Het probleem met afbeeldingen is dat wanneer u deze comprimeert, er ook een kwaliteitsvermindering optreedt, wat u misschien niet wilt.
Wanneer u met afbeeldingen werkt, is de beste manier om de grootte van uw Excel-bestand laag te houden, door de afbeeldingen te comprimeren voordat u ze in Excel invoegt.
Een goede online tool om dit te doen is TinyPNG (of u kunt Photoshop of een andere soortgelijke tool gebruiken).
Het is een gratis tool die de grootte van een afbeelding met maximaal 90% kan verminderen en toch een fatsoenlijke afbeeldingskwaliteit behoudt.
Hieronder ziet u de schermafbeelding waarin ik een afbeelding met een grootte van 3,9 MB kon comprimeren tot 331 KB met minimaal kwaliteitsverlies.
Als u deze gecomprimeerde afbeeldingen eenmaal naar Excel hebt geüpload, kunt u deze verder comprimeren met de ingebouwde optie in Excel.
Hier vindt u de stappen voor het comprimeren van een afbeelding in Excel:
- Selecteer de afbeelding die u wilt comprimeren.
- Klik op het tabblad Afbeeldingshulpmiddelen opmaken. Dit is een contextueel tabblad dat alleen verschijnt wanneer u op een afbeelding klikt.
- In de groep Aanpassen klikt u op de optie ‘Afbeelding comprimeren’.
- In het dialoogvenster Afbeeldingen comprimeren:
- Selecteer ‘Alleen op deze afbeelding toepassen’ als u alle afbeeldingen in de werkmap wilt comprimeren.
- Selecteer E-mail (96 ppi) voor maximale compressie.
- Klik op OK.
- Bewaar het bestand.
Dit comprimeert alle afbeeldingen en helpt u de bestandsgrootte van uw Excel-werkmap te verkleinen.
Testresultaten:
Ik heb de optie voor het comprimeren van afbeeldingen in Excel gebruikt met één afbeelding.
Hieronder vindt u de resultaten:
Door de optie afbeelding comprimeren te gebruiken, is de bestandsgrootte teruggebracht van 445 KB naar 43 KB (~90% reductie in bestandsgrootte).
Dit zou ook afhangen van de afbeelding. Een afbeelding die al is gecomprimeerd, leidt mogelijk niet tot een enorme reductie in grootte.
Verwijder Pivot-cache om ruimte te besparen
Als u werkt met draaitabellen en worstelt met grote Excel-bestanden, kan kennis over Pivot-cache nuttig zijn.
Wanneer u een draaitabel maakt met behulp van een gegevensset, maakt Excel automatisch de Pivot Cache.
Pivot cache is een object dat een replica van de gegevensbron bevat.
Hoewel u het niet kunt zien, maakt het deel uit van de werkmap en is het verbonden met de draaitabel. Wanneer u wijzigingen aanbrengt in de draaitabel, gebruikt deze niet de gegevensbron, maar de draaitabelcache.
Hoewel u denkt dat u direct gekoppeld bent aan de brongegevens, hebt u in werkelijkheid toegang tot de draaitabelcache (en niet tot de brongegevens) wanneer u wijzigingen aanbrengt in de draaitabel.
Nu, omdat u een replica van de gegevensverzameling maakt, kost dat enige ruimte.
Zodra u dus een draaitabel maakt, zult u merken dat de grootte van het Excel-bestand toeneemt.
In bovenstaand voorbeeld wordt het bestand twee keer zo groot zodra ik de draaitabel toevoeg.
Nu zijn er twee manieren waarop u de bestandsgrootte kunt verkleinen wanneer u met draaitabellen werkt.
- Brongegevens bewaren en de Pivot-cache verwijderen
- De Pivot-cache bewaren en de brongegevens verwijderen
Laten we deze beide technieken eens bekijken.
Brongegevens bewaren en Pivot-cache verwijderen
Wanneer u klaar bent met de draaitabel en deze opslaat, kunt u Excel dwingen alleen de brongegevens en de resulterende draaitabel op te slaan, en niet de Pivot-cache.
Doet u dit, dan wordt uw Excel-bestand lichter, en u hebt een manier om de Pivot-cache opnieuw te maken wanneer u deze de volgende keer nodig hebt.
Hieronder ziet u het verschil in grootte wanneer u een Excel-bestand opslaat met en zonder Pivot Cache.
Hier volgen de stappen om de Pivot Cache NIET op te slaan:
- Selecteer een willekeurige cel in de Pivot Table.
- Klik op het tabblad Analyseren.
- Klik op Pivot Table ‘Options’ icoon.
- Klik in het dialoogvenster Pivot Table-opties op het tabblad Gegevens.
- In de opties voor Pivot Table-gegevens schakelt u het selectievakje ‘Brongegevens opslaan met bestand’ uit. Deze optie zorgt ervoor dat de Pivot Cache niet wordt opgeslagen wanneer u de werkmap opslaat (en helpt bij het verkleinen van Excel-bestandsgrootte).
- Vink de optie – ‘Gegevens verversen bij openen bestand’ aan. Deze optie zorgt ervoor dat zodra u de werkmap opent, de draaitabel wordt ververst en de Pivot Cache automatisch wordt gegenereerd. Als u deze optie niet aanvinkt, moet u de draaitabel handmatig verversen om de Pivot Cache te genereren.
- Klik op OK.
Nu, wanneer u het bestand met de draaitabel opslaat, ziet u dat de bestandsgrootte kleiner is geworden.
Houd de Pivot Cache en verwijder de brongegevens
Een andere manier om Excel-bestandsgrootte te verkleinen is door de brongegevens van een draaitabel te verwijderen en alleen de Pivot Cache te bewaren.
Dit werkt omdat een Pivot Cache een spiegelbeeld van uw gegevens is en u geen toegang tot de brongegevens hebt wanneer u wijzigingen in de draaitabel aanbrengt, maar in plaats daarvan de Pivot Cache gebruikt.
Hieronder staan de resultaten toen ik het Excel-bestand zonder de brongegevens opsloeg.
Om dit te doen, verwijdert u gewoon de gegevens (door de gegevens of het werkblad met de gegevens te verwijderen).
Wanneer u de brongegevens wilt terugkrijgen, dubbelklikt u op de cel ‘Grand Total’ van de draaitabel en deze genereert onmiddellijk de volledige gegevensset in een nieuw werkblad.
Comprimeer het bestand (ZIP IT)
Dit is iets wat u buiten de Excel-toepassing kunt doen.
Wanneer u een Excel-bestand zipt, ziet u onmiddellijk een vermindering in grootte van ongeveer 10-15%.
U kunt dit gezipte bestand vervolgens delen via e-mail, en de persoon die dit ontvangt kan het bestand uitpakken en de Excel-werkmap gebruiken.
Hieronder vindt u de stappen om een Excel-bestand te zippen (voor Windows):
- Rechtsklik op het Excel-bestand
- Beweeg uw cursor op de optie Verzenden naar.
- Klik op de optie ‘Gecomprimeerde (gezipte) map’.
Dit creëert een gezipt bestand voor de geselecteerde Excel-werkmap.
U zult ook een vermindering in bestandsgrootte opmerken (zoals hieronder getoond).
Testresultaten
Met deze techniek kon ik de bestandsgrootte van de Excel-werkmap met 8% terugbrengen.
Deze techniek werkt beter voor bestanden in het bereik van 20-50 MB. Naarmate de bestandsgrootte toeneemt, is het reductiepercentage niet erg hoog.
Gegevensopmaak verwijderen
Gegevensopmaak zoals het toepassen van een achtergrondkleur, of het toevoegen van randen, of het veranderen van de letterstijl kunnen allemaal bijdragen aan de bestandsgrootte.
Hoewel, gebaseerd op mijn tests, heb ik gemerkt dat het geen enorme impact heeft.
Hieronder staan de afmetingen van twee Excel-werkmappen, een met 2,6 miljoen datapunten (in één werkblad), en de andere met dezelfde gegevens maar met opmaak toegepast (celkleur, kader, en cursief).
Testresultaten
U kunt zien dat het verschil tussen de twee bestanden niet zo groot is (hoewel het wel een impact heeft als u veel gegevens hebt).
Het bestand met gegevens is 14.6 MB en het bestand met gegevens en opmaak is 14,9 MB.
Dus als u de bestandsgrootte van uw Excel-werkmap wilt verkleinen, kunt u op zoek gaan naar opmaak die u niet nodig hebt. Door deze te verwijderen kunt u wat schijfruimte besparen.
Hier volgen de stappen om direct alle opmaak te verwijderen, terwijl de gegevens intact blijven:
- Selecteer de hele dataset (waarvan u de opmaak wilt verwijderen).
- Klik op het tabblad Home.
- Klik in de groep Bewerken op ‘Wissen’.
- Klik op ‘Formaten wissen’.
- Sla de werkmap op.
Voorwaardelijke opmaak verwijderen
Net als gewone gegevensopmaak voegt ook voorwaardelijke opmaak toe aan de bestandsgrootte.
Op basis van mijn tests heb ik echter gemerkt dat het verschil minder groot is (tenzij je meerdere regels hebt toegepast op meerdere bladen met enorme datasets).
Niettemin is het een goed idee om voorwaardelijke opmaak te verwijderen uit elke dataset waar je die niet nodig hebt.
Naast de grootte is er nog een goede reden om voorwaardelijke opmaak te verwijderen – SNELHEID.
Voorwaardelijke opmaak is vluchtig en telkens als er een verandering in het werkblad optreedt, wordt het opnieuw berekend. Dit kan leiden tot een tragere werkmap.
Testresultaten
Ik heb een bestand gemaakt met 27,2 miljoen datapunten en daar 5 voorwaardelijke opmaakregels op toegepast.
Het verschil in grootte lijkt slechts een paar KB’s te zijn.
Also Read: Tips to Make Slow Excel Spreadsheets Fast
Excel Performance Booster Tips
De bovenstaande methoden helpen u de bestandsgrootte van Excel te verkleinen, maar hier volgen enkele aanvullende tips om de prestaties van Excel-werkmappen te verbeteren:
- Vermijd het gebruik van vluchtige formules. Afgezien van het verhogen van de bestandsgrootte, vluchtige formules maken je werkmappen ook traag. Voorbeelden van vluchtige functies zijn RAND, TODAY, OFFSET, INDIRECT, enz.
- Als u samenvattingen van een grote dataset moet maken, kunt u in plaats van formules beter een draaitabel gebruiken. Een draaitabel is snel en zeer intuïtief in vergelijking met formules.
- Als u gegevens uit meerdere bronnen haalt of gegevens uit meerdere bestanden combineert, gebruik dan Power Query om de gegevens op te halen en te transformeren. U kunt de gegevens opslaan als een Power Query-verbinding in plaats van ze in het werkblad te bewaren. U kunt eenvoudig draaitabellen maken met behulp van de verbindingen die met Power Query zijn gemaakt.
- U kunt uw gegevens in tabelvorm omzetten in een Excel-tabel en de Excel-tabel gebruiken in formules. Het maakt uw werkmap sneller presteren en formules gemaakt met behulp van Excel-tabel gestructureerde verwijzingen zijn gemakkelijker te beheren (omdat deze meer intuïtief).
- Vermijd het verwijzen hele rij of kolom in een formule. Dit helpt uw formules efficiënter te maken.
- Als u veel formules in uw werkmap hebt, schakelt u Automatische berekening uit en schakelt u over op Handmatige berekening. Door dit te doen, elke keer dat er een verandering in de werkbladen, zou de formules niet herberekenen. U kunt deze wijziging aanbrengen door naar het tabblad Formule te gaan en de ‘Berekeningsopties’ te wijzigen.
Hoop dat u de methode en de tips in deze Tips & Tricks nuttig vindt.