3.6 Troubleshoot spill errors - Video Tutorials & Practice Problems

<v Narrator>Spill errors are returned</v> when a formula returns multiple results, and excel cannot return the results in a grid or display them in the corresponding spill range. Specifically, spill errors may be returned for one of the following scenarios. Spill range isn't blank, which means it has existing values, formulas, tables, etc within the range. In this case, as soon as we clear up the cells that have the data, the spill will work as expected. (indistinct) size, which means that the spill range will need to resize between calculation passes. In this case, we're using the sequence function with a random number as its argument. So as the sequence function, (indistinct) will determine the structure and size of the resulting spill data, the random number would change, which in turn would change the size and structure of the resulting spill data. Thus, it just never gets out of that loop, which means it never displays the result on screen and results in the editor. In such cases, we just need to use a different combination for functions, or maybe use a different approach to solving our problem. Another scenario is where the spill range extends beyond the worksheets edge. As an example, here, we're using the sequence function to generate a list of numbers in 1,048,576 rows. However, that's the maximum number of rows allowed in Excel. If you account for the additional rows at the top of this formula cell, this will go past these spreadsheets edge, and thus this results in an error. A solution in this case, is to just use a smaller argument and allow for the spill to work. As discussed previously, spill ranges will also not work with Excel tables. If you think about it, the spill data from one of these cells would probably overlap other content in the table. This would result in the first spill error that we discussed where the spill range isn't blank, we would also encounter a spill error if a merged cell is within a spill range. In such a scenario, even clearing out the merged cell will not result in the spill working. The solution here would be to unmerge the cells. And the spill data will work as expected. Other scenarios for spill errors would include out of memory, where we might want to try a smaller range or an array and unrecognized or fallback, which is for errors where Excel can't recognize or reconcile the cause. Now that we have covered the spill behavior in sufficient detail, let's review another relevant topic, implicit intersection