8 manieren om de Excel-bestandsgrootte te verkleinen (die echt werken)

Het is bekend dat Excel-werkmappen pijnlijk traag en zwaar worden naarmate er meer gegevens/formules/afbeeldingen aan worden toegevoegd.

Soms is het gemakkelijk om van de extra flab af te komen en de Excel-bestandsgrootte onder controle te houden, en soms is dat niet het geval.

En aangezien Excel geen webgebaseerde tool is, werken en werken de meesten van ons samen door Excel-bestanden via e-mail te verzenden. En een groot Excel-bestand van 50 MB kan moeilijk zijn om te delen met uw collega's/klanten.

In mijn laatste baan gebruikten we de e-mailclient van Lotus Notes, die traag is, en het verzenden van zware Excel-bestanden was een marteling.

Hoewel er geen magische manier is om de grootte van een Excel-werkmap te verkleinen, zijn er een paar technieken die u kunt gebruiken om wat flab te verminderen.

Opmerking: de impact van deze technieken om de Excel-bestandsgrootte te verkleinen, is afhankelijk van uw gegevens. Voor sommigen kan het helpen om de maat met 50% te verkleinen en voor sommigen kan het de maat uiteindelijk maar met 5% verkleinen.

Tips om de Excel-bestandsgrootte te verkleinen

Hieronder staan ​​de technieken die u kunt gebruiken om de bestandsgrootte van uw Excel-werkmap te verkleinen.

Voor elke tip heb ik wat tests gedaan en de snapshots toegevoegd om je de impact op de bestandsgrootte te laten zien. De resultaten die u krijgt, zijn afhankelijk van uw gegevens en bestanden.

Op basis van je werkmap kun je een of meer van deze tips gebruiken.

Onnodige werkbladen/gegevens verwijderen

De beste oplossingen zijn vaak de gemakkelijkste.

En deze techniek is meer gezond verstand en minder Excel-tovenarij.

Als u een Excel-werkmap heeft met enkele werkbladen die niet nodig zijn (en niet in formules worden gebruikt), kijk dan of u die kunt verwijderen.

Dit heeft een directe correlatie met de Excel-bestandsgrootte. Dus zodra u enkele werkbladen verwijdert en de werkmap opslaat, ziet u meteen een kleinere bestandsgrootte.

Test resultaten:

Ik heb een Excel-bestand gemaakt met 10 werkbladen waarbij elk werkblad 2,6 miljoen gegevenspunten heeft (26 kolommen met 100.000 gegevenspunten in elke kolom).

De resulterende grootte van deze werkmap is 143 MB (en het kost nogal wat tijd om op te slaan en te openen).

Toen ik nu vijf vellen verwijderde, werd het formaat gehalveerd (duh).

Zoals u kunt zien, hebben de gegevens die u op uw werkblad hebt een directe correlatie met de bestandsgrootte.

Converteren 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 dat u hoeft te doen, is tijdens het opslaan van een werkmap het .xlsb-formaat gebruiken.

Hier zijn de stappen om dit te doen:

1. Klik op het tabblad Bestand.
2. Klik op Opslaan als.
3. Klik op Bladeren.
4. Wijzig in het dialoogvenster Opslaan als het bestandstype in Excel Binary Workbook (.xlsb)

Test resultaten

Ik had een Excel-werkmap die 14,6 MB aan ruimte in beslag nam.

Nadat ik hetzelfde bestand naar het XLSB-formaat had geconverteerd, kon ik de grootte verkleinen tot 10,4 MB (een vermindering van ~ 28%).

Wat is XLSB?

Wanneer u een Excel-bestand (.xlsx of .xlsm) opslaat, wordt het opgeslagen in de XML-indeling.

Als ik bijvoorbeeld de bestandsextensie van het XLSX-bestand in ZIP verander en het vervolgens open, krijg ik dit.

En als ik verder in de map 'xl' ga en vervolgens in de map met werkbladen, toont het me de werkbladen in die werkmap (en deze werkbladen worden opgeslagen als een XML-document).

