ArcGIS for Excel - #SPILL! error when using ArcGIS functions in Excel tables

Description

Running an ArcGIS function within the Excel tables or in the column directly to the right side of the Excel table causes the #SPILL! Error. 

Cause

Spilled array formulas aren’t supported in Excel tables. The cause of this behavior is a limitation within Microsoft Excel where running the formulas are not fully supported in the Excel tables, please check the Microsoft documentation #SPILL! error - Table formula for more information.

Solution or Workaround

1. As per Microsoft's suggestion, convert the Excel table into a range, execute the ArcGIS for Excel function to avoid the #SPILL error.
2. If, however, you don’t want to convert the Excel table to a range, run the ArcGIS for Excel function outside the table. This can be done by:
   1. Place the formula in the cell two columns to the right of the last table column.
   2. Use the Copy and Paste Values option to remove the formula from the Excel sheet.
   3. Delete the column between the Excel table and the newly added columns from the function.
   4. Resize the table to incorporate the newly added columns within the table area, as demonstrated in the video: Avoid SPILLERROR in Excel tables
3. Another option that avoids converting the Excel table to a range and keeps the function dynamic, involves placing the formula on a separate worksheet, you can then create cell references within the original table that point to the function located on the other worksheet, as demonstrated in the video: Excel Dynamic Function Workaround