8 måter å redusere Excel -filstørrelse (som faktisk fungerer)

Excel -arbeidsbøker har vært kjent for å bli smertefullt sakte og tunge etter hvert som flere data/formler/bilder legges til.

Noen ganger er det lett å bli kvitt den ekstra flabben og holde excel -filstørrelsen i sjakk, og noen ganger er det ikke det.

Og siden Excel ikke er et nettbasert verktøy, jobber og samarbeider de fleste av oss ved å sende Excel-filer via e-post. Og en stor Excel -fil som veier 50 MB kan være vanskelig å dele med dine kolleger/klienter.

I min siste jobb brukte vi Lotus Notes e-postklient, som er snegle-treg, og sendte tunge Excel-filer over det som var tortur.

Selv om det ikke er noen magisk måte å redusere størrelsen på en Excel -arbeidsbok, er det noen få teknikker du kan bruke til å kutte ned noen flab.

Merk: Virkningen av disse teknikkene for å redusere Excel -filstørrelse vil avhenge av dataene dine. For noen kan det bidra til å redusere størrelsen med 50%, og for noen kan det bare ende opp med å redusere størrelsen med 5%.

Tips for å redusere Excel -filstørrelse

Nedenfor er teknikkene du kan bruke for å redusere filstørrelsen til Excel -arbeidsboken.

For hvert tips har jeg testet og lagt til øyeblikksbilder for å vise effekten på filstørrelsen. Resultatene du får vil variere avhengig av dataene og filene dine.

Basert på arbeidsboken din, kan du bruke ett eller flere av disse tipsene.

Fjern unødvendige regneark/data

De beste løsningene er ofte de enkleste.

Og denne teknikken er mer av sunn fornuft og mindre av noen Excel -veiviser.

Hvis du har en Excel -arbeidsbok som har noen regneark som ikke er nødvendige (og som ikke brukes i noen formler), må du se om du kan bli kvitt dem.

Dette har en direkte korrelasjon med Excel -filstørrelsen. Så så snart du sletter noen av regnearkene og lagrer arbeidsboken, vil du umiddelbart se en redusert filstørrelse.

Testresultater:

Jeg opprettet en Excel -fil med 10 regneark der hvert regneark har 2,6 millioner datapunkter (26 kolonner med 100 000 datapunkter i hver kolonne).

Den resulterende størrelsen på denne arbeidsboken er 143 MB (og det tar ganske lang tid å lagre og åpne).

Når jeg nå slettet fem ark, ble størrelsen redusert med halvparten (duh).

Som du kan se, har dataene du har regnearket en direkte sammenheng med filstørrelsen.

Konverter til binært format (XLSB)

Vet du at bare ved å lagre en Excel -fil i XLSB -format kan du redusere filstørrelsen?

Ja det er sant.

Alt du trenger å gjøre er å bruke .xlsb -formatet mens du lagrer en arbeidsbok.

Her er trinnene for å gjøre dette:

1. Klikk kategorien Fil.
2. Klikk på Lagre som.
3. Klikk på Bla gjennom.
4. I dialogboksen Lagre som endrer du filtypen til Excel Binary Workbook (.xlsb)

Testresultater

Jeg hadde en Excel -arbeidsbok som tok 14,6 MB plass.

Etter å ha konvertert den samme filen til XLSB -formatet, klarte jeg å redusere størrelsen til 10,4 MB (en størrelsesreduksjon på ~ 28%).

Hva er XLSB?

Når du lagrer en Excel -fil (.xlsx eller .xlsm), blir den lagret i XML -formatet.

For eksempel, hvis jeg endrer filtypen til XLSX -filen til ZIP og deretter åpner den, er dette det jeg får.

Og hvis jeg går videre til ‘xl’ -mappen og deretter går til regnearkmappen, viser den meg regnearkene i arbeidsboken (og disse regnearkene lagres som et XML -dokument).

La oss nå gjøre det samme med en XLSB -fil.

Så jeg endrer først filtypen til ZIP, åpner den og finner ark1. Her er hva du får:

Du vil legge merke til at både XLSX og XLSB lagrer regnearket i et annet format.

XLSX/XLSM lagrer den som en XML -fil og XLSB lagrer den som en binær (.bin) fil.

Og uten å bli for teknisk, er XML -filer store i forhold til en binær fil.

Så når du lagrer Excel -arbeidsboken din i XLSB -format, reduseres filstørrelsen. I vårt eksempel ovenfor var størrelsesreduksjonen ~ 30%.

XLSB mot XLSX/XLSM

Når det gjelder bruk av XLSB -format, er størrelsesreduksjon en stor fordel. Men du må være litt forsiktig når du arbeider med XLSB -formatet.

Fordeler med XLSB -format:

