Résolvez une erreur de déversement dans Excel comme un pro en utilisant ces conseils

Les dernières versions de Microsoft Excel proposent des tableaux dynamiques. Grâce à ces tableaux, vous pouvez saisir une formule dans Excel, par exemple pour calculer les totaux ou les moyennes d’un groupe de nombres, une seule fois. Ensuite, plusieurs sorties de valeurs « déborderont » dans les cellules voisines. Cette méthode est différente de l’utilisation de la poignée de remplissage sur votre cellule de formule, dans la mesure où Excel remplit automatiquement les cellules voisines avec les informations calculées. Il arrive cependant que cela ne fonctionne pas comme prévu, ce qui vous laisse avec une erreur de débordement frustrante dans Excel. Suivez ce tutoriel pour savoir comment remettre votre formule sur les rails et remplir les cellules de votre feuille de calcul avec les données dont vous avez besoin.

Les choses les plus courantes qui brisent une formule dynamique et entraînent une erreur #SPILL!

Lorsque vous rencontrez une erreur #SPILL! dans Microsoft Excel, c’est généralement parce que la formule n’est pas en mesure de remplir plusieurs cellules avec les résultats calculés par la feuille de calcul. Le problème est presque toujours que quelque chose empêche Excel de remplir les cellules où vous voulez vos données, appelé gamme de jeu.

Il y a plusieurs raisons à cela, et Excel fait de son mieux pour vous indiquer ce qui empêche le remplissage des cellules.

Cliquer sur l’icône d’avertissement en forme de triangle jaune affichera généralement l’une de ces erreurs de déversement Excel :

• La plage de déversement n’est pas vide
• La plage de déversement contient des cellules fusionnées
• Plage de déversement dans le tableau
• La portée du déversement est inconnue
• La portée du déversement est trop grande

Le problème le plus simple : la plage de déversement n’est pas vide

Le problème le plus simple à résoudre est probablement l’erreur « La plage de débordement n’est pas vide ». Excel n’écrase pas les données dans vos cellules, alors vérifiez si l’une des cellules de votre plage de débordement contient déjà une valeur.

Pour ce faire, commencez par cliquer sur la cellule de formule. Excel affichera la plage de débordement avec une bordure en pointillés autour. Dans le cas le plus simple, vous verrez immédiatement quelle cellule n’est pas vide et vous pourrez l’effacer.

Cela ne vous aidera peut-être pas si la feuille de calcul est vraiment grande ou s’il y a des caractères invisibles (comme un espace), ce qui rend l’une des cellules non vide. Heureusement, Excel peut vous aider. Il vous suffit de cliquer sur Sélectionner les cellules obstruantes à partir de la boîte de dialogue d’erreur, Excel mettra en évidence les cellules non vides.

Dès que vous videz la ou les cellules à l’origine de l’erreur, vous verrez vos données entièrement renseignées.

Comment réparer les cellules fusionnées dans la plage de débordement

Ensuite, vous avez peut-être fusionné par inadvertance des cellules au sein de votre plage de débordement. Si tel est le cas, la boîte de dialogue d’erreur indique : « La plage de débordement a fusionné une cellule ». Vous devrez annuler la fusion des cellules pour que la formule dynamique répartisse ses valeurs sur toute la plage de cellules.

Le moyen le plus simple de trouver les cellules fusionnées est de cliquer Sélectionner les cellules obstruantes à partir de la boîte de dialogue d’erreur. Une fois cela fait, vous pouvez cliquer sur le bouton Fusionner les cellules dans le ruban pour dissocier les cellules. Une fois cette opération effectuée, vos données devraient s’afficher comme vous le souhaitez.

Que faire si la plage de déversement se situe dans un tableau

Pour une raison inconnue de Microsoft, les formules dynamiques et les tableaux dans les feuilles de calcul Excel ne fonctionnent pas bien. Si vous essayez de remplir les cellules d’un tableau à l’aide d’une formule dynamique, cela échouera. Vous devrez soit afficher le résultat ailleurs, soit convertir le tableau en plage.

Si vous devez convertir un tableau en plage, suivez simplement ces étapes.

1. Cliquez avec le bouton droit sur n’importe quelle cellule du tableau.
2. Ensuite, choisissez Tableau > Convertir en plage.
   
3. Confirmez que vous souhaitez convertir le tableau en plage en cliquant sur Oui.
   

En supposant que vous n’ayez pas d’autres erreurs avec votre plage de déversement, votre formule dynamique devrait réussir à remplir les cellules appropriées avec des données.

Comment résoudre un déversement inconnu

Ensuite, il y a une erreur délicate : « La plage de débordement est inconnue ». Cette erreur se produit lorsqu’Excel n’est pas en mesure de déterminer la taille à définir pour le tableau. Cela se produit généralement lorsque vous utilisez une fonction volatile comme RANDARRAY, RAND ou RANDBETWEEN dans votre fonction dynamique.

Essentiellement, une telle fonction randomisée entraîne une modification du tableau entre les calculs de la feuille de calcul. La seule façon de résoudre ce problème est d’élaborer une formule différente qui ne modifie pas la taille du tableau entre les calculs.

Comment corriger une erreur de débordement trop importante dans Excel

Enfin, vous avez peut-être l’habitude d’utiliser une formule qui calcule, par exemple, un pourcentage de chaque cellule d’une colonne. Par exemple, une formule telle que =B:B*10% fonctionnerait parfaitement bien dans Excel 2019 et les versions antérieures.

Dans les versions plus récentes d’Excel qui prennent en charge les formules dynamiques, cette même formule génère une erreur « La plage de débordement est trop grande ». En effet, avec les formules dynamiques, Excel tente de calculer toutes les valeurs du tableau en même temps. Par conséquent, il atteint la fin de la feuille de calcul avant d’avoir terminé la sortie des valeurs.

Pour contourner ce problème, la meilleure solution consiste à faire référence à une plage de cellules au lieu d’une colonne ou à éviter complètement l’utilisation d’une formule dynamique. Par exemple, en remplaçant la formule ci-dessus par =B2:B12*10% fonctionnera très bien.

Vous pouvez également vous en tenir à la méthode traditionnelle de remplissage de votre formule sur toute la plage de votre colonne, soit en copiant et collant la formule, soit en utilisant la poignée de remplissage automatique dans la cellule contenant votre formule.

Tirer le meilleur parti des formules dynamiques et des tableaux de déversement

Microsoft a introduit les tableaux dynamiques dans Office 365 en janvier 2020. Ils sont pris en charge dans Excel 365 pour Windows, Mac, Apple, Android et Windows Mobile. Vous constaterez notamment qu’Excel Online ne prend pas en charge les tableaux dynamiques ou en débordement.