I like putting a conditional formatting on my sheets which is simply =ISFORMULA(A1) and encompasses the entire sheet.

So, basically, if a cell contains a formula, it gets a grey background or something of the sort, which makes for a clear distinction between cells which expect user input (not grey) and those which are output and shouldn't be messed with (grey).

However, when using a spilled array result, only the cell which actually contains the formula gets marked by this formatting, with the spilled results remaining unaffected by the conditional formatting.

So, is there some ISFORMULA() equivalent or some other way of detecting whether a cell has been spilled into?

  • @ScottCraner, thanks. That's a VBA solution, is it the only way to do it? If so, is there a VBA method to test if a given cell has been spilled into? Thinking of how to adapt that solution to this case of conditional formatting, the only way I can think of is to -- for each cell -- check the entire worksheet and see if any spill ranges include that cell, which sounds intensive. My worksheets have thousands of rows with hundreds of columns, so I fear that'd slow the worksheet to a crawl (but haven't tested yet).
    – Wasabi
    Jun 22, 2020 at 18:56
  • You can use the code that I did in that answer to return a range using UNION. Then apply the back ground color to it. I think VBA is the only method. and as such it will need a trigger Jun 22, 2020 at 18:59
  • 1
    You can use =ISREF(A2 $A$1#) but that requires you to know the parent cell. I cannot find any formula that on its own will tell if the data in the cell was placed there by spill or was placed there by the user. Jun 22, 2020 at 19:20

4 Answers 4


Not entirely sure if this helps, but you can refer to the list of spilled results (within formulas) when you don't know how many results there are by using the spill range operator #. For instance, if a result at A1 spills into A2,A3, etc, and you use A1# it will pull the whole range or results automatically. You can also use counta(A1#) to quantify how many results there are, perhaps as part of a check function to see if there are any spills like if(counta(A1#)>1,true).

  • Welcome to SuperUser! I'm aware of this, but how does that help to identify cells which have been spilled into?
    – Wasabi
    Jun 22, 2020 at 19:15
  • If you don't need the spilled values, you could append the @ symbol before the reference, such as @A1 and this stops excel from processing them. If you need to keep them though I'm not immediately sure how you'd do that easily, but I know it could be done through combinations of these operators and if statements
    – Ash243x
    Jun 22, 2020 at 19:29
  • I've read your edit, but I don't think you understood my question. I'm not trying to determine whether a cell spills. I'm trying to determine whether a cell has been spilled into. For example, what formula can I use to determine whether the contents of Z100 are the result of another cell somewhere in the worksheet having spilled into Z100?
    – Wasabi
    Jun 22, 2020 at 19:31

An efficient solution is to define the following simple VBA function:

Function isSpill(x As Range)
    isSpill = x.HasSpill
End Function

Then calling something like =isSpill(Z100) returns TRUE for cells which have been spilled into by some other cell.

For my conditional formatting situation, this can then be trivially combined with =OR(ISFORMULA(A1), isSpill(A1)) to also include non-spill formulas.

However, this has the obvious (and pretty big) downside that it requires an .xlsm file and for users to always have to enable macros. So a non-VBA solution would be welcome, if possible.


Say we know that A1 has a formula that spills down and we want to list the cells that are occupied by the spill. Try:


enter image description here

(here we use a Spill to describe a Spill)

and to put the answer in a single cell:



To determine if a cell is filled by entry or by Spill, just click on it and run:

Sub DontCryOverSpiltCell()
    MsgBox (ActiveCell.Address & vbCrLf & ActiveCell.HasSpill)
End Sub
  • Unfortunately this is the inverse of what I want. Say Z100 has a value and =ISFORMULA(Z100) returns FALSE. How can I differentiate between a constant value written by the user and a value which was spilled from somewhere else on the worksheet?
    – Wasabi
    Jun 22, 2020 at 19:58
  • See my answer for a better idea of what I'm trying to find. Unfortunately, I could only do it with VBA, so I'm still on the lookout for a non-VBA solution.
    – Wasabi
    Jun 22, 2020 at 20:07
  • @Wasabi You are correct. My EDIT#1 is a VBA method. Jun 22, 2020 at 20:12

You can use the Excel 4 Macro function, IN a Named Range, to test whether a cell has a formula via GET.CELL(41,"cell being checked") which WILL work on the formula a SPILLED Into cell has. It will ALSO return the contents of non-formula cells, so a further test needs included, via AND() being the simplest approach, of, say, Does the result of whatever is in the cell equal the result of the GET.CELL() result?

If it does, then the content is PROBABLY a constant of some sort, numerical or string. However, a formula will likely return a value utterly different than the text of the formula itself, like... essentially always. So if the test fails, one would assume, with good chances, a formula in the cell. That's half the battle.

Then, is it a SPILL formula? Well, usually, one does not encounter contiguous cells, either up/down or right/left (that's right, unless you can eliminate right/left SPILL you need to test for both here so hopefully one can eliminate one or the other as a concern). So A1-A2-A3-A4-A5, say, would maybe have related formulas, but not IDENTICAL formulas, as a SPILL range would. Testing the content of a cell, say, three or 14 cells up (as one's guess about such might suggest: the more distant a match, the likelier SPILL is at work, but also the greater the chance that simply something odd is occurring) vs. the cell of interest ought to be a decent test. Do that via GET.CELL() as well.

Binding the two tests together (AND() again) in a single Named Range would give you a return that is problematic since you need AND() here, but the two tests are going to give opposite results (of TRUE and FALSE) so you must NOT() one of them. Whichever way you go, you will have a result you can use in a simple IF().

Unfortunately, FORMULATEXT() returns "" for a SPILLED Into cell's contents. That'd be the way to go otherwise (sigh...).

There are other ways to do the testing, like TYPE() for instance instead of does the GET.CELL() result equal a simple =cell's contents result. But nothing in the approach is direct in the sense of "exact and unambivalent" so one needs to fit the testing to the situation. The method described above works, and it looks to me like a pretty decent test (the critical part being the assumption that contiguous cells should have no closer than "similar" formulas, not "identical" ones).


No teaching necessary as you'll write the formula and they'll use the results, not the technique:

It's part of the macro language from then, but it is actually simply a function, like SUM() or VLOOKUP() with one single exception: it will not work if entered DIRECTLY into a cell. You write a perfectly normal formula, say to check cell A5, just like this:


but you first create a Named Range, call it Horse perhaps, then write it there (or write it cell-side where it won't even be treated as a function, but who cares, right, you just want the text, and copy it to the clipboard, then make the Named Range and just paste it in). So now you have a Named Range called Horse and the "Refers to" box has the above in it.

Back in the cells, let's say you want the result in cell F23. In that cell you just type:


and it finds the value by using the function for you. ALL you and the user see in the cell is the result.

No macro at all, just a perfectly normal formula with a weird name. Give it a normal one and no one's even wondering anything.

So, before giving up on it, just try the above: make some Named Range, maybe called Experiment, put that formula in, make some spill function that puts some value into cell A5, then pick some other cell and type =Experiment and see the formula the content of A5 spawns from.

If you still feel weirded out, that's cool. But maybe you'll see how easy it is and like it. Just would take 2-3 minutes.

  • 1
    Yeah, after posting my question I realized that if I could find a way to extract what was literally written in a cell (be that a formula or a constant), I'd be able to solve this: double-clicking a spilled-into cell to edit-in-place shows a blank cell, so I'd expect to get a blank result. I googled and found FORMULATEXT(), but it doesn't differentiate between empty cells, constants and spilled into results, all of which return #N/A for me. If it actually made that distinction, I could match it with ISBLANK() (returns false for spilled-into cells) to get my desired result. But no dice.
    – Wasabi
    Jun 24, 2020 at 0:03
  • And I didn't get to try this solution using GET.CELL() because I don't even know how to activate that macro, and I certainly wouldn't want to have to teach the people I share my work with how to do it, either.
    – Wasabi
    Jun 24, 2020 at 0:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .