The Spreadsheet Copilot Test: Ask It a Number You Already Know

To evaluate spreadsheet artificial intelligence, first create a sanitized copy of a real spreadsheet. Preserve all important elements such as dates, empty rows, duplicate IDs, refunds, text-formatted numbers, and ambiguous labels. Remove any personal or confidential information. Develop ten questions with known answers, covering sum across a given date range, pivot by product or region, duplicate count using a stable ID, join between two tables, trend where the denominator varies, an ambiguous term like "active customer," a formula that should return an error for missing data, and a question with its answer in a footnote rather than a column name.

Run these questions without showing the expected results. For each response, record the formula, query, cell range, chart data, and explanation. Then, compare the results with manually verified answers. Don't just check if the final number is close; the answer must be correct for the right range even as future data changes. Assess the AI's performance through four levels of functionality.

The first layer is Copilots inside a spreadsheet, which includes features like formula generation, explanations, pivots, charts, and Python for heavier analysis. The second layer is AI databases for shared structured data, where Table serves as a bridge between a spreadsheet and a database, offering typed fields, views, permissions, APIs, and AI-assisted table operations.

The third layer is Natural Language to SQL tools like AI2SQL and SQLAI, which translate questions into SQL and can explain, optimize, or repair the query. The fourth and final layer is AI on a governed BI model, which allows natural-language questions on top of a semantic model for consistent answers from governed data. To accurately assess the AI's performance, record the exact workbook, locale, formulas, and sharing model your team uses.

If the AI's answer is wrong, capture the evidence, formula or SQL used, input range or tables, definitions in the prompt, workbook locale and date format, model output and chart source, and the type of error (arithmetic, scope, definition, freshness, formatting, or unsupported operation). This structured approach helps organizations test spreadsheet AI effectively and identify areas needing improvement.