1. Det reduserer Excel -filstørrelsen. Dette kan være nyttig når du har store filer og du vil dele det via e -post. Du kan raskt redusere størrelsen ved å lagre den i XLSB.
2. XLSB -filer har en tendens til å åpne og lagre raskere enn XML -filer. Basert på de begrensede testene jeg gjorde, åpnet XLSB-filer og sparte 25% -50% raskere. Denne forskjellen er bare merkbar i store Excel -filer. Med mindre filer er både XLSB og XLSX raske.

Selv om XLSB ser bra ut, er det noen få grunner til at du bør holde deg til å bruke XLSX/XLSM -filer:

1. Med XLSB -format har du ingen mulighet til å vite om den har en makro eller ikke. Dette gjør det mer risikabelt ettersom noen kan bruke det til å utføre ondsinnet kode.
2. XLSB-filer er kodet i proprietær bin-filformat mens XML er et lesbart filformat med åpen kildekode. Dette betyr at hvis du bruker tredjepartsverktøy med Excel, er det bedre å bruke XML-format.
3. Power Query kan ikke lese data fra en XLSB -fil. Så hvis du bruker Power Query, er det bedre å ha dataene dine i XLSX/XLSM -formater.

Som en generell regel, hvis filstørrelsen er liten (mindre enn 5 MB), er det bedre å holde seg til XLSX/XLSM -formater.

Basert på det jeg hørte fra folk og leste på mange fora, foretrekker mange å bruke XLSB som filformat når det gjelder bruk av Excel.

Her er en god artikkel om forståelse av XLSB -formatet.

Fjern unødvendige formler

For mange formler vil oppblåse arbeidsbokens filstørrelse.

Så hvis du har et datasett du trenger, men ikke trenger formlene i det, er det bedre å konvertere disse formlene til verdier.

Her er trinnene for å raskt konvertere formler til verdier i Excel:

1. Velg hele regnearket/datasettet.
2. trykk F5 nøkkel.
3. Klikk på "Spesiell" i dialogboksen Gå til.
4. Velg Formler i dialogboksen Gå til spesiell.
5. Klikk OK.

Trinnene ovenfor vil velge alle cellene som har en formel.

Bruk nå trinnene nedenfor for å konvertere disse formlene til verdier:

1. Kopier de valgte cellene (kontroll + C)
2. Klikk på kategorien Hjem.
3. Klikk på Lim inn i Utklippstavle -gruppen.
4. Klikk på Lim inn verdi -ikonet.

Trinnene ovenfor vil konvertere alle cellene med formler til verdier.

Testresultater

Jeg opprettet en Excel -fil med 2,7 millioner datapunkter (1048576*26) og la deretter til tre kolonner med formler (SUM, TELL og GJENNOMSNITT).

Deretter konverterte jeg disse formlene til verdier og lagret filen med et annet navn.

Nedenfor er resultatet:

Filen uten formler er ~ 8% mindre i størrelse (og merk at jeg bare hadde 3 kolonner med formler).

Hvis du har mange formler, kan den øke størrelsen.

MERK: Flyktige formler kan virkelig oppblåse filstørrelsen din. Som en del av testen, da jeg erstattet SUM -formelen med en flyktig formel (OFFSET), førte det til et hopp i filstørrelsen (fra 186 MB til 198 MB). Så hvis du har flyktige formler som du kan erstatte med ikke-flyktige formler, eller kan konvertere disse til verdier, vil det hjelpe deg med å redusere filstørrelsen.

Komprimer bilder (før og etter opplasting)

Hvis du jobber med Excel -filer som har bilder i, må du optimalisere disse bildene og sørge for at størrelsen er mindre.

Problemet med bilder er at når du komprimerer disse, er det også en reduksjon i kvaliteten, som du kanskje ikke vil ha.

Når du arbeider med bilder, er den beste måten å holde størrelsen på Excel -filen lav ved å komprimere bildene før du setter den inn i Excel.

Et godt online verktøy for å gjøre dette er TinyPNG (eller du kan bruke Photoshop eller et annet lignende verktøy).

Det er et gratis verktøy som kan redusere størrelsen på et bilde med opptil 90% og fortsatt opprettholder anstendig bildekvalitet.

Nedenfor er skjermbildet der jeg klarte å komprimere et bilde med størrelsen 3,9 MB til 331 KB med minimalt tap av kvalitet.

Når du har lastet opp disse komprimerte bildene til Excel, kan du komprimere dem ytterligere ved hjelp av det innebygde alternativet i Excel.

Her er trinnene for å komprimere et bilde i Excel:

