8 sätt att minska Excel -filstorlek (som faktiskt fungerar)

Excel -arbetsböcker har varit kända för att bli smärtsamt långsamma och tunga eftersom mer data/formler/bilder läggs till.

Ibland är det lätt att bli av med den extra flabben och hålla Excel -filstorleken i schack, och ibland är det inte det.

Och eftersom Excel inte är ett webbaserat verktyg, arbetar och samarbetar de flesta av oss genom att skicka Excel-filer via e-post. Och en stor Excel -fil som väger 50 MB kan vara svår att dela med dina kollegor/kunder.

I mitt senaste jobb använde vi Lotus Notes e-postklient, som är snigel-långsam och skickade tunga Excel-filer över det var tortyr.

Även om det inte finns något magiskt sätt att minska storleken på en Excel -arbetsbok, finns det några tekniker som du kan använda för att skära ner några flab.

Obs! Effekten av dessa tekniker för att minska Excel -filstorleken beror på din data. För vissa kan det hjälpa till att minska storleken med 50% och för vissa kan det bara sluta minska storleken med 5%.

Tips för att minska Excel -filstorlek

Nedan finns de tekniker du kan använda för att minska filstorleken i din Excel -arbetsbok.

För varje tips har jag gjort några tester och lagt till ögonblicksbilder för att visa effekten på filstorleken. Resultaten du får varierar beroende på dina data och filer.

Baserat på din arbetsbok kan du använda ett eller flera av dessa tips.

Ta bort onödiga kalkylblad/data

De bästa lösningarna är ofta de enklaste.

Och denna teknik är mer av sunt förnuft och mindre av någon Excel -trollkarl.

Om du har en Excel -arbetsbok som har några kalkylblad som inte behövs (och som inte används i några formler), se om du kan bli av med dem.

Detta har en direkt korrelation med Excel -filstorleken. Så så snart du tar bort några av kalkylblad och sparar arbetsboken, ser du omedelbart en minskad filstorlek.

Testresultat:

Jag skapade en Excel -fil med 10 kalkylblad där varje kalkylblad har 2,6 miljoner datapunkter (26 kolumner med 100 000 datapunkter i varje kolumn).

Den resulterande storleken på denna arbetsbok är 143 MB (och det tar ganska lång tid att spara och öppna).

När jag nu raderade fem ark minskade storleken med hälften (duh).

Som du kan se har data som du har ditt kalkylblad en direkt korrelation med filstorleken.

Konvertera till binärt format (XLSB)

Vet du att bara genom att spara en Excel -fil i XLSB -format kan du minska filstorleken?

Ja det är sant.

Allt du behöver göra är att använda .xlsb -formatet när du sparar en arbetsbok.

Här är stegen för att göra detta:

1. Klicka på fliken Arkiv.
2. Klicka på Spara som.
3. Klicka på Bläddra.
4. Ändra filtypen i dialogrutan Spara som till Excel Binär arbetsbok (.xlsb)

Testresultat

Jag hade en Excel -arbetsbok som tog 14,6 MB utrymme.

Efter att ha konverterat samma fil till XLSB -format kunde jag minska storleken till 10,4 MB (en storleksminskning på ~ 28%).

Vad är XLSB?

När du sparar en Excel -fil (.xlsx eller .xlsm) sparas den i XML -formatet.

Till exempel, om jag ändrar filtillägget för XLSX -filen till ZIP och sedan öppnar den, är det här jag får.

Och om jag går vidare i mappen ‘xl’ och sedan går i kalkylbladsmappen, visar det mig kalkylbladet i den arbetsboken (och dessa kalkylblad sparas som ett XML -dokument).

Låt oss nu göra samma sak med en XLSB -fil.

Så jag ändrar först filtillägget till ZIP, öppnar det sedan och letar upp sheet1. Här är vad du får:

Du kommer att märka att både XLSX och XLSB sparar kalkylbladet i ett annat format.

XLSX/XLSM sparar den som en XML -fil och XLSB sparar den som en binär (.bin) fil.

Och utan att bli för teknisk är XML -filer stora i jämförelse med en binär fil.

Så när du sparar din Excel -arbetsbok i XLSB -format minskar filstorleken. I vårt exempel ovan var storleksminskningen ~ 30%.

XLSB mot XLSX/XLSM

När det gäller att använda XLSB -format är storleksreduktion en stor fördel. Men du måste vara lite försiktig när du arbetar med XLSB -format.

Fördelar med XLSB -format:

