Excel #EPARS! erreur : comment la résoudre

Depuis qu’Excel prend en charge les matrices dynamiques (Microsoft 365 et Excel 2021 ou version ultérieure), une seule formule peut renvoyer plusieurs résultats qui se propagent automatiquement dans les cellules adjacentes. Cette propagation s’appelle le débordement. Si celle-ci échoue, l’erreur #EPARS! s’affiche et le reste de votre calcul reste vide. Ce guide vous explique exactement pourquoi cela se produit et comment résoudre le problème en fonction de la situation.

L’erreur #EPARS! signifie qu’Excel n’a nulle part où placer le résultat

L’erreur #EPARS! (en anglais #SPILL!, dans certaines versions françaises plus anciennes #DEBORDEMENT) apparaît lorsqu’une formule produit plusieurs valeurs, mais que la zone où ces valeurs doivent arriver n’est pas entièrement vide. Excel appelle cette zone la plage de débordement.

Par exemple, si vous placez en D2 la formule =A2:A100, Excel souhaite placer 99 valeurs de D2 à D100. Si D50 contient déjà quelque chose, la formule ne peut pas déborder complètement et D2 affiche #EPARS! au lieu de la série de nombres.

Si vous cliquez sur la cellule d’erreur, Excel dessine une bordure bleue en pointillés autour de l’endroit où les valeurs auraient dû apparaître. Vous voyez ainsi immédiatement la taille de la plage de débordement et l’emplacement du blocage. Le message d’erreur n’est donc pas un bug dans votre formule, mais un conflit avec un élément déjà présent.

Il est important de savoir que seule la première cellule de la plage contient la vraie formule. Les autres cellules sont un écho de cette unique formule et se reconnaissent à l’affichage en gris clair dans la barre de formule. Si vous supprimez la cellule du haut, toute la plage disparaît. Ce comportement explique pourquoi une formule qui déborde ne peut être modifiée que dans son coin supérieur gauche.

L’erreur se produit dès qu’Excel calcule la formule, donc immédiatement après la saisie ou après une modification des données sources. Si votre liste s’allonge et que la matrice entre en collision avec une cellule auparavant libre, une formule qui fonctionnait se transforme subitement en #EPARS!. Cela rend l’erreur difficile à reproduire : le fichier fonctionnait hier, mais aujourd’hui quelqu’un a ajouté une note sous la formule.

Quelques fonctions sont à l’origine de la grande majorité des erreurs de débordement

L’erreur #EPARS! est liée aux formules qui renvoient par définition plusieurs valeurs. Reconnaître ces fonctions permet de savoir immédiatement à quoi faire attention.

• FILTRE renvoie toutes les lignes qui satisfont une condition. Le nombre de lignes varie avec les données, de sorte que la plage de débordement s’agrandit et se réduit.
• UNIQUE extrait les valeurs uniques d’une liste. Le résultat s’allonge quand de nouvelles catégories apparaissent.
• TRIER et TRIER.PAR renvoient une liste entière triée.
• SEQUENCE génère une série de nombres, par exemple =SEQUENCE(10) pour les entiers de 1 à 10.
• RECHERCHEX peut renvoyer plusieurs colonnes à la fois et déborde alors vers la droite.

Comme le résultat de ces fonctions n’est pas connu à l’avance, il est plus difficile de garder suffisamment d’espace libre. C’est pourquoi ces fonctions rencontrent le plus souvent une cellule occupée. Si vous utilisez régulièrement des fonctions de recherche renvoyant une matrice, l’article RECHERCHEV vs RECHERCHEX vous explique quand RECHERCHEX produit une plage entière.

Supprimer la cellule bloquante résout la grande majorité des cas

La cause la plus fréquente est simple : quelque chose se trouve dans la plage de débordement. Excel vous aide à trouver cette cellule immédiatement.

1. Sélectionnez la cellule affichant #EPARS!.
2. Cliquez sur le petit triangle d’avertissement jaune qui apparaît à gauche de la cellule.
3. Choisissez dans le menu Sélectionner les cellules gênantes. Excel accède alors directement à la ou aux cellules qui empêchent le débordement.
4. Supprimez ou déplacez le contenu de ces cellules. Dès que la plage est vide, la formule se remplit d’elle-même.

Attention au contenu invisible. Un espace, un saut de ligne manuel ou une cellule au texte blanc est également considéré comme occupé. En cas de doute, sélectionnez toute la plage de débordement et appuyez sur Suppr avant de ressaisir la formule.

Il arrive que le blocage ne vienne pas d’un texte ordinaire mais d’une mise en forme, comme une cellule qui contenait auparavant une formule et qui renvoie une chaîne vide (""). Une telle cellule semble vide mais n’est pas considérée comme telle. Vérifiez avec =ESTVIDE(D50) : si la fonction renvoie FAUX, cela signifie qu’un élément doit d’abord être nettoyé.

Chaque erreur #EPARS! relève d’une des six causes

Excel indique la raison précise dans le texte qui suit le triangle d’erreur. Ce tableau fait correspondre chaque raison à sa solution.

Faire référence à une colonne entière fait déborder la plage hors de la feuille

Une erreur fréquente consiste à appliquer une fonction dynamique à une colonne entière. Prenez =UNIQUE(A:A) en C1. Excel souhaite placer les valeurs uniques sous C1, mais comme la source comporte plus d’un million de lignes, le résultat dépasserait la dernière ligne de la feuille. C’est impossible, et #EPARS! s’affiche avec le message que la plage dépasse la feuille.

La solution consiste à utiliser une plage limitée. Si vous savez que vos données vont jusqu’à la ligne 5000, écrivez :

=UNIQUE(A2:A5000)

Si vous souhaitez néanmoins travailler avec la colonne entière pour que les nouvelles lignes soient automatiquement prises en compte, transformez les données sources en tableau Excel (Ctrl+T) et faites référence à la colonne du tableau. Celle-ci ne s’étend que jusqu’à la dernière ligne remplie et non jusqu’à la fin de la feuille, ce qui évite l’erreur.

Les cellules fusionnées et les tableaux Excel bloquent toujours le débordement

Deux causes du tableau reviennent si souvent qu’elles méritent un traitement séparé.

Cellules fusionnées. Une formule qui déborde ne peut pas écrire dans une cellule fusionnée. Sélectionnez la plage de débordement, allez dans l’onglet Accueil et désactivez le bouton Fusionner et centrer. Si vous souhaitez conserver l’apparence visuelle, utilisez Format de cellule (Ctrl+1) avec l’alignement Centrer sur la sélection au lieu de fusionner. Vous gardez ainsi l’aspect centré sans fusionner physiquement les cellules.

Tableaux Excel. À l’intérieur d’un tableau créé avec Ctrl+T, une formule matricielle dynamique ne peut pas déborder. Deux options s’offrent à vous : cliquez dans le tableau, allez dans l’onglet Création de tableau et choisissez Convertir en plage, ou placez la formule matricielle dans une cellule en dehors du tableau. Ne mettez jamais la formule dans une colonne de tableau si vous attendez plusieurs résultats. Un tableau constitue une excellente source pour une formule qui déborde, mais pas un emplacement où le résultat doit arriver.

Avec le signe @, vous forcez une seule valeur, avec # vous faites référence à toute la plage

Parfois, vous ne voulez justement pas de débordement. Le signe @ (l’opérateur d’intersection implicite) limite une formule à une seule valeur. Placez un @ devant la référence et Excel ne prend que la valeur de la même ligne :

=@A2:A100

Cette approche est utile lorsque vous souhaitez reproduire l’ancien comportement sans débordement des versions antérieures d’Excel, par exemple lors de la conversion d’un fichier existant où la formule devait renvoyer un seul résultat par ligne.

À l’inverse, vous pouvez désigner d’un seul coup une plage de débordement réussie avec la référence de débordement, le dièse. Si votre matrice se trouve en D2, D2# fait référence à toute la plage remplie par la formule. Pratique pour les calculs consécutifs qui s’adaptent automatiquement :

=SOMME(D2#)
=NBVAL(D2#)

Si vous ajoutez plus tard des lignes à la source, la somme s’agrandit aussi sans que vous ayez à modifier la plage. Cette référence de débordement fonctionne également dans les graphiques et la validation des données, de sorte qu’une liste déroulante affiche automatiquement les éléments les plus récents.

Restaurer une formule FILTRE illustre la démarche en pratique

Prenons un cas concret. En F2 se trouve =FILTRE(A2:C500; B2:B500="Nord") pour afficher toutes les lignes de la région Nord. La formule renvoie #EPARS! parce que les colonnes G et H, à l’intérieur de la plage de débordement, contiennent encore d’anciennes annotations.

1. Cliquez sur F2 puis sur le petit triangle jaune ; choisissez Sélectionner les cellules gênantes. Excel sélectionne les cellules remplies en G et H.
2. Déplacez ces annotations vers une zone vide plus loin, par exemple la colonne M.
3. Dès que la plage est libre, FILTRE remplit toutes les lignes Nord à partir de F2 vers la droite et vers le bas.

Si vous doutez de la taille du résultat, comptez d’abord le nombre de lignes qui remplissent la condition avec =NB.SI(B2:B500; "Nord"). Une fois ce nombre connu, réservez exactement le nombre de lignes vides nécessaires sous F2 et évitez ainsi que l’erreur ne se reproduise.

Garder de l’espace libre évite #EPARS! avant qu’il n’apparaisse

Le moyen le plus simple d’éviter l’erreur est d’organiser votre feuille de manière à ce que les formules matricielles disposent toujours d’espace.

• Attribuez à chaque formule qui déborde sa propre colonne et laissez les cellules en dessous vides.
• Réservez au moins autant de lignes vides que votre plage source en compte.
• Ne faites pas référence à des colonnes entières (A:A) à côté d’une formule qui déborde ; utilisez une plage limitée ou une colonne de tableau.
• Évitez les fonctions volatiles dans une matrice si la taille doit être fixe.
• N’empilez pas plusieurs formules de débordement les unes sous les autres ; laissez de la place pour la plus longue.
• Veillez à ce que les collègues partageant le fichier ne tapent pas dans la plage de débordement ; une brève instruction ou un marquage coloré sous la formule peut y aider.

Si vous obtenez un message d’erreur qui ressemble à un problème de référence plutôt qu’à un manque de place, consultez la procédure dans Résoudre l’erreur #REF! dans Excel : causes et solutions. Pour plus d’informations sur la plage de débordement et les messages précis, reportez-vous à l’explication officielle de Microsoft.