1. Velg bildet du vil komprimere.
2. Klikk på kategorien Bildeverktøy Format. Dette er en kontekstuell fane som bare vises når du klikker på et bilde.
3. I Juster -gruppen klikker du på alternativet "Komprimer bilde".
4. I dialogboksen Komprimer bilder:
   • Fjern merket for "Bruk bare på dette bildet" hvis du vil komprimere alle bildene i arbeidsboken.
   • Velg E -post (96 ppi) for maksimal komprimering.
5. Klikk OK.
6. Lagre filen.

Dette vil komprimere alle bildene og hjelpe deg med å redusere filstørrelsen til Excel -arbeidsboken.

Testresultater:

Jeg brukte alternativet komprimere imge i Excel ved å bruke ett bilde.

Nedenfor er resultatene:

Ved å bruke komprimeringsbildealternativet reduseres filstørrelsen fra 445 KB til 43 KB (~ 90% reduksjon i filstørrelse).

Dette vil også avhenge av bildet. Et bilde som allerede er komprimert, kan ikke føre til en enorm størrelsesreduksjon.

Fjern Pivot Cache for å spare plass

Hvis du jobber med pivottabeller og sliter med store Excel -filer, kan det være nyttig å vite om Pivot -cache.

Når du oppretter en pivottabell med et datasett, oppretter Excel automatisk pivotbufferen.

Pivot -cache er et objekt som inneholder en kopi av datakilden.

Selv om du ikke kan se den, er den en del av arbeidsboken og er koblet til pivottabellen. Når du gjør noen endringer i pivottabellen, bruker den ikke datakilden, den bruker snarere pivotbufferen.

Mens du tror at du er direkte koblet til kildedataene, får du i virkeligheten tilgang til pivotbufferen (og ikke kildedataene) når du gjør endringer i pivottabellen.

Siden du lager en kopi av datasettet, tar det litt plass.

Så så snart du oppretter et pivottabell, vil du legge merke til at størrelsen på Excel -filen øker.

I eksemplet ovenfor, så snart jeg legger til pivottabellen, øker størrelsen på filen to ganger.

Nå er det to måter du kan redusere filstørrelsen på når du arbeider med pivottabeller.

1. Behold kildedataene og slett Pivot Cache
2. Behold Pivot Cache og slett kildedataene

La oss se på begge disse teknikkene.

Behold kildedataene og slett pivotbufferen

Når du er ferdig med pivottabellen og lagrer den, kan du tvinge Excel til bare å lagre kildedataene og den resulterende pivottabellen, og ikke pivotbufferen.

Hvis du gjør dette, blir Excel -filen lettere, og du har en måte å gjenskape Pivot -bufferen når du trenger den neste gang.

Nedenfor er forskjellen i størrelse når du lagrer en Excel -fil med og uten Pivot Cache.

Her er trinnene til IKKE lagre Pivot Cache:

1. Velg en celle i pivottabellen.
2. Klikk på kategorien Analyser.
3. Klikk på ikonet Pivot Table 'Options'.
4. Klikk på kategorien Data i dialogboksen Alternativer for pivottabell.
5. I alternativene for pivottabelldata, fjern merket ‘Lagre kildedata med fil’. Dette alternativet sikrer at Pivot Cache ikke blir lagret når du lagrer arbeidsboken (og hjelper til med å redusere Excel -filstørrelse).
6. Merk av for alternativet - "Oppdater data når du åpner filen". Dette alternativet sikrer at så snart du åpner arbeidsboken, oppdateres pivottabellen og Pivot -bufferen genereres automatisk. Hvis du ikke merker av for dette alternativet, må du oppdatere pivottabellen manuelt for å generere pivotbufferen.
7. Klikk OK.

Når du lagrer filen med pivottabellen, vil du merke en redusert filstørrelse.

Behold pivotbufferen og slett kildedataene

En annen måte å redusere Excel -filstørrelse på er å slette kildedataene til et pivottabell og bare beholde pivotbufferen.

Dette fungerer som en Pivot Cache er et speilbilde av dataene dine, og du får ikke tilgang til kildedataene når du gjør endringer i pivottabellen, i stedet bruker du Pivot Cache.

Nedenfor er resultatene da jeg lagret Excel -filen uten kildedata.

For å gjøre dette, bare slett dataene (enten ved å slette dataene eller regnearket som har dataene).

Når du vil få kildedataene tilbake, dobbeltklikker du på cellen ‘Grand Total’ i pivottabellen, og det genererer umiddelbart hele datasettet i et nytt regneark.

Komprimer filen (ZIP IT)

Dette er noe du kan gjøre utenfor Excel -applikasjonen.

Når du zipper en Excel-fil, ser du en størrelsesreduksjon på omtrent 10-15% umiddelbart.

Du kan deretter dele denne zippede filen til via e -post, og personen som mottar denne kan pakke ut filen og bruke Excel -arbeidsboken.

Her er trinnene for å zip en Excel -fil (for Windows):

1. Høyreklikk på Excel-filen
2. Hold markøren på alternativet Send til.
3. Klikk på alternativet ‘Komprimert (zippet) mappe’.

Dette vil opprette en zip -fil for den valgte Excel -arbeidsboken.

Du vil også merke en reduksjon i filstørrelsen (som vist nedenfor).

Testresultater

Med denne teknikken klarte jeg å redusere filstørrelsen til Excel -arbeidsboken med 8%.

Denne teknikken fungerer bedre for filer som er i området 20-50 MB. Etter hvert som filstørrelsen øker, er ikke prosentvis reduksjon særlig høy.

Fjern dataformatering

Dataformatering, for eksempel å bruke en bakgrunnsfarge, eller legge til kanter, eller endre skriftstil, kan alle legge til filstørrelsen.

Selv om jeg, basert på testene mine, la merke til at det ikke har stor innvirkning.

Nedenfor er størrelsene på to Excel -arbeidsbøker, en med 2,6 millioner datapunkter (i ett regneark), og den andre med samme data, men formatering brukt på den (cellefarge, kant og kursiv).

Testresultater

Du kan se at forskjellen mellom de to filene ikke er så stor (selv om det påvirker når du har mye data).

Filen med data er 14,6 MB og filen med data og formatering er 14,9 MB.

Så hvis du vil redusere filstørrelsen på Excel -arbeidsboken, kan du se etter formatering du ikke trenger. Hvis du fjerner den, kan du spare litt diskplass.

Her er trinnene for å fjerne all formatering umiddelbart, mens dataene forblir intakte:

1. Velg hele datasettet (som du vil fjerne formateringen fra).
2. Klikk på kategorien Hjem.
3. Klikk på "Slett" i redigeringsgruppen.
4. Klikk på "Fjern formater".
5. Lagre arbeidsboken.

Fjern betinget formatering

På samme måte som vanlig dataformatering, legger betinget formatering også til filstørrelsen.

Basert på testene mine la jeg imidlertid merke til at forskjellen er mindre (med mindre du har flere regler på flere ark med store datasett).

Likevel er det en god idé å fjerne betinget formatering fra alle datasett der du ikke trenger det.

Bortsett fra størrelsen er det en annen god grunn til å fjerne betinget formatering - SPEED.

Betinget formatering er flyktig, og når det er endringer i regnearket, beregnes det på nytt. Dette kan føre til en tregere arbeidsbok.

Testresultater

Jeg opprettet en fil med 27,2 millioner datapunkter og brukte fem betingede formateringsregler på den.

Forskjellen i størrelse ser ut til å være bare noen få KB.

Les også: Tips for å gjøre sakte Excel -regneark raskt

Excel Performance Booster Tips

Selv om metodene ovenfor hjelper deg med å redusere Excel -filstørrelse, er det noen flere tips for å forbedre ytelsen til Excel -arbeidsbøker:

1. Unngå å bruke flyktige formler. Bortsett fra å øke filstørrelsen, gjør flyktige formler deg også sakte i arbeidsbøkene. Eksempler på flyktige funksjoner inkluderer RAND, TODAY, OFFSET, INDIRECT, etc.
2. Hvis du har behov for å lage oppsummeringer av et stort datasett, må du se om du kan bruke en pivottabell i stedet for å bruke formler. Et pivottabell er raskt og veldig intuitivt sammenlignet med formler.
3. Hvis du trekker ut data fra flere kilder eller kombinerer data fra flere filer, bruker du Power Query til å hente og transformere dataene. Du kan lagre dataene som en Power Query -tilkobling i stedet for å beholde dem i regnearket. Du kan enkelt opprette pivottabell ved å bruke tilkoblingene som er opprettet med Power Query.
4. Konverter tabelldataene til en Excel -tabell og bruk Excel -tabellen i formler. Det får arbeidsboken til å fungere raskere og formler som er opprettet ved hjelp av Excel -tabell, strukturerte referanser er lettere å administrere (siden disse er mer intuitive).
5. Unngå å referere til hele raden eller kolonnen i en formel. Dette vil hjelpe formlene dine til å bli mer effektive.
6. Hvis du har mange formler i arbeidsboken, slår du av Automatisk beregning og bytter til manuell beregning. Ved å gjøre dette, ville formlene ikke beregnes på nytt hver gang det er en endring i regnearkene. Du kan gjøre denne endringen ved å gå til Formel -fanen og endre "Beregningsalternativer".

Håper du finner metoden og tipsene i denne opplæringen nyttige.

Hva mer kan jeg legge til i denne opplæringen for å gjøre den mer nyttig? Gi meg beskjed i kommentarfeltet.