1. Det minskar Excel -filstorleken. Detta kan vara till hjälp när du har stora filer och du vill dela det via e -post. Du kan snabbt minska storleken genom att spara den i XLSB.
2. XLSB -filer tenderar att öppna och spara snabbare än XML -filer. Baserat på de begränsade tester jag gjorde öppnade XLSB-filer och sparade 25% -50% snabbare. Denna skillnad märks bara i stora Excel -filer. Med mindre filer är både XLSB och XLSX snabba.

Även om XLSB ser bra ut, finns det några anledningar till att du ska hålla dig till att använda XLSX/XLSM -filer:

1. Med XLSB -format har du inget sätt att veta om det har ett makro eller inte. Detta gör det mer riskabelt eftersom någon kan använda den för att köra skadlig kod.
2. XLSB-filer är kodade i proprietär bin-filformat medan XML är ett läsbart filformat med öppen källkod. Det betyder att om du använder tredjepartsverktyg med Excel är det bättre att använda XML-format.
3. Power Query kan inte läsa data från en XLSB -fil. Så om du använder Power Query är det bättre att ha dina data i XLSX/XLSM -format.

Som en allmän regel, om din filstorlek är liten (mindre än 5 MB), är det bättre att hålla sig till XLSX/XLSM -format.

Baserat på vad jag hört från människor och läst på många forum, föredrar många att använda XLSB som filformat när det gäller att använda Excel.

Här är en bra artikel om att förstå XLSB -formatet.

Ta bort onödiga formler

För många formler kommer att svälla upp din arbetsbokfilstorlek.

Så om du har en datamängd som du behöver, men inte behöver formlerna i den, är det bättre att konvertera dessa formler till värden.

Här är stegen för att snabbt konvertera formler till värden i Excel:

1. Välj hela kalkylbladet/datauppsättningen.
2. tryck på F5 nyckel.
3. Klicka på "Special" i dialogrutan Gå till.
4. Välj Formler i dialogrutan Gå till special.
5. Klicka på OK.

Stegen ovan skulle välja alla celler som har en formel i den.

Använd nu stegen nedan för att konvertera dessa formler till värden:

1. Kopiera de markerade cellerna (kontroll + C)
2. Klicka på fliken Hem.
3. Klicka på Klistra in i Urklipp -gruppen.
4. Klicka på ikonen Klistra in värde.

Stegen ovan skulle konvertera alla celler med formler till värden.

Testresultat

Jag skapade en Excel -fil med 2,7 miljoner datapunkter (1048576*26) och lade sedan till tre kolumner med formler (SUMMA, RÄKNA och GENomsnitt).

Sedan konverterade jag dessa formler till värden och sparade filen med ett annat namn.

Nedan är resultatet:

Filen utan formler är ~ 8% mindre i storlek (och observera att jag bara hade 3 kolumner med formler).

Om du har många formler kan det lägga till storleken.

NOTERA: Flyktiga formler kan verkligen svälla upp din filstorlek. Som en del av testet, när jag ersatte SUM -formeln med en flyktig formel (OFFSET), ledde det till ett hopp i filstorleken (från 186 MB till 198 MB). Så om du har flyktiga formler som du kan ersätta med icke-flyktiga formler, eller kan konvertera dessa till värden, hjälper det dig att minska filstorleken.

Komprimera bilder (före och efter uppladdning)

Om du arbetar med Excel -filer som innehåller bilder, måste du optimera dessa bilder och se till att storleken är mindre.

Problemet med bilder är att när du komprimerar dessa blir det också en minskning av kvaliteten, vilket du kanske inte vill ha.

När du arbetar med bilder är det bästa sättet att hålla storleken på din Excel -fil låg genom att komprimera bilderna innan du sätter in den i Excel.

Ett bra onlineverktyg för att göra detta är TinyPNG (eller du kan använda Photoshop eller något annat liknande verktyg).

Det är ett gratis verktyg som kan minska storleken på en bild med upp till 90% och fortfarande behålla en anständig bildkvalitet.

Nedan är skärmdumpen där jag kunde komprimera en bild med storleken 3,9 MB till 331 KB med minimal förlust av kvalitet.

När du har laddat upp dessa komprimerade bilder till Excel kan du komprimera det ytterligare med det inbyggda alternativet i Excel.

Här är stegen för att komprimera en bild i Excel:

1. Välj bilden som du vill komprimera.
2. Klicka på fliken Bildverktyg Format. Detta är en kontextuell flik som bara visas när du klickar på en bild.
3. Klicka på alternativet "Komprimera bild" i gruppen Justera.
4. I dialogrutan Komprimera bilder:
   • Avmarkera "Använd endast på den här bilden" om du vill komprimera alla bilder i arbetsboken.
   • Välj E -post (96 ppi) för maximal komprimering.
5. Klicka på OK.
6. Spara filen.

Detta kommer att komprimera alla bilder och hjälpa dig att minska filstorleken i din Excel -arbetsbok.

Testresultat:

Jag använde alternativet komprimera imge i Excel med en bild.

Nedan följer resultaten:

Genom att använda alternativet komprimera bild minskas filstorleken från 445 KB till 43 KB (~ 90% minskning av filstorleken).

Detta beror också på bilden. En bild som redan är komprimerad kanske inte leder till en enorm storleksminskning.

Ta bort Pivot Cache för att spara utrymme

Om du arbetar med pivottabeller och kämpar med stora Excel -filer kan kunskap om Pivot -cache vara användbart.

När du skapar en pivottabell med en datauppsättning, skapar Excel automatiskt Pivot -cachen.

Pivotcache är ett objekt som innehåller en kopia av datakällan.

Även om du inte kan se den, är den en del av arbetsboken och är ansluten till pivottabellen. När du gör några ändringar i pivottabellen använder den inte datakällan, den använder snarare Pivot -cachen.

Medan du tror att du är direkt länkad till källdata, kommer du i verkligheten åt pivotcachen (och inte källdatan) när du gör ändringar i pivottabellen.

Eftersom du nu skapar en kopia av datamängden tar det lite utrymme.

Så så snart du skapar ett pivottabell kommer du att märka att storleken på Excel -filen ökar.

I exemplet ovan, så snart jag lägger till pivottabellen, ökar filstorleken två gånger.

Nu finns det två sätt att minska filstorleken när du arbetar med pivottabeller.

1. Behåll källdata och ta bort Pivot Cache
2. Behåll Pivot Cache och ta bort källdata

Låt oss titta på båda dessa tekniker.

Behåll källdata och ta bort pivotcache

När du är klar med pivottabellen och sparar den kan du tvinga Excel att bara spara källdata och den resulterande pivottabellen, och inte pivotcachen.

Om du gör detta blir din Excel -fil lättare, och du har ett sätt att återskapa Pivot -cachen när du behöver den nästa gång.

Nedan visas skillnaden i storlek när du sparar en Excel -fil med och utan Pivot Cache.

Här är stegen till INTE spara Pivot Cache:

1. Markera vilken cell som helst i pivottabellen.
2. Klicka på fliken Analysera.
3. Klicka på pivottabellens "Alternativ" -ikon.
4. Klicka på fliken Data i dialogrutan Alternativ för pivottabell.
5. I alternativen för pivottabelldata, avmarkera "Spara källdata med fil". Detta alternativ säkerställer att Pivot Cache inte sparas när du sparar arbetsboken (och hjälper till att minska Excel -filstorlek).
6. Markera alternativet - "Uppdatera data när du öppnar filen". Detta alternativ säkerställer att så fort du öppnar arbetsboken uppdateras pivottabellen och Pivot -cachen genereras automatiskt. Om du inte markerar det här alternativet måste du uppdatera pivottabellen manuellt för att generera Pivot -cachen.
7. Klicka på OK.

När du nu sparar filen med pivottabellen märker du en minskad filstorlek.

Behåll pivotcachen och ta bort källdata

Ett annat sätt att minska Excel -filstorleken är att radera källdata från en pivottabell och bara behålla Pivot -cachen.

Detta fungerar som en Pivot Cache är en spegelbild av dina data och du får inte tillgång till källdata när du gör ändringar i pivottabellen, istället använder du Pivot Cache.

Nedan är resultat när jag sparade Excel -filen utan källdata.

För att göra detta, ta bara bort data (antingen genom att radera data eller kalkylbladet som har data).

När du vill få tillbaka källdatan dubbelklickar du på cellen "Grand Total" i pivottabellen och den genererar genast hela datamängden i ett nytt kalkylblad.

Komprimera filen (ZIP IT)

Detta är något du kan göra utanför Excel -applikationen.

När du zipar en Excel-fil ser du en storleksminskning på cirka 10-15% omedelbart.

Du kan sedan dela den här zippade filen via e -post, och personen som tar emot den kan packa upp filen och använda Excel -arbetsboken.

Här är stegen för att zippa en Excel -fil (för Windows):

