Pourquoi Excel plante sur les gros fichiers, et que faire à la place

Excel plante très rarement parce qu'un fichier a trop de lignes. Il plante à cause de ce qu'il y a sur ces lignes.

Une feuille de 900 000 lignes de simples valeurs s'ouvre sans problème sur un portable de 2015. Une feuille de 40 000 lignes avec une mise en forme conditionnelle sur des colonnes entières, quelques milliers de RECHERCHEV (VLOOKUP) et un cache de tableau croisé dynamique bloquera la même machine pour de bon. Le nombre de lignes, c'est ce qu'on remarque ; ce n'est presque jamais la cause.

La bonne question est donc laquelle des cinq causes est la vôtre, car elles n'ont pas les mêmes remèdes, et quatre d'entre eux prennent moins d'une minute.

Les cinq causes, de la plus à la moins probable

1. Excel manque de mémoire (et vous êtes peut-être en 32 bits)

Excel charge tout le classeur en RAM, plus la chaîne de calcul, l'historique d'annulation et un cache par tableau croisé dynamique, souvent plusieurs fois la taille du fichier sur le disque.

Vérifiez d'abord votre version : Fichier → Compte → À propos d'Excel. Si la première ligne indique 32 bits, Excel est plafonné à environ 2 Go de mémoire quelle que soit la quantité dont dispose la machine, et c'est votre réponse. Microsoft a livré le 32 bits par défaut pendant des années, et beaucoup d'installations n'ont jamais été migrées. Réinstaller en 64 bits est, pour ceux que ça concerne, le correctif le plus rentable de cette page.

Le signe d'un problème de mémoire : « Excel ne peut pas terminer cette tâche avec les ressources disponibles », ou un plantage précisément à l'enregistrement ou à l'ouverture d'un deuxième classeur.

2. Des formules volatiles recalculent tout, en permanence

Certaines fonctions se recalculent à chaque modification n'importe où dans le classeur, pas seulement quand leurs arguments changent. NOW() (MAINTENANT), TODAY() (AUJOURDHUI), RAND() (ALEA), OFFSET() (DECALER), INDIRECT(), INFO() et CELL() (CELLULE) sont les fonctions volatiles, et chacune entraîne avec elle tout ce qui en dépend.

INDIRECT() est le coupable habituel d'un classeur lent, et OFFSET() celui d'un classeur devenu inutilisable. Quelques centaines de l'une ou l'autre sur une grande feuille suffisent pour que chaque frappe déclenche un recalcul complet.

Diagnostic en dix secondes : Formules → Options de calcul → Manuel. Si le classeur redevient réactif aussitôt, le problème était le recalcul, pas la taille.

Les remèdes, par ordre de préférence : remplacer OFFSET/INDIRECT par INDEX, remplacer VLOOKUP sur une colonne entière par INDEX/MATCH ou XLOOKUP sur une plage bornée, et ne jamais référencer A:A quand A1:A50000 suffit. Une référence de colonne entière, c'est un balayage d'un million de cellules par formule.

3. Une mise en forme appliquée à des colonnes entières

Sélectionnez la colonne A, appliquez une mise en forme conditionnelle ou une couleur de remplissage, et Excel peut stocker l'état de mise en forme d'un million de cellules. Faites-le sur une douzaine de colonnes et le fichier gonfle. Les classeurs de 40 Mo contenant 5 000 lignes de données sont courants, et ils s'ouvrent comme s'ils en contenaient un million.

Deux signes :

• Ctrl+Fin saute à un endroit absurde (ligne 900 000, colonne BZ) alors que vos données s'arrêtent à la ligne 5 000. C'est la plage utilisée, et Excel alloue de la mémoire en fonction d'elle.
• Le fichier est énorme par rapport à son contenu.

Le remède : sélectionnez toutes les lignes sous vos données jusqu'en bas, supprimez les lignes (pas seulement le contenu), faites pareil pour les colonnes à droite, enregistrez, fermez, rouvrez. Ctrl+Fin devrait maintenant atterrir sur la vraie dernière cellule. Réappliquez ensuite la mise en forme conditionnelle à la plage réelle plutôt qu'à des colonnes entières.