Laten we nu hetzelfde doen met een XLSB-bestand.

Dus ik verander eerst de bestandsextensie in ZIP, open het en zoek sheet1. Dit is wat je krijgt:

U zult merken dat zowel XLSX als XLSB het werkblad in een ander formaat opslaan.

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, zijn XML-bestanden groot in vergelijking met een binair bestand.

Dus wanneer u uw Excel-werkmap opslaat in de XLSB-indeling, wordt de bestandsgrootte verkleind. In ons bovenstaande voorbeeld was de verkleining ~ 30%.

XLSB versus XLSX/XLSM

Als het gaat om het gebruik van het XLSB-formaat, is verkleining een enorm voordeel. Maar je moet een beetje voorzichtig zijn bij het werken met het XLSB-formaat.

Voordelen van XLSB-formaat:

1. Het verkleint de Excel-bestandsgrootte. Dit kan handig zijn als u grote bestanden heeft en deze via e-mail wilt delen. U kunt het formaat snel verkleinen door het op te slaan in XLSB.
2. XLSB-bestanden hebben de neiging om sneller te openen en op te slaan dan een XML-bestand. Op basis van de beperkte tests die ik deed, werden XLSB-bestanden geopend en 25%-50% sneller opgeslagen. Dit verschil is alleen merkbaar in grote Excel-bestanden. Met kleinere bestanden zijn zowel XLSB als XLSX snel.

Hoewel XLSB er geweldig uitziet, zijn er een paar redenen waarom je je moet houden aan het gebruik van XLSX/XLSM-bestanden:

1. Met XLSB-formaat weet je niet of het een macro heeft of niet. Dit maakt het riskanter omdat iemand het kan gebruiken om kwaadaardige code uit te voeren.
2. XLSB-bestanden zijn gecodeerd in een eigen bin-bestandsindeling, terwijl XML een open-source leesbaar bestandsformaat is. Dit betekent dat als u tools van derden gebruikt met Excel, het beter is om de XML-indeling te gebruiken.
3. 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-indelingen te hebben.

Als algemene regel geldt dat als uw bestandsgrootte klein is (minder dan 5 MB), het beter is om bij de XLSX/XLSM-indelingen te blijven.

Op basis van wat ik van mensen heb gehoord en op veel forums heb gelezen, geven veel mensen de voorkeur aan XLSB als bestandsformaat als het gaat om het gebruik van Excel.

Hier is een goed artikel over het begrijpen van het XLSB-formaat.

Onnodige formules verwijderen

Te veel formules zullen de bestandsgrootte van uw werkmap doen toenemen.

Dus als je een dataset hebt die je nodig hebt, maar de formules erin niet nodig hebt, is het beter om deze formules om te zetten in waarden.

Hier zijn de stappen om formules snel om te zetten naar waarden in Excel:

1. Selecteer het hele werkblad/de gegevensset.
2. druk de F5 sleutel.
3. Klik in het dialoogvenster Ga naar op 'Speciaal'.
4. Selecteer in het dialoogvenster Ga naar speciaal de optie Formules.
5. Klik OK.

De bovenstaande stappen zouden alle cellen selecteren die een formule bevatten.

Gebruik nu de onderstaande stappen om deze formules om te zetten in waarden:

1. Kopieer de geselecteerde cellen (control + C)
2. Klik op het tabblad Start.
3. Klik in de groep Klembord op Plakken.
4. Klik op het pictogram Waarde plakken.

De bovenstaande stappen zouden alle cellen met formules in waarden omzetten.

Test resultaten

Ik heb een Excel-bestand gemaakt met 2,7 miljoen gegevenspunten (1048576*26) en vervolgens drie kolommen met formules toegevoegd (SUM, COUNT en AVERAGE).

Vervolgens heb ik deze formules omgezet in waarden en het bestand onder een andere naam opgeslagen.

Hieronder het resultaat:

Het bestand zonder formules is ~8% kleiner (en merk op dat ik maar 3 kolommen met formules had).

Als u veel formules heeft, kan dit de grootte vergroten.

OPMERKING: Vluchtige formules kunnen uw bestandsgrootte behoorlijk doen opzwellen. Als onderdeel van de test, toen ik de SUM-formule verving door een vluchtige formule (OFFSET), leidde dit tot een sprong in de bestandsgrootte (van 186 MB naar 198 MB). Dus als u vluchtige formules heeft die u kunt vervangen door niet-vluchtige formules, of deze kunt converteren naar waarden, zal dit u helpen de bestandsgrootte te verkleinen.

Afbeeldingen comprimeren (voor en na het uploaden)

Als u werkt met Excel-bestanden die afbeeldingen bevatten, moet u deze afbeeldingen optimaliseren en ervoor zorgen dat de grootte kleiner is.

Het probleem met afbeeldingen is dat als je deze comprimeert, er ook een vermindering van de kwaliteit is, wat je 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 deze in Excel invoegt.

Een goede online tool om dit te doen is TinyPNG (of je kunt Photoshop of een andere soortgelijke tool gebruiken).

Het is een gratis tool die de grootte van een afbeelding tot 90% kan verkleinen en toch een behoorlijke beeldkwaliteit behoudt.

Hieronder is de schermafbeelding waar ik een afbeelding met een grootte van 3,9 MB tot 331 KB kon comprimeren met minimaal kwaliteitsverlies.

Nadat u deze gecomprimeerde afbeeldingen naar Excel hebt geüpload, kunt u deze verder comprimeren met behulp van de ingebouwde optie in Excel.

Hier zijn de stappen om een ​​afbeelding in Excel te comprimeren:

1. Selecteer de afbeelding die u wilt comprimeren.
2. Klik op het tabblad Opmaakhulpmiddelen voor afbeeldingen. Dit is een contextueel tabblad dat alleen verschijnt wanneer u op een afbeelding klikt.
3. Klik in de groep Aanpassen op de optie 'Afbeelding comprimeren'.
4. In het dialoogvenster Afbeeldingen comprimeren:
   • Schakel 'Alleen toepassen op deze afbeelding' uit als u alle afbeeldingen in de werkmap wilt comprimeren.
   • Selecteer E-mail (96 ppi) voor maximale compressie.
5. Klik OK.
6. Bewaar het bestand.

Hiermee worden alle afbeeldingen gecomprimeerd en kunt u de bestandsgrootte van uw Excel-werkmap verkleinen.

Test resultaten:

Ik heb de optie afbeelding comprimeren in Excel gebruikt met één afbeelding.

Hieronder de resultaten:

Door de optie afbeelding comprimeren te gebruiken, is de bestandsgrootte verkleind van 445 KB naar 43 KB (~90% vermindering van de bestandsgrootte).

Dit zou ook van de afbeelding afhangen. Een afbeelding die al is gecomprimeerd, leidt mogelijk niet tot een enorme verkleining van de grootte.

Pivot Cache verwijderen om ruimte te besparen

Als u met draaitabellen werkt en worstelt met grote Excel-bestanden, kan het handig zijn om meer te weten over draaitabellen.

Wanneer u een draaitabel maakt met behulp van een gegevensset, maakt Excel automatisch de draaitabel.

Draaicache 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 draaitabel.

Hoewel u denkt dat u rechtstreeks aan de brongegevens bent gekoppeld, krijgt u in werkelijkheid toegang tot de draaitabel (en niet de brongegevens) wanneer u wijzigingen aanbrengt in de draaitabel.

Omdat u nu een replica van de gegevensset maakt, neemt dit wat ruimte in beslag.

Dus zodra je een draaitabel maakt, zul je merken dat de grootte van het Excel-bestand toeneemt.

In het bovenstaande voorbeeld, zodra ik de draaitabel toevoeg, wordt de grootte van het bestand twee keer zo groot.

Er zijn nu twee manieren waarop u de bestandsgrootte kunt verkleinen wanneer u met draaitabellen werkt.

1. Bewaar de brongegevens en verwijder de Pivot Cache
2. Bewaar de Pivot Cache en verwijder de brongegevens

