Quando i fogli Excel invecchiano, quando inseriamo troppi dati, o impostiamo troppe formattazioni in troppe celle, o troppe tabelle e grafici, i nostri fogli “ingrassano” e si appesantiscono, peggiorando in prestazioni. In questo post vediamo come si fa manutenzione dei fogli excel per diminuirne le dimensioni e migliorarne le prestazioni.
Perché i fogli di lavoro aumentano di dimensioni
Il formato standard di Excel è l’xlsx, a cui si affianca l’xlsm quando abbiamo bisogno di conservare e gestire macro e strumenti per l’automazione. Sono formati compressi che usano gli standard open xml (eXtensible Markup Language), formati aperti pensati per la condivisione delle informazioni, che utilizzano marcatori come l’Html per definire i dati, le strutture, relazioni e impostazioni, eccetera.
Quando salviamo un foglio Excel, il programma trasforma i contenuti delle pagine, i dati, gli oggetti di Excel, immagini o elementi grafici, i temi e le impostazioni, in diversi file xml distribuiti in diverse cartelle, per poi comprimere tutto in un file contenitore che viene nominato come “nome file.xlsx” o xlsm.
Dati e formule sono di solito la parte rilevante delle informazioni conservate in questi formati, ma quando andiamo a modificare le celle delle pagine con formati diversi da quello standard, quando aggiungiamo tabelle pivot, quando usiamo in abbondanza strumenti come la formattazione condizionale, quando inseriamo migliaia di formule con funzioni complesse che richiedono risorse rilevanti per funzionare (es: cerca.vert() ), allora le dimensioni possono crescere notevolmente e viceversa le prestazioni calare significativamente.
Come cambiano le dimensioni del file
Un modo per comprendere direttamente come i contenuti cambiano le dimensioni dei file, è provare a fare questo:
- apri un nuovo file Excel, elimina ogni pagina eccetto una, crea una tabella di 10 righe e 10 colonne con dati fittizi, salva e prendi nota delle dimensioni;
- salva il file con un altro nome e copia un record per tutta la pagina dalla prima all’ultima riga (seleziona la riga, premi Ctrl+C, premi Ctrl+Maius+Giù finché non arrivi all’ultima riga, premi Ctrl+V per incollare), salva il file e prendi nota delle dimensioni;
- riprendi il primo file con la tabella 10×10, salva il file con un altro nome, seleziona le 10 colonne della tabella, imposta una formattazione per il font (es: dimensioni e tipo font), per il fondo (es: colore), per i bordi (es: imposta i bordi); salva il file e prendi nota delle dimensioni;
- riprendi il primo file con la tabella 10×10, salva il file con un altro nome, seleziona le 10 colonne, imposta una formattazione condizionale con una regola a piacere); salva il file e prendi nota delle dimensioni;
- riprendi il primo file con la tabella 10×10, salva il file con un altro nome, trasforma la tabella 10×10 in una tabella excel (Ctrl+T), poi copia la pagina 10 volte; salva il file e prendi nota delle dimensioni;
- riprendi il primo file con la tabella 10×10, salva il file con un altro nome, e applica tutte le modifiche dei punti precedenti: amplia la tabella per l’intera colonna, applica un formato diverso alle colonne della tabella, applica una formattazione condizionale all’intera colonna della tabella, copia la pagina 10 volte; poi salva il file e prendi nota delle dimensioni.
Come hai osservato i contenuti che aggiungi ai fogli vengono tutti conservati nel file, con precisione ed efficacia. Via via che aggiungi tabelle, dati, formule, pagine, grafici, formati, qualsiasi cosa nel foglio, questo viene conservato. Quindi non devi stupirti se il file con cui esegui le tue attività è cresciuto in dimensioni e calato in prestazioni.
Le prestazioni dei nostri file Excel
Cosa intendo con prestazioni? Mi riferisco alla velocità di esecuzione delle nostre attività lavorative con il foglio di lavoro. Se il foglio svolge le sue attività in modo fluido senza presentare rallentamenti, ha buone prestazioni. Viceversa se mostra rallentamenti allora ha bisogno di ripristinare o migliorare le prestazioni precedenti.
Per farlo in primo luogo verifica che non sia opportuno fare manutenzione al pc, o perfino aggiornarlo con una macchina più potente. Questo perché le prestazioni sono determinate in primo luogo dalle caratteristiche del computer, in secondo luogo dal foglio di calcolo, da Excel, e in terzo luogo dal foglio di lavoro.
Presumendo che il pc abbia una dotazione discreta e la versione di office sia gestibile dall’hardware, allora il problema dipende probabilmente dal foglio di lavoro. Puoi verificarlo facilmente: se usando altri fogli Excel non osserviamo un calo di prestazioni, allora il problema è il foglio.
Quando un foglio cresce a dismisura ha bisogno di più risorse da parte del pc per svolgere le proprie attività e quando la richiesta diventa rilevante per le caratteristiche della macchina, è opportuno fare manutenzione dei fogli Excel.
La manutenzione dei fogli Excel
Come riduciamo le dimensioni dei fogli Excel?
In primo luogo andando a rimuovere le cose inutili. Prima di dire mi serve tutto, pensaci con attenzione, guarda il tuo strumento da fuori, da lontano. Può essere che nel tempo tu abbia aggiunto solo cose importanti, ma nella maggior parte dei casi i nostri fogli di lavoro di lunga durata si popolano di dati, strumenti e oggetti senza più importanza che possiamo eliminare, oppure conservare in una copia del foglio.
In secondo luogo possiamo ottimizzare il nostro foglio, sostituendo strumenti con altri più efficienti o modificandone la struttura, per esempio sostituendo le migliaia di formule di campi calcolati con altri strumenti (pivot, query, ecc).
Infine possiamo usare soluzioni diverse che cambiano le caratteristiche dei file Excel, come passare a un formato binario.
In realtà l’ideale sarebbe di non dover mai intervenire quando la situazione del foglio è complicata o “eccessiva”, perché di fatto hai perso e stai perdendo tempo utilizzando uno strumento lento. E per chissà quanto lo hai fatto. È più saggio evitare questi sprechi facendo manutenzioni periodiche, per esempio una volta l’anno, con cui possiamo garantire il mantenimento delle prestazioni e della qualità dello strumento e quindi del lavoro svolto su di esso.
Non vuoi usare strumenti lenti e inefficienti, vero?
Come fare manutenzione dei fogli Excel?
La manutenzione di un foglio Excel esistente e strutturato consiste di solito in:
- Eliminare i dati inutili
- Eliminare gli oggetti inutili
- Rimuovere le formattazioni inutili
- Ridurre le dimensioni delle immagini
- Rimuovere le formule inutili
- Ottimizzare le formule
- Sostituire le formule con altri strumenti
- Rimuovere le formattazioni condizionali
- Ridurre le dimensioni delle tabelle pivot
- Converti il file in formato binario
Per svolgere la manutenzione dei fogli excel devi:
- realizzare una copia di backup del foglio, in modo da poter tornare sempre indietro in caso di errori di manutenzione;
- prendere nota delle dimensioni del file e dei problemi di prestazioni per le attività principali svolte nel foglio;
- applicare in modo sistematico i singoli interventi esaminando pagina per pagina il nostro foglio di lavoro;
- salvare il foglio e si esamina la variazione di dimensioni, poi si usa il foglio e si valuta il miglioramento delle prestazioni; è consigliato farlo senza avere altri programmi rilevanti in background che potrebbero influenzare i risultati;
- se gli interventi sul foglio, i dati e gli strumenti non sono sufficienti, si può modificare il formato passando a quello binario, ma è consigliabile farlo solo se strettamente necessario.
Vediamo adesso i singoli interventi di manutenzione dei fogli Excel:
Eliminare i dati inutili
Il fattore che di solito pesa di più per le dimensioni del file sono i dati che accumuliamo nei nostri fogli. Mese dopo mese aggiungiamo record alle nostre tabelle senza rimuoverli, arrivando a conservare i dati di interi anni, decine di migliaia di record e più, milioni di numeri e stringhe che vengono conservati, ma ingrassano i nostri fogli Excel.
Raramente abbiamo bisogno di anni di dati, non quando possiamo rappresentarli con dati sintetici, come le medie del periodo. Un modo molto efficace per ridimensionare i fogli è rimuovere i dati “vecchi” dai nostri fogli. Naturalmente non ti sto invitando a cancellarli e basta, ma ti invito a esportarli in un foglio dedicato alla conservazione dei dati storici. Potresti aver bisogno di eseguire in seguito analisi o verifiche.
Come farlo?
Il modo più veloce è fare una copia del foglio in cui lasciare i dati che non servono, per poi rimuovere nel file in uso i dati in eccesso conservando solo le medie storiche in una pagina apposita. Se hai tempo a disposizione puoi preparare un file dedicato alla conservazione dei dati storici, rimuovendo pagine e strumenti inutili.
Eliminare oggetti inutili
Spesso nei fogli accumuliamo oggetti che non usiamo:
- pagine vuote,
- copie di pagine “temporanee” diventate permanenti, perché perse nella struttura del foglio,
- pagine nascoste con dati o strumenti che non usiamo,
- celle nascoste, righe e colonne con dati che non ci servono e nascondiamo per toglierle alla vista,
- copie di tabelle che ci sono servite per approfondimenti, ma che non usiamo o non guardiamo più,
- grafici o copie di grafici che abbiamo realizzato e poi abbandonato,
- pagine con tabelle di formule con cui abbiamo individuato cause di anomalie, lacune nei dati, con cui abbiamo valutato la qualità di dati,
- forme e controlli, pulsanti che abbiamo posizionato nelle pagine per automatizzare il foglio o per abbellirlo, ma che non usiamo
- e molto altro.
Come fare a eliminare tutto ciò?
In primo luogo presta attenzione alle attività che svolgi sul foglio, ricorda gli obiettivi di queste attività e prendi nota di quali strumenti e pagine del foglio sono necessari per svolgerle e quali usi in pratica. Poi prendi il foglio e lo ripulisci.
Prima naturalmente fai una copia di backup, fai sempre una copia prima di modificare in modo rilevante struttura o contenuti dei tuoi fogli. Non dimenticarlo.
Poi con calma esamini una pagina alla volta, uno strumento alla volta e ti chiedi: a cosa serve, qual è la sua utilità, quando, perché e come lo uso?
Quindi decidi se tenerlo, eventualmente revisionarlo, oppure eliminarlo.
Una volta che hai ripulito tutto, usa il foglio come al solito e osserva con attenzione se sorgono imprevisti e anomalie. Può capitare di rimuovere oggetti utili, soprattutto in fogli datati che non ricordi neppure come li hai realizzati. In tal caso individua la causa e ripristina i dati o gli oggetti che hai eliminato, ma che sono importanti.
Rimuovere le formattazioni inutili alle celle
Quando creiamo e revisioniamo il nostro foglio di lavoro impostiamo nelle pagine formattazioni in porzioni più o meno estese a seconda delle nostre abitudini e della nostra conoscenza di Excel. Spesso succede che impostiamo formattazioni per intere righe (decine di migliaia di celle), o colonne (milioni di celle), o per estensioni notevoli di celle senza che ce ne sia un effettivo bisogno.
Ogni formattazione di ogni cella viene memorizzata nel file excel. Normalmente il peso delle singole formattazioni è minimo, ma quando lo fai per milioni di celle le formattazioni possono contribuire in modo significativo alle dimensioni finali del foglio. L’eliminazione delle formattazioni inutili delle celle ridurrà le dimensioni del file e migliorerà le prestazioni del foglio di calcolo.
Come si rimuovono le formattazioni dalle celle?
- seleziona l’intervallo di celle da cui vuoi cancellare la formattazione,
- clicca sul comando “Cancella” della scheda “Home” della barra multifunzione per aprire il menu a tendina, poi clicca sul comando “Cancella formati”.
Un modo più veloce per rimuovere formattazioni e contenuti è rimuovere l’intervallo di celle premendo Ctrl+–, ma questo modo modifica la struttura della pagina. Quindi usalo quando sei sicuro di quello che stai facendo.
Ricorda anche che non serve a nulla rimuovere le celle al di fuori dell’area di lavoro. L’area di lavoro è intervallo di celle della pagina che hai modificato nel tempo, per individuarlo puoi premere Ctrl+Fine per raggiungere l’angolo inferiore destro dell’area. Rimuovi le formattazioni all’interno dell’area perché fuori non ne puoi trovare.
E le formattazioni degli oggetti come tabelle Excel, tabelle pivot, grafici, eccetera? Sono una caratteristica dell’oggetto gestita attraverso gli strumenti dedicati che pesano poco rispetto al “peso” dell’oggetto e dei dati contenuti.
Ridurre le dimensioni delle immagini
Personalmente non importo mai immagini in un foglio Excel, ma conosco chi lo fa per migliorare l’impatto di report e grafici. Le immagini che inseriamo nei file Excel pesano anche in modo notevole, quindi dovremmo sempre preparare le immagini prima di importarle. Le ultime versioni di Excel sono state equipaggiate con notevoli strumenti che permettono tra l’altro di gestire e modificare le immagini, anche ridurne le dimensioni senza perdite significative di qualità.
Come si riducono le immagini dentro Excel?
- seleziona l’immagine o le immagini,
- vai alla scheda “Formato”, “Strumenti di immagini” e clicca sul comando “comprimi immagini” per ridurne le dimensioni; si aprirà la finestra omonima che permette di impostare le opzioni della compressione, fra cui trovi la possibilità di comprimere tutte le immagini e di scegliere la risoluzione che preferisci,
- premi ok per confermare.
Rimuovere le formule inutili
Inserire molte formule nei nostri fogli excel può influire sulla dimensione del file e anche sulle prestazioni, soprattutto quando sono formule che contengono funzioni di ricerca, come Cerca.vert(), che richiedono notevoli risorse.
Cosa intendo con molte formule?
Intendo per esempio interi campi di formule nelle tue tabelle dei dati con migliaia di record. Sei sicuro di aver bisogno di tutti quei campi calcolati nella tua base dati? Non puoi modificare la query con cui estrai i dati dal server, quindi lavorare a monte? Perché non usare Recupera e trasforma per ristrutturare la base dati? Non puoi cambiare il modo per individuare il record o calcolare il valore che stai cercando?
Per ridurre le dimensioni del tuo foglio puoi:
- rimuovere le formule che non ti servono, rimuovendo i campi o le celle che le contengono, oppure convertendo le formule non utilizzate in valori statici;
- compattare più formule in una sola, non ha molto senso separare i calcoli nei diversi passaggi, moltiplicando in questo modo celle e campi con le formule;
- ottimizzare le formule, usando funzioni che richiedono meno risorse;
- sostituire le formule con altri strumenti che raggiungono lo stesso risultato con meno risorse.
Ottimizza le formule che utilizzi
Se non puoi eliminare le formule puoi cercare di ottimizzarle, o di usare funzioni che “pesano” meno. In primo luogo riducendo al minimo le funzioni volatili (es: Ora(), Oggi(), Indiretto(), Scarto() ) che vengono ricalcolate ogni volta che si modifica il foglio e possono appesantirlo sia come dimensioni che prestazioni.
Un altro modo per ottimizzare le nostre formule è sostituire le funzioni con altre che richiedono meno risorse, per esempio le funzioni di ricerca più comuni (es: Cerca.Vert(), Cerca.Orizz(), Ricerca() ) con la coppia Indice() e Confronta(), o la funzione Val.Errore() di solito usata con Se(), con la funzione Se.Errore(). Oppure non usare funzioni Se() annidate, o dove possibile riducile o sostituiscile con le funzioni condizionali (es: Somma.Se, Conta.Se, Somma.più.se), con Indice() e Confronta(), o usa le funzioni O() ed E() per accorpare le condizioni.
In modo analogo evita di usare funzioni molto pesanti come le funzioni matriciali o le funzioni database nei campi calcolati di basi dati o tabelle intermedie. Sono funzioni che richiedono notevoli risorse per lavorare su intere matrici di dati contemporaneamente e non è una buona idea replicarle per migliaia di record. Spesso se ne può evitare l’impiego, semplicemente modificando la base dati, o lavorando a monte, sulla query di estrazione o con strumenti diversi.
Sostituisci le formule con altri strumenti
Formule e funzioni sono flessibili e potenti, permettono di fare cose sorprendenti, ma non sono gli unici strumenti di Excel con cui possiamo elaborare i dati, o ricercare valori specifici, o verificare i dati, o ristrutturare la base dati, eccetera.
Per esempio se hai bisogno di integrare due tabelle mantenendole aggiornabili, invece di usare migliaia di Cerca.vert(), puoi modificare la query che estrae i dati dal server aziendale, oppure puoi usare lo strumento Recupera e trasforma di Excel (ex power query) per unire le due tabelle nel tuo foglio.
Oppure puoi usare formule diverse per andare a elaborare direttamente i dati della base dati senza dover aggiungere campi calcolati, come le nuove funzioni a matrice dinamica, le formule matriciali, le funzioni database, o funzioni flessibili come Indice e Confronta.
Oppure se hai grandi tabelle di formule, puoi usare le tabelle pivot per replicarle, tutte o in parte, dove i calcoli sono relativamente semplici. Per esempio puoi usare le pivot per creare una base dati intermedia che filtra, raggruppa e ordina solo quella parte dei dati che ti interessa e su cui puoi lavorare con le formule. Ci sono molti modi per usare le pivot e per ridurre le formule nei fogli Excel.
Rimuovere le formattazioni condizionali inutili
La formattazione condizionale è un notevole strumento di Excel che certamente conosci e usi per evidenziare in modo efficace e professionale valori e campi delle tue tabelle. Questo strumento richiede risorse per mantenere aggiornate le formattazioni dinamiche, finché le utilizzi in qualche centinaio di celle non vedi differenze significative, ma se imposti la formattazione in decine di migliaia o milioni di celle, appesantirai notevolmente le prestazioni del foglio e ne aumenterai le dimensioni.
La formattazione serve a visualizzare in modo efficace un dato o a evidenziare il rapporto tra dati diversi, quindi informazioni che devono essere viste da utenti, quindi è opportuno usarle solo dove serve. Non applicare o propagare la formattazione condizionale a intere colonne di celle, invece applicala ai dati di tabelle excel o pivot che la propagano in modo dinamico.
Per rimuovere la formattazione condizionale:
- seleziona l’intervallo di celle da cui vuoi rimuovere la formattazione condizionale,
- clicca sul comando “Formattazione Condizionale” della scheda “Home” della barra multifunzione per aprire il menu a tendina,
- clicca sul comando “Cancella regole” per aprire il menu relativo e poi sul comando “Cancella regole dalle celle selezionate”.
Ridurre le dimensioni delle tabelle pivot
Le tabelle pivot sono strumenti di elaborazione dei dati che eseguono calcoli relativamente semplici su grandi quantità di dati e per lavorare richiedono risorse (memoria) e possono aumentare le dimensioni del file.
Quando aggiungiamo tabelle pivot aumentiamo le dimensioni del foglio sia per la struttura dell’oggetto che per i dati elaborati e mostrati (cache). È importante tenere sotto controllo il numero di tabelle pivot del foglio e rimuovere quelle che non ci servono più. Per farlo è sufficiente selezionare l’intervallo di celle della pivot e rimuoverle, per esempio premendo Ctrl+–.
Un altro modo per contenere il peso delle tabelle pivot è duplicarle, dove possibile, in quanto condivideranno la stessa cache, cioè le stesse risorse di memoria dedicate per l’elaborazione della base dati specificata. Per copiarle seleziona l’intervallo di celle che le contiene e usa Ctrl+C e Ctrl+V per copia–incollarle.
Infine è possibile evitare di salvare i dati di origine della tabella pivot con il file. Per farlo, si procede in questo modo:
- clicca con il pulsante destro del mouse sulla tabella pivot,
- clicca sul comando “Opzioni tabella pivot” per aprire la finestra omonima,
- vai alla scheda “Dati” e deseleziona la voce “Salva dati sorgente con file”
- clicca su “Ok”.
Converti i file Excel in formato binario
Excel permette di convertire i file excel in un formato compatto non xml detto binario (xlsb) che riduce drasticamente la dimensione del file, è più veloce da salvare e aprire, ma è meno compatibile e ha una minore possibilità di configurazione. Quindi quando la situazione richiede un intervento drastico e le altre soluzioni proposte non riducono a sufficienza il file puoi trasformarlo in formato binario, arrivando anche a risparmiare il 50–70% delle dimensioni per file di decine di Mb o più.
Come? In questo modo:
- fai una copia di backup del file e conservarla con cura,
- premi F12 per aprire la finestra Salva con nome,
- seleziona il tipo di file binario di Excel (.xlsb)
- clicca su “Salva”.
I file binari sono compatti e agili, ma possono dare problemi di compatibilità con strumenti di terze parti, con strumenti di excel di vecchie versioni o con servizi online, e limitano la configurabilità di Excel.
Conclusioni sulla manutenzione dei fogli Excel
Abbiamo visto che i fogli excel di lunga durata, nonostante le caratteristiche dei formati xlsx e xlsm, possono crescere in dimensioni fino a provocare problemi di prestazioni, di condivisione, di uso del foglio o di servizi online correlati.
Se i fogli diventano troppo grandi, possiamo realizzare una serie di interventi di manutenzione straordinaria che, come abbiamo visto, puntano a ridurre le dimensioni del foglio, riducendo dati, formule e strumenti a quelli che effettivamente servono alle nostre attività, o puntano a modificare le impostazioni o le caratteristiche del file stesso.
Il mio consiglio non è quello di intervenire quando il problema è sorto, ma per i tuoi fogli “permanenti” o di lunga durata, consiglio di svolgere periodici interventi di manutenzione dei fogli excel quando le dimensioni superano i 3 Mb, per esempio in concomitanza di revisioni o di attività rilevanti.
Anzi ti consiglio ogni qualvolta devi revisionare, modificare o semplicemente aggiornare un foglio, di farlo tenendo in considerazione anche l’aspetto dimensioni e prestazioni.
E infine ti ricordo di fare sempre una copia di backup del foglio prima di eseguire interventi rilevanti sui tuoi strumenti. Tra le macro che ho condiviso puoi trovare una macro che lo fa per te con un semplice clic.
PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie.