1. Högerklicka på Excel-filen
2. Håll muspekaren på alternativet Skicka till.
3. Klicka på alternativet "Komprimerad (zippad)".

Detta skapar en zippad fil för den valda Excel -arbetsboken.

Du kommer också att märka en minskning av filstorleken (som visas nedan).

Testresultat

Med denna teknik kunde jag minska filstorleken i Excel -arbetsboken med 8%.

Denna teknik fungerar bättre för filer som ligger i intervallet 20-50 MB. När filstorleken ökar är procentminskningen inte särskilt hög.

Ta bort dataformatering

Dataformatering, till exempel att använda en bakgrundsfärg, eller lägga till kanter, eller ändra teckensnittsstil, kan alla läggas till i filstorleken.

Även om jag, utifrån mina tester, märkte att det inte har någon stor inverkan.

Nedan visas storleken på två Excel -arbetsböcker, en med 2,6 miljoner datapunkter (i ett kalkylblad) och den andra med samma data men formatering tillämpad på den (cellfärg, kant och kursiv).

Testresultat

Du kan se att skillnaden mellan de två filerna inte är så stor (även om det påverkar när du har mycket data).

Filen med data är 14,6 MB och filen med data och formatering är 14,9 MB.

Så om du vill minska filstorleken i din Excel -arbetsbok kan du leta efter formatering du inte behöver. Om du tar bort det kan du spara lite diskutrymme.

Här är stegen för att omedelbart ta bort all formatering, samtidigt som data hålls intakta:

1. Markera hela datamängden (från vilken du vill ta bort formatering).
2. Klicka på fliken Hem.
3. Klicka på "Rensa" i gruppen Redigering.
4. Klicka på "Rensa format".
5. Spara arbetsboken.

Ta bort villkorlig formatering

Precis som vanlig dataformatering lägger villkorlig formatering också till filstorleken.

Baserat på mina tester märkte jag dock att skillnaden är mindre (om du inte har flera regler tillämpade på flera blad med enorma datamängder).

Ändå är det en bra idé att ta bort villkorlig formatering från alla datamängder där du inte behöver det.

Förutom storleken finns det en annan bra anledning att ta bort villkorlig formatering - SPEED.

Villkorsstyrd formatering är flyktig och när det blir någon förändring i kalkylbladet beräknas det om. Detta kan leda till en långsammare arbetsbok.

Testresultat

Jag skapade en fil med 27,2 miljoner datapunkter och tillämpade fem villkorliga formateringsregler på den.

Skillnaden i storlek verkar bara vara några kB.

Läs också: Tips för att göra långsamma Excel -kalkylblad snabba

Excel Performance Booster Tips

Även om metoderna ovan hjälper dig att minska Excel -filstorlek, här är några ytterligare tips för att förbättra prestanda för Excel -arbetsböcker:

1. Undvik att använda flyktiga formler. Bortsett från att öka filstorleken gör flyktiga formler dig också arbetsböcker långsamma. Exempel på flyktiga funktioner inkluderar RAND, TODAY, OFFSET, INDIRECT, etc.
2. Om du behöver skapa sammanfattningar av en stor datamängd, se om du kan använda en pivottabell istället för att använda formler. Ett pivottabell är snabbt och mycket intuitivt jämfört med formler.
3. Om du extraherar data från flera källor eller kombinerar data från flera filer, använd Power Query för att hämta och transformera data. Du kan spara data som en Power Query -anslutning istället för att behålla dem i kalkylbladet. Du kan enkelt skapa pivottabell med anslutningarna som skapats med Power Query.
4. Konvertera dina tabelldata till en Excel -tabell och använd Excel -tabellen i formler. Det gör att din arbetsbok fungerar snabbare och formler som skapats med hjälp av Excel -tabellstrukturerade referenser är lättare att hantera (eftersom dessa är mer intuitiva).
5. Undvik att referera till hela raden eller kolumnen i en formel. Detta hjälper dina formler att bli mer effektiva.
6. Om du har många formler i din arbetsbok stänger du av automatisk beräkning och växlar till manuell beräkning. Genom att göra detta, varje gång det ändras i kalkylblad, skulle formlerna inte räkna om. Du kan göra den här ändringen genom att gå till fliken Formel och ändra "Beräkningsalternativ".

Hoppas att du hittar metoden och tipsen i denna handledning användbara.

Vad mer kan jag lägga till i denna handledning för att göra den mer användbar? Meddela mig i kommentarsfältet.