Laten we naar beide technieken kijken.

De brongegevens behouden en de draaicache verwijderen

Wanneer u klaar bent met de draaitabel en deze opslaat, kunt u Excel dwingen om alleen de brongegevens en de resulterende draaitabel op te slaan, en niet de draaitabel.

Als u dit doet, wordt uw Excel-bestand lichter en heeft u een manier om de Pivot-cache opnieuw te maken wanneer u deze de volgende keer nodig heeft.

Hieronder ziet u het verschil in grootte wanneer u een Excel-bestand opslaat met en zonder Pivot Cache.

Hier zijn de stappen om NIET sla de draaicache op:

1. Selecteer een cel in de draaitabel.
2. Klik op het tabblad Analyseren.
3. Klik op het pictogram 'Opties' van de draaitabel.
4. Klik in het dialoogvenster Opties draaitabel op het tabblad Gegevens.
5. In de opties voor draaitabelgegevens, uitvinken ‘Brongegevens opslaan met bestand’. Deze optie zorgt ervoor dat de draaicache niet wordt opgeslagen wanneer u de werkmap opslaat (en helpt bij het verkleinen van de Excel-bestandsgrootte).
6. Vink de optie - 'Gegevens vernieuwen bij openen van bestand' aan. Deze optie zorgt ervoor dat zodra u de werkmap opent, de draaitabel wordt vernieuwd en de draaitabel automatisch wordt gegenereerd. Als u deze optie niet aanvinkt, moet u de draaitabel handmatig vernieuwen om de draaicache te genereren.
7. Klik OK.

Wanneer u het bestand nu opslaat met de draaitabel, ziet u een kleinere bestandsgrootte.

Bewaar de draaicache en verwijder de brongegevens

Een andere manier om de Excel-bestandsgrootte te verkleinen, is door de brongegevens van een draaitabel te verwijderen en alleen de draaitabel te behouden.

Dit werkt omdat een draaicache een spiegelbeeld is van uw gegevens en u geen toegang hebt tot de brongegevens wanneer u wijzigingen aanbrengt in de draaitabel, maar in plaats daarvan gebruikt u de draaicache.

Hieronder vindt u de resultaten toen ik het Excel-bestand opsloeg zonder de brongegevens.

Om dit te doen, verwijdert u gewoon de gegevens (door de gegevens te verwijderen of door het werkblad met de gegevens te verwijderen).

Wanneer u de brongegevens terug wilt krijgen, dubbelklikt u op de cel 'Grand Total' van de draaitabel en genereert deze onmiddellijk de volledige gegevensset in een nieuw werkblad.

Comprimeer het bestand (ZIP IT)

Dit is iets dat u buiten de Excel-toepassing kunt doen.

Wanneer u een Excel-bestand zipt, ziet u onmiddellijk een groottevermindering van ongeveer 10-15%.

U kunt dit gezipte bestand vervolgens via e-mail delen en de persoon die dit ontvangt, kan het bestand uitpakken en de Excel-werkmap gebruiken.

Hier zijn de stappen om een ​​Excel-bestand te zippen (voor Windows):

1. Klik met de rechtermuisknop op het Excel-bestand
2. Beweeg uw cursor over de optie Verzenden naar.
3. Klik op de optie 'Gecomprimeerde (gezipte) map'.

Hiermee wordt een gecomprimeerd bestand gemaakt voor de geselecteerde Excel-werkmap.

U zult ook een vermindering van de bestandsgrootte opmerken (zoals hieronder weergegeven).

Test resultaten

Met deze techniek kon ik de bestandsgrootte van de Excel-werkmap met 8% verkleinen.

Deze techniek werkt beter voor bestanden in het bereik van 20-50 MB. Naarmate de bestandsgrootte toeneemt, is de procentuele reductie niet erg hoog.

Gegevensopmaak verwijderen

Gegevensopmaak, zoals het toepassen van een achtergrondkleur of het toevoegen van randen of het wijzigen van de tekenstijl, kan allemaal bijdragen aan de bestandsgrootte.

