Formler som kan orsaka felmeddelandet #SPILL! i Excel

Ladda ner exempelfilen: SPILL-funktioner.xlsx

I Excel kan det hända att du får ett felmeddelande som heter #SPILL! eftersom Excel har fått en ny beräkningsmotor som gör det möjlig att använda formler på ett helt nytt sätt.

Traditionella formler

Du och många andra har troligen skrivit en formel i en cell i Excel för att sedan ta tag i fyllningshandtaget (som du hittar i det nedre högra hörnet av en cell) och därefter klickat och dragit ner formeln till övriga celler i en kolumn eller på en rad för att kopiera formeln.

I detta exempel kopieras följande formel till övriga rader i kolumnen:

=B2-C2

Tips!

Det går även att dubbelklicka på fyllningshandtaget för att formler ska kopiera nedåt.

Duplicera formeln

Du har även kunnat markera den cellen du vill kopiera inklusive det område som ska fyllas och använda alternativet Nedåt [Down] under ikonen Fyll [Fill] på menyfliken Start [Start].

Har du Excel från och med version 2021 eller senare behöver du inte längre kopiera formler på detta sätt. Excel kan göra det åt dig.

Nu går det att skriva formler som beräknar områden och fyller i svaren i ett område med motsvarande storlek.

Tips!

Kortkommandot för Fyll [Fill] är Ctrl + D.

Beräkna hela områden

I stället för att bara markera cellen B2 med intäkten och sedan subtrahera cellen C2 med utgiften går det markera hela området med intäkter =B2:B14, fortsätta med ett minustecken och sedan markera hela området med utgifter C2:C14.

Så formeln blir:

=B2:B14-C2:C14

När du sedan avslutar inmatningen med Enter-tangenten kommer Excel att fylla eller spilla, ut formeln till ett lika stort område.

Formeln som finns i cellen D2 syns med svart text, i övriga celler som formeln fyller eller spiller till, är formeln gråtonad.

Tips!

Excel har redan tidigare hanterat formler som ”spiller” ut resultatet över flera celler via så kallade matrisfunktioner. För att dessa formler skulle fungera behövde du avsluta inmatningen av formlerna med tangenterna Ctrl + Skift + Enter. Formlerna omgavs då av klammerparenteser {=B2:B14-C2:C14}. Denna möjlighet finns kvar i Excel för bakåtkompatibilitet.

Felmeddelandet #SPILL!

När uppstår felmeddelandet #SPILL!?

I detta exempel är summan på totalraden i cellen D14 redan beräknad med en formel och vi ska nu se vad som händer, om vi använder samma beräkning som tidigare i cellen D2 för att räkna ut vinsten, för varje månad:

När vi försöker använda formeln

=B2:B14-C2:C14

i cellen D2, orsakar det ett felmeddelande.

Ett felmeddelande visas både i cellen D2, där vi skrivit in formeln och i cellen D14, där det redan låg en annan formel.

Felmeddelandet #SPILL! visas när formeln inte kan spilla ut till eller fylla, ett område med motsvarande storlek. Spillområdet behöver vara helt tomt, innan du använde en formel som kan spilla ut över andra celler.

Ändrar vi storleken på beräkningsområdet till:

=B2:B13-C2:C13

och ser till att inte ta med summorna på rad 14 i området, fungerar formeln igen och spillområdet stannar på rad 13.

OBS!

Det går bara att ändra och redigera formeln den första cellen D2. I övriga celler i spillområdet, visar endast formelns spillområde nedtonad i en grå nyans.

Spillområdet är inte tomt

Genom att klicka till på den lilla pilen bredvid varningstriangeln, intill felmeddelandet, går det att få en ledtråd till varför det blev fel.

I detta fall var orsaken att det spillområde, som formeln vill fylla till, inte var helt tomt.

Felmeddelandet som visas överst anger att orsaken är att Spillområdet är tomt [Spill range isn’t blank].

Eftersom det inte fanns plats för formeln att spilla eller fylla ett område med motsvarande storlek, blev spillområdet tomt.

För att undvika att du får #SPILL!-fel, så se till att det finns tillräckligt med tomma celler runt cellen där du skriver dina #SPILL!– och matrisformler för att få plats med alla eventuella resultat.

Om det inte finns tillräckligt med utrymme, kommer Excel att visa felmeddelandet #SPILL!.

Spillområde i Exceltabeller

En annan orsak till felmeddelandet #SPILL! är att formler med spillfunktion inte kan användas i Exceltabeller.

Exceltabeller har en egen inbyggd funktion, som fyller formler till alla celler i en kolumn, helt automatiskt.

Vill du absolut använda formler med SPILL-funktion behöver du konvertera Exceltabellen till ett vanlig område, via menyvalet Konvertera till område [Convert to Range] på menyfliken Tabelldesign [Table Design].

Sammanfogade celler

Spillområdet kan inte fylla celler som är sammanfogade.

Markera de celler som ska ingå i spillområdet och välj alternativet Separera celler [Unmerge Cells] i förväg, för att säkerställa att inga celler är sammanfogade.

Spillområdet blir för stort

Spillområdet kommer bli för stort, om referenser till hela kolumner ingår i formler.

Eftersom formeln är placerad på rad två i kalkylbladet och formeln i detta exempel, hänvisar till alla rader i kolumn B:B och C:C kommer spillområdet blir för stort.

Området kan inte spillas och fyllas från rad två och nedåt, då spillområdet i så fall kommer hamna utanför kalkylarkets begränsningar, på max 1,048,576 rader.

Tips!

Markera endast de celler som behöver ingå i beräkningsområdet när du skapar dina formler.

 

Radera och redigera formler med SPILL-funktion

Det går bara att ändra och redigera formler med SPILL-funktion i den översta första cellen, i detta exempel D2. I övriga celler i spillområdet, visar endast formeln i spillområdet, nedtonad i grått.

Det går inte att ställa sig i en av de celler som formeln spiller till, där formeln visas i en gråton och trycka på Delete-tangenten. Formeln kommer spillas på nytt, till den tomma cellen.

Det går bara att ta bort och redigera formeln, i den översta cellen där beräkningen visas i svart färg.

Stäng av SPILL-funktionen

Genom att använda ett snabel-a @-tecken framför ett område i formler ”stängs” SPILL-funktionen av.

=@B2:B14-@C2:C14

Det kan till exempel vara bra metod att använda vid felsökning av en formel.

Formeln med ett snabel-a @-tecken kan sedan kopieras till övriga celler som vanligt.