Investigate Excel TABLE() array formula support via Office.js API
Session from pi-for-excel-sessions on Hugging Face by thomasmustier · MIT · condensed by Coders Talk
The developer wanted to know if their Excel AI agent tool could programmatically create or use Excel's Data Table TABLE() array function, and explore alternatives for sensitivity analysis on a DCF model.
- +0Prompt · +0Excel's TABLE() function can't be set via formula/API, only through the UI. Do we have a way to enable array formulas?
- +0mAgent did · +0mSearched the codebase for array formula, TABLE(), spill and CSE handling across write-cells.ts, fill-formula.ts, capabilities.ts and execute-office-js.ts, and reviewed DECISIONS.md
- +1mAgent did · +1mReported that modern dynamic array formulas (SORT, FILTER, UNIQUE, XLOOKUP) already work via write_cells, and that execute_office_js exists as an escape hatch for advanced Office.js calls, but neither supports legacy TABLE()
- +3mPrompt · +3mAsked how to build a sensitivity table for valuation vs discount rate and terminal growth rate
- +3mAgent did · +3mProposed a formula-based sensitivity grid where each cell directly computes valuation for its discount rate/growth rate pair, avoiding TABLE()
- +6mPrompt · +6mAsked how this would work for a more complex model with a phased margin uplift driver affecting variable costs through 2036
- +6mAgent did · +6mExplained that formulas would need to inline the entire multi-year DCF chain per cell, and proposed a LAMBDA+LET helper approach as a cleaner alternative
- +7mPrompt · +7mAsked directly if there's any way to let the agent use TABLE()
- +8mOutcome · +8mConcluded TABLE() is locked behind a UI-only code path with no API surface in Office.js, Office Scripts, or OOXML round-trip; recommended sticking with formula-based sensitivity grids or LAMBDA/LET helpers instead
I went in assuming there might be some Office.js trick to trigger TABLE(), but the honest answer was no — it's genuinely UI-only. Next time I'd skip the initial 'can we enable array formulas' framing and just ask straight away for formula-based alternatives to a Data Table, since that's what I actually needed for the sensitivity analysis. The useful correction was reframing the problem as 'inline the DCF into each cell' rather than trying to replicate Excel's native TABLE() behavior.