Hoewel ik op basis van mijn tests merkte dat het geen enorme impact heeft.

Hieronder staan ​​de formaten van twee Excel-werkmappen, één met 2,6 miljoen gegevenspunten (in één werkblad) en de andere met dezelfde gegevens maar met opmaak (celkleur, rand en cursief).

Test resultaten

Je kunt zien dat het verschil tussen de twee bestanden niet zo groot is (hoewel het wel een impact heeft als je 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 zoeken naar opmaak die u niet nodig hebt. Als u het verwijdert, kunt u wat schijfruimte besparen.

Hier zijn de stappen om alle opmaak onmiddellijk te verwijderen, terwijl de gegevens intact blijven:

1. Selecteer de volledige dataset (waaruit u de opmaak wilt verwijderen).
2. Klik op het tabblad Start.
3. Klik in de groep Bewerken op ‘Wissen’.
4. Klik op 'Formaten wissen'.
5. Bewaar de werkmap.

Voorwaardelijke opmaak verwijderen

Net als gewone gegevensopmaak, draagt ​​voorwaardelijke opmaak ook bij aan de bestandsgrootte.

Op basis van mijn tests merkte ik echter dat het verschil minder is (tenzij je meerdere regels hebt toegepast op meerdere bladen met enorme datasets).

Desalniettemin is het een goed idee om voorwaardelijke opmaak te verwijderen uit elke dataset waar u deze niet nodig hebt.

Afgezien van de grootte is er nog een goede reden om voorwaardelijke opmaak te verwijderen - SPEED.

Voorwaardelijke opmaak is vluchtig en wanneer er een wijziging in het werkblad is, wordt het opnieuw berekend. Dit kan leiden tot een langzamere werkmap.

Test resultaten

Ik heb een bestand gemaakt met 27,2 miljoen gegevenspunten en er 5 voorwaardelijke opmaakregels op toegepast.

Het verschil in grootte lijkt slechts enkele KB's te zijn.

Lees ook:: Tips om trage Excel-spreadsheets snel te maken

Excel-prestatieverhogende tips

Hoewel de bovenstaande methoden u zullen helpen de Excel-bestandsgrootte te verkleinen, volgen hier enkele aanvullende tips om de prestaties van Excel-werkmappen te verbeteren:

1. Vermijd het gebruik van vluchtige formules. Afgezien van het vergroten van de bestandsgrootte, maken vluchtige formules uw werkmappen ook traag. Voorbeelden van vluchtige functies zijn RAND, TODAY, OFFSET, INDIRECT, enz.
2. Als u samenvattingen van een grote gegevensset wilt maken in plaats van formules te gebruiken, kijk dan of u in plaats daarvan een draaitabel kunt gebruiken. Een draaitabel is snel en zeer intuïtief in vergelijking met formules.
3. Als u gegevens uit meerdere bronnen extraheert of gegevens uit meerdere bestanden combineert, gebruikt u 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 draaitabel maken met behulp van de verbindingen die zijn gemaakt met Power Query.
4. Converteer uw tabelgegevens naar een Excel-tabel en gebruik de Excel-tabel in formules. Het zorgt ervoor dat uw werkmap sneller presteert en formules die zijn gemaakt met gestructureerde referenties in Excel-tabel, zijn gemakkelijker te beheren (omdat deze intuïtiever zijn).
5. Vermijd verwijzingen naar hele rijen of kolommen in een formule. Dit zal helpen om uw formules efficiënter te maken.
6. Als u veel formules in uw werkmap hebt, schakelt u Automatische berekening uit en schakelt u over naar handmatige berekening. Door dit te doen, zouden de formules elke keer dat er een wijziging in de werkbladen is, niet opnieuw worden berekend. U kunt deze wijziging aanbrengen door naar het tabblad Formule te gaan en de 'Berekeningsopties' te wijzigen.

Ik hoop dat je de methode en tips in deze tutorial nuttig vindt.

Wat zou ik nog meer aan deze tutorial kunnen toevoegen om het nuttiger te maken? Laat het me weten in het opmerkingengedeelte.