8 modi per ridurre le dimensioni del file Excel (che funzionano davvero)

È noto che le cartelle di lavoro di Excel diventano dolorosamente lente e pesanti man mano che vengono aggiunti più dati/formule/immagini.

A volte, è facile sbarazzarsi della ciccia in più e tenere sotto controllo le dimensioni del file excel, a volte no.

E poiché Excel non è uno strumento basato sul Web, la maggior parte di noi lavora e collabora inviando file Excel tramite e-mail. E un file Excel di grandi dimensioni che pesa 50 MB può essere difficile da condividere con i tuoi colleghi/clienti.

Nel mio ultimo lavoro, abbiamo usato il client di posta elettronica Lotus Notes, che è lento come una lumaca, e l'invio di pesanti file Excel su questo era una tortura.

Sebbene non esista un modo magico per ridurre le dimensioni di una cartella di lavoro di Excel, esistono alcune tecniche che è possibile utilizzare per ridurre la ciccia.

Nota: l'impatto di queste tecniche per ridurre le dimensioni del file Excel dipende dai dati. Per alcuni, può aiutare a ridurre le dimensioni del 50% e per alcuni, potrebbe finire per ridurre le dimensioni solo del 5%.

Suggerimenti per ridurre le dimensioni del file Excel

Di seguito sono riportate le tecniche che è possibile utilizzare per ridurre le dimensioni del file della cartella di lavoro di Excel.

Per ogni suggerimento, ho eseguito alcuni test e ho aggiunto le istantanee per mostrarti l'impatto sulla dimensione del file. I risultati che otterrai variano a seconda dei dati e dei file.

In base alla tua cartella di lavoro, puoi utilizzare uno o più di questi suggerimenti.

Rimuovi fogli di lavoro/dati non necessari

Le soluzioni migliori sono spesso le più semplici.

E questa tecnica è più di buon senso e meno di qualsiasi stregoneria di Excel.

Se hai una cartella di lavoro di Excel che contiene alcuni fogli di lavoro che non sono necessari (e non vengono utilizzati in nessuna formula), vedi se riesci a sbarazzartene.

Questo ha una correlazione diretta con la dimensione del file Excel. Quindi, non appena elimini alcuni fogli di lavoro e salvi la cartella di lavoro, vedrai immediatamente una dimensione del file ridotta.

Risultati del test:

Ho creato un file Excel con 10 fogli di lavoro in cui ogni foglio di lavoro ha 2,6 milioni di punti dati (26 colonne con 100.000 punti dati in ogni colonna).

La dimensione risultante di questa cartella di lavoro è di 143 MB (e ci vuole un po' di tempo per salvarla e aprirla).

Ora, quando ho cancellato cinque fogli, la dimensione si è ridotta della metà (duh).

Come puoi vedere, i dati che hai nel tuo foglio di lavoro hanno una correlazione diretta con la dimensione del file.

Converti in formato binario (XLSB)

Lo sai che il solo salvataggio di un file Excel in formato XLSB può ridurre le dimensioni del file?

Si è vero.

Tutto quello che devi fare è durante il salvataggio di una cartella di lavoro, utilizzare il formato .xlsb.

Ecco i passaggi per farlo:

1. Fare clic sulla scheda File.
2. Fare clic su Salva con nome.
3. Fare clic su Sfoglia.
4. Nella finestra di dialogo Salva con nome, modificare il tipo di file in Cartella di lavoro binaria di Excel (.xlsb)

Risultati del test

Avevo una cartella di lavoro di Excel che occupava 14,6 MB di spazio.

Dopo aver convertito lo stesso file nel formato XLSB, sono stato in grado di ridurre le dimensioni a 10,4 MB (una riduzione delle dimensioni di ~ 28%).

Cos'è XLSB?

Quando salvi un file Excel (.xlsx o .xlsm), viene salvato nel formato XML.

Ad esempio, se cambio l'estensione del file XLSX in ZIP e poi lo apro, questo è quello che ottengo.

E se vado ulteriormente nella cartella "xl" e poi vado nella cartella del foglio di lavoro, mi mostra i fogli di lavoro in quella cartella di lavoro (e questi fogli di lavoro vengono salvati come documento XML).

Ora facciamo lo stesso con un file XLSB.

Quindi prima cambio l'estensione del file in ZIP, quindi lo apro e trovo sheet1. Ecco cosa ottieni:

Noterai che sia XLSX che XLSB salvano il foglio di lavoro in un formato diverso.

XLSX/XLSM lo salva come file XML e XLSB lo salva come file binario (.bin).

E senza diventare troppo tecnici, i file XML sono di grandi dimensioni rispetto a un file binario.

Quindi, quando salvi la cartella di lavoro di Excel nel formato XLSB, la dimensione del file viene ridotta. Nel nostro esempio sopra, la riduzione delle dimensioni è stata del 30% circa.

XLSB contro XLSX/XLSM

Quando si tratta di utilizzare il formato XLSB, la riduzione delle dimensioni è un enorme vantaggio. Ma devi essere un po' cauto quando lavori con il formato XLSB.

Vantaggi del formato XLSB:

1. Riduce la dimensione del file Excel. Questo può essere utile quando si hanno file di grandi dimensioni e si desidera condividerli tramite e-mail. Puoi ridurre rapidamente le dimensioni salvandole in XLSB.
2. I file XLSB tendono ad aprirsi e salvare più velocemente del file XML. Sulla base dei test limitati che ho fatto, i file XLSB si aprivano e risparmiavano dal 25% al ​​50% più velocemente. Questa differenza è evidente solo nei file Excel di grandi dimensioni. Con file più piccoli, sia XLSB che XLSX sono veloci.

Sebbene XLSB abbia un bell'aspetto, ci sono alcuni motivi per cui dovresti continuare a utilizzare i file XLSX/XLSM:

1. Con il formato XLSB, non hai modo di sapere se ha una macro o meno. Ciò lo rende più rischioso in quanto qualcuno può usarlo per eseguire codice dannoso.
2. I file XLSB sono codificati in un formato di file bin proprietario mentre XML è un formato di file leggibile open source. Ciò significa che se utilizzi strumenti di terze parti con Excel, è meglio utilizzare il formato XML.
3. Power Query non può leggere i dati da un file XLSB. Quindi, se usi Power Query, è meglio avere i tuoi dati nei formati XLSX/XLSM.

Come regola generale, se la dimensione del file è piccola (meno di 5 MB), è meglio attenersi ai formati XLSX/XLSM.

Sulla base di ciò che ho sentito dalle persone e letto su molti forum, molte persone preferiscono utilizzare XLSB come formato di file quando si tratta di utilizzare Excel.

Ecco un buon articolo sulla comprensione del formato XLSB.

Rimuovi le formule non necessarie

Troppe formule aumenteranno le dimensioni del file della cartella di lavoro.

Quindi, se hai un set di dati di cui hai bisogno, ma non hai bisogno delle formule al suo interno, è meglio convertire queste formule in valori.

Ecco i passaggi per convertire rapidamente le formule in valori in Excel:

1. Seleziona l'intero foglio di lavoro/set di dati.
2. premi il F5 chiave.
3. Nella finestra di dialogo Vai a, fare clic su "Speciale".
4. Nella finestra di dialogo Vai a speciale, selezionare Formule.
5. Fare clic su OK.

I passaggi precedenti selezionerebbero tutte le celle che contengono una formula.

Ora usa i passaggi seguenti per convertire queste formule in valori:

1. Copia le celle selezionate (control + C)
2. Fare clic sulla scheda Home.
3. Nel gruppo Appunti, fai clic su Incolla.
4. Fare clic sull'icona Incolla valore.

I passaggi precedenti convertirebbero tutte le celle con formule in valori.

Risultati del test

Ho creato un file Excel con 2,7 milioni di punti dati (1048576*26) e poi ho aggiunto tre colonne con formule (SOMMA, CONTEGGIO e MEDIA).

Quindi ho convertito queste formule in valori e ho salvato il file con un nome diverso.

Di seguito il risultato:

Il file senza formule ha una dimensione inferiore di circa l'8% (e nota che avevo solo 3 colonne di formule).

Nel caso in cui tu abbia molte formule, può aggiungere alla dimensione.

NOTA: Le formule volatili possono davvero gonfiare le dimensioni del file. Come parte del test, quando ho sostituito la formula SUM con una formula volatile (OFFSET), ha portato a un salto nella dimensione del file (da 186 MB a 198 MB). Quindi, se hai formule volatili che puoi sostituire con formule non volatili, o puoi convertirle in valori, ti aiuterà a ridurre le dimensioni del file.

Comprimi immagini (prima e dopo il caricamento)

Se lavori con file Excel che contengono immagini, devi ottimizzare queste immagini e assicurarti che le dimensioni siano inferiori.

Il problema con le immagini è che quando le comprimi, c'è anche una riduzione della qualità, cosa che potresti non volere.

Quando si lavora con le immagini, il modo migliore per mantenere basse le dimensioni del file Excel è comprimere le immagini prima di inserirle in Excel.

Un buon strumento online per farlo è TinyPNG (oppure puoi usare Photoshop o qualsiasi altro strumento simile).

È uno strumento gratuito che può ridurre le dimensioni di un'immagine fino al 90% e mantiene comunque una qualità dell'immagine decente.

Di seguito è riportato lo screenshot in cui sono stato in grado di comprimere un'immagine con dimensioni da 3,9 MB a 331 KB con una perdita di qualità minima.

Dopo aver caricato queste immagini compresse in Excel, puoi comprimerle ulteriormente utilizzando l'opzione integrata in Excel.

Ecco i passaggi per comprimere un'immagine in Excel:

1. Seleziona l'immagine che vuoi comprimere.
2. Fare clic sulla scheda Formato Strumenti immagine. Questa è una scheda contestuale che appare solo quando fai clic su un'immagine.
3. Nel gruppo Regola, fai clic sull'opzione "Comprimi immagine".
4. Nella finestra di dialogo Comprimi immagini:
   • Deseleziona "Applica solo a questa immagine" se desideri comprimere tutte le immagini nella cartella di lavoro.
   • Selezionare E-mail (96 ppi) per la massima compressione.
5. Fare clic su OK.
6. Salva il file.

Questo comprimerà tutte le immagini e ti aiuterà a ridurre le dimensioni del file della tua cartella di lavoro di Excel.

Risultati del test:

Ho usato l'opzione Comprimi immagine in Excel usando un'immagine.

Di seguito i risultati:

Utilizzando l'opzione Comprimi immagine, la dimensione del file è stata ridotta da 445 KB a 43 KB (~90% di riduzione delle dimensioni del file).

Questo dipenderebbe anche dall'immagine. Un'immagine già compressa potrebbe non portare a un'enorme riduzione delle dimensioni.

Rimuovi Pivot Cache per risparmiare spazio

Se lavori con tabelle Pivot e lotti con file Excel di grandi dimensioni, può essere utile conoscere la cache Pivot.

Quando crei una tabella pivot utilizzando un set di dati, Excel crea automaticamente la Pivot Cache.

La cache pivot è un oggetto che contiene una replica dell'origine dati.

Anche se non puoi vederlo, fa parte della cartella di lavoro ed è collegato alla tabella pivot. Quando apporti modifiche alla tabella pivot, non utilizza l'origine dati, ma utilizza la cache pivot.

Mentre pensi di essere direttamente collegato ai dati di origine, in realtà accedi alla cache pivot (e non ai dati di origine) quando apporti modifiche alla tabella pivot.

Ora, poiché stai creando una replica del set di dati, richiede un po' di spazio.

Quindi, non appena crei una tabella Pivot, noterai che le dimensioni del file Excel aumentano.

Nell'esempio sopra, non appena aggiungo la tabella Pivot, la dimensione del file raddoppia.

Ora, ci sono due modi per ridurre la dimensione del file quando si lavora con le tabelle pivot.

1. Conserva i dati di origine ed elimina la cache Pivot
2. Mantieni la Pivot Cache ed elimina i dati di origine

Diamo un'occhiata a entrambe queste tecniche.

Mantieni i dati di origine ed elimina la cache pivot

Quando hai finito con la tabella pivot e la stai salvando, puoi forzare Excel a salvare solo i dati di origine e la tabella pivot risultante e non la cache pivot.

In questo modo il tuo file Excel sarà più leggero e avrai un modo per ricreare la cache Pivot quando ne avrai bisogno la prossima volta.

Di seguito è riportata la differenza di dimensioni quando si salva un file Excel con e senza Pivot Cache.

Ecco i passaggi per NON salva la cache pivot:

1. Seleziona una cella nella tabella pivot.
2. Fare clic sulla scheda Analizza.
3. Fare clic sull'icona "Opzioni" della tabella pivot.
4. Nella finestra di dialogo Opzioni tabella pivot, fare clic sulla scheda Dati.
5. Nelle opzioni Dati tabella pivot, deselezionare 'Salva i dati di origine con file'. Questa opzione garantisce che la Pivot Cache non venga salvata quando si salva la cartella di lavoro (e aiuta a ridurre le dimensioni del file Excel).
6. Seleziona l'opzione - "Aggiorna i dati all'apertura del file". Questa opzione garantisce che non appena si apre la cartella di lavoro, la tabella pivot venga aggiornata e la Pivot Cache venga generata automaticamente. Se non selezioni questa opzione, dovrai aggiornare manualmente la tabella Pivot per generare la Pivot Cache.
7. Fare clic su OK.

Ora quando salvi il file con la tabella Pivot, noterai una dimensione ridotta del file.

Mantieni la cache pivot ed elimina i dati di origine

Un altro modo per ridurre le dimensioni del file Excel consiste nell'eliminare i dati di origine di una tabella pivot e mantenere solo la cache pivot.

Funziona come una Pivot Cache è un'immagine speculare dei tuoi dati e non accedi ai dati di origine quando apporti modifiche alla tabella pivot, invece, usi la Pivot Cache.

Di seguito sono riportati i risultati quando ho salvato il file Excel senza i dati di origine.

Per fare ciò, basta eliminare i dati (eliminando i dati o il foglio di lavoro che contiene i dati).

Quando desideri recuperare i dati di origine, fai doppio clic sulla cella "Totale generale" della tabella Pivot e genererà immediatamente l'intero set di dati in un nuovo foglio di lavoro.

Comprimi il file (ZIP IT)

Questo è qualcosa che puoi fare al di fuori dell'applicazione Excel.

Quando comprimi un file Excel, vedrai immediatamente una riduzione delle dimensioni di circa il 10-15%.

È quindi possibile condividere questo file compresso tramite e-mail e la persona che lo riceve può decomprimere il file e utilizzare la cartella di lavoro di Excel.

Ecco i passaggi per comprimere un file Excel (per Windows):

1. Fare clic con il tasto destro sul file Excel
2. Passa il cursore sull'opzione Invia a.
3. Fare clic sull'opzione "Cartella compressa (zippata)".

Questo creerà un file compresso per la cartella di lavoro Excel selezionata.

Noterai anche una riduzione delle dimensioni del file (come mostrato di seguito).

Risultati del test

Con questa tecnica, sono stato in grado di ridurre le dimensioni del file della cartella di lavoro di Excel dell'8%.

Questa tecnica funziona meglio per i file che sono nell'intervallo 20-50 MB. All'aumentare della dimensione del file, la riduzione percentuale non è molto elevata.

Rimuovi la formattazione dei dati

La formattazione dei dati come l'applicazione di un colore di sfondo, l'aggiunta di bordi o la modifica dello stile del carattere possono aumentare le dimensioni del file.

Anche se, in base ai miei test, ho notato che non ha un impatto enorme.

Di seguito sono riportate le dimensioni di due cartelle di lavoro di Excel, una con 2,6 milioni di punti dati (in un foglio di lavoro) e l'altra con gli stessi dati ma la formattazione applicata (colore della cella, bordo e corsivo).

Risultati del test

Puoi vedere che la differenza tra i due file non è così grande (anche se ha un impatto quando hai molti dati).

Il file con i dati è 14,6 MB e il file con i dati e la formattazione è 14,9 MB.

Quindi, se vuoi ridurre le dimensioni del file della tua cartella di lavoro di Excel, puoi cercare la formattazione che non ti serve. Rimuoverlo può farti risparmiare spazio su disco.

Ecco i passaggi per rimuovere istantaneamente tutta la formattazione, mantenendo intatti i dati:

1. Seleziona l'intero set di dati (da cui desideri rimuovere la formattazione).
2. Fare clic sulla scheda Home.
3. Nel gruppo Modifica, fai clic su "Cancella".
4. Fare clic su "Cancella formati".
5. Salva la cartella di lavoro.

Rimuovi formattazione condizionale

Proprio come la normale formattazione dei dati, anche la formattazione condizionale aumenta la dimensione del file.

Tuttavia, in base ai miei test, ho notato che la differenza è inferiore (a meno che tu non abbia più regole applicate a più fogli con enormi set di dati).

Tuttavia, è una buona idea rimuovere la formattazione condizionale da qualsiasi set di dati in cui non è necessaria.

Oltre alla dimensione, c'è un altro buon motivo per rimuovere la formattazione condizionale: SPEED.

La formattazione condizionale è volatile e ogni volta che si verifica una modifica nel foglio di lavoro, viene ricalcolata. Questo può portare a una cartella di lavoro più lenta.

Risultati del test

Ho creato un file con 27,2 milioni di punti dati e gli ho applicato 5 regole di formattazione condizionale.

La differenza di dimensioni sembra essere solo di pochi KB.

Leggi anche: Suggerimenti per rendere veloci fogli di calcolo Excel lenti

Suggerimenti per il potenziamento delle prestazioni di Excel

Sebbene i metodi precedenti ti aiutino a ridurre le dimensioni del file Excel, ecco alcuni suggerimenti aggiuntivi per migliorare le prestazioni delle cartelle di lavoro di Excel:

1. Evita di usare formule volatili. Oltre ad aumentare la dimensione del file, le formule volatili rallentano anche le cartelle di lavoro. Esempi di funzioni volatili includono RAND, TODAY, OFFSET, INDIRETTO, ecc.
2. Se hai la necessità di creare riepiloghi di un set di dati di grandi dimensioni, invece di utilizzare le formule, verifica se puoi utilizzare invece una tabella pivot. Una tabella Pivot è veloce e molto intuitiva rispetto alle formule.
3. Se estrai dati da più origini o combini dati da più file, usa Power Query per ottenere e trasformare i dati. È possibile salvare i dati come connessione Power Query invece di mantenerli nel foglio di lavoro. Puoi creare facilmente una tabella pivot usando le connessioni create usando Power Query.
4. Converti i tuoi dati tabulari in una tabella Excel e usa la tabella Excel nelle formule. Rende la tua cartella di lavoro più veloce e le formule create utilizzando i riferimenti strutturati della tabella di Excel sono più facili da gestire (poiché sono più intuitive).
5. Evita di fare riferimento a intere righe o colonne in una formula. Questo aiuterà le tue formule a essere più efficienti.
6. Se hai molte formule nella cartella di lavoro, disattiva Calcolo automatico e passa al calcolo manuale. In questo modo, ogni volta che c'è un cambiamento nei fogli di lavoro, le formule non verranno ricalcolate. Puoi apportare questa modifica andando alla scheda Formula e modificando le "Opzioni di calcolo".

Spero che il metodo e i suggerimenti di questo tutorial ti siano utili.

Cos'altro potrei aggiungere a questo tutorial per renderlo più utile? Per favore fatemelo sapere nella sezione commenti.