4. Un cache par tableau croisé dynamique

Chaque tableau croisé dynamique stocke sa propre copie des données sources, sauf s'il partage explicitement un cache avec un autre. Cinq tableaux croisés sur une source de 200 000 lignes, c'est un million de lignes dupliquées en cachette dans le fichier.

Le remède : créez les tableaux croisés supplémentaires en copiant un tableau existant plutôt qu'en repartant de la source, car les copies partagent le cache. Et dans les Options du tableau croisé dynamique, désactivez « Enregistrer les données sources avec le fichier » quand c'est possible.

5. C'est bien le nombre de lignes, finalement

Si le fichier est un CSV de plusieurs millions de lignes, rien de ce qui précède ne s'applique. Excel contient 1 048 576 lignes par feuille, et c'est structurel. Il ne plantera pas, à proprement parler ; il chargera ce qui rentre et s'arrêtera, ce qui est pire. Comment ouvrir un gros fichier CSV qu'Excel n'ouvre pas est le guide dédié à ce cas, et comment ouvrir un fichier CSV de 1 Go compare ce qui sait le gérer à la place.

Trouver laquelle est la vôtre

Deux minutes, dans cet ordre :

1. Fichier → Compte → À propos d'Excel. 32 bits ? Arrêtez-vous là ; c'est le correctif.
2. Ctrl+Fin. Atterrit bien après vos données ? Cause 3.
3. Formules → Options de calcul → Manuel. Réactif maintenant ? Cause 2.
4. Comparez la taille du fichier à son contenu. Un fichier de 30 Mo avec 8 000 lignes relève de la cause 3 ou 4.
5. Rien de tout ça, et le fichier compte des millions de lignes ? Cause 5. Excel est le mauvais outil ici, pas un outil cassé.

Écartez les compléments séparément. Lancez Excel avec excel /safe. Si le classeur se comporte normalement, c'est un complément qui posait problème, et le reste n'a plus d'importance.

Que faire à la place

Si c'est un fichier de données plutôt qu'un document. Ce qui met les gens en difficulté, c'est d'utiliser un tableur comme base de données : des centaines de milliers de lignes, des colonnes de formules calculées sur toutes, un tableau croisé par-dessus. Cette charge de travail demande un moteur de requêtes :

duckdb -c "SELECT region, SUM(amount) FROM 'export.csv' GROUP BY region"

DuckDB répond en environ une seconde à ce qu'Excel met deux minutes et un blocage à faire, et la syntaxe est plus simple qu'un SUMIFS imbriqué. Une explication plus complète se trouve dans exécuter des requêtes SQL sur un CSV sans base de données.

Si vous devez rester dans Excel. Power Query (Données → Obtenir des données) traite le fichier entier et ne dépose que le résumé dans une feuille : des millions de lignes n'occupent jamais une feuille de calcul. Le modèle de données gère plus de lignes qu'une feuille ne peut en contenir et permet de faire tourner des tableaux croisés directement dessus.

Si vous voulez juste lire le fichier. Ouvrir un fichier de données dans un outil qui le traite comme des données plutôt que comme un document élimine les cinq causes d'un coup :

CSV File Viewer - Smart CSViPhone et iPad · Android

Lit le fichier au lieu de l'importer : pas de recalcul, pas d'état de mise en forme, pas de cache de tableau croisé. SQL ou questions en langage courant sur les valeurs telles qu'elles sont écrites.

Ce que ça fait

Deux habitudes qui évitent l'essentiel

Séparez les valeurs de la présentation. Une feuille de données brutes sans mise en forme ni formules, une feuille d'analyse qui s'y réfère. La plupart des classeurs catastrophiques sont une seule feuille qui fait les trois métiers.

Référencez des plages, pas des colonnes. A1:A50000 au lieu de A:A, partout. C'est l'habitude qui pèse le plus sur la capacité d'un classeur à rester utilisable